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.




Array

No comments yet... Be the first to leave a reply!