Controlling Growth of a msdb Database

I recently encountered a situation where the drive hosting Sharepoint Databases in a Staging environment ran out of space. I logged onto the server and found that the msdb database has itself occupied 38 GB of the total disk space. Msdb database generally contain maintenance information for the database such as backups, log shipping and so on. My first step was to examine all the tables and I noted that there was not an abnormally large number of records.

I then decided to verify using a T-SQL script (shown below) exactly who the culprit actually is and the results were rather strange.

SELECT object_name(i.object_id) as objectName,
i.[name] as indexName,
sum(a.total_pages) as totalPages,
sum(a.used_pages) as usedPages,
sum(a.data_pages) as dataPages,
(sum(a.total_pages) * 8 ) / 1024 as totalSpaceMB,
(sum(a.used_pages) * 8 ) / 1024 as usedSpaceMB,
(sum(a.data_pages) * 8 ) / 1024 as dataSpaceMB
FROM sys.indexes i
INNER JOIN sys.partitions p
ON i.object_id = p.object_id
AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a
ON p.partition_id = a.container_id
GROUP BY i.object_id, i.index_id, i.[name]
ORDER BY sum(a.total_pages) DESC, object_name(i.object_id)
GO

The output of the above T-SQL query is as shown in the screen capture below:

As you can see in the first highlighted section we can see that there are two indexes named c1lsmonitor_history_detail, and nc2lsmonitor_history_detail is present in the table named log_shipping_monitor_history_detail which has occupied 25698+9899=35597 MB= 34.76 GB and these were the primary cause of the large database size.

I then decided to perform a Re-index of the two indexes noted above and I did this by just right-clicking on the Index Name and selecting Rebuild.I also updated the statistics of the corressponding indexes. After completing the Re-indexing and Update Statistics, I tried to Shrink the msdb database and it shrank the database size from 35 GB to a mere 700 MB. please refer the screen capture below:

I then decided to include the msdb database as a part of daily reindexing and update statistics job which is set to occur daily.

Reindexing and updating statistics could alternatively be accomplished using the T-SQL below.

T-SQL for Re-indexing:

DECLARE
@dbname varchar(1000),
@parentname varchar(255),
@SQLSTR VARCHAR (1000),
@ctrl CHAR (2),
@command varchar(1000)
SET @ctrl = CHAR (13) + CHAR (10)
DECLARE DBCUR CURSOR FOR
select [name]
from sysdatabases where name not in
(
'master',
'model',
'tempdb'
)
order by 1
OPEN DBCUR
FETCH NEXT FROM DBCUR INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
select @command =
'
use ['+@dbname+']
Exec sp_MSForEachtable ''DBCC DBREINDEX ("?")''
'
exec (@command)
FETCH NEXT FROM DBCUR INTO @dbname
END
CLOSE DBCUR
DEALLOCATE DBCUR
GO

T-SQL for Update Statistics:

DECLARE
@dbname varchar(1000),
@parentname varchar(255),
@SQLSTR VARCHAR (1000),
@ctrl CHAR (2),
@command varchar(1000)
SET @ctrl = CHAR (13) + CHAR (10)
DECLARE DBCUR CURSOR FOR
select [name]
from sysdatabases where name not in
(
'master',
'model',
'tempdb'
)
order by 1
OPEN DBCUR
FETCH NEXT FROM DBCUR INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
select @command =
'
use ['+@dbname+']
Exec sp_MSForEachtable ''update statistics ? with fullscan''
'
exec (@command)
FETCH NEXT FROM DBCUR INTO @dbname
END
CLOSE DBCUR
DEALLOCATE DBCUR
GO

Thus we have successfully controlled the growth of the Distribution Database. Please let us know if you have any suggestions or comments on this approach.




Related Articles :

  • No Related Articles Found

10 Responses to “Controlling Growth of a msdb Database”

  1. Good Article….

  2. Good Article……..

  3. You pointed roughly finicky subject matter. These tips are very beneficial.
    Acne medication

  4. Very useful tip.
    Thanks

  5. Uhm, I hope you’re not running the update-statistics (which computes an approximation of the stats) right after the index-rebuild (which by itself obtains accurate statistics on the newly created index).
    In other words, doing an update-statistics right after an index-rebuild is pointless or even detrimental on large tables since the update-statistics takes a less accurate approach (=record-samples instead of looking at each indexed record) in favor of performance

  6. Pesonally, I found the title a little misleading. What you describe should be a part of regular database maintenance. In my opinion, controlling the size of MSDB should be about purging data on a regular basis. Have a look at procedures like sp_purge_jobhistory or procedures which starts with ‘sp_delete_%’. In case of logshipping there are some routines to keep the size of these in control.

    In case of maintaining databases: there are a lot of scripts to do this. I recommend the solution from http://ola.hallengren.com/

  7. good article

  8. Hi,
    All object shows data values space is zero.
    but msdb stil having 4967mb space.
    what will cause ??

  9. Great Work :) !!!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |