SQL DBA Guru

Case Scenario:

This morning I got an alert that the MSDB Data file has increased a lot in some GB size. When I tried to shrink it, I am unable to do so, though shrinking the database is not the best thing to do. Unfortunately I am in a situation where I didn’t have another option.  There is no job to delete records from the tables used to log SQL Server Agent job information. This database server has over hundreds of databases and bi-hourly transactional logs and a full backup.  This alone will create thousands of rows daily when logging the results, consuming the full drive space.

Resolution:

The first and foremost task is to take the full backup of the MSDB database.  Next I wanted to do the analysis for the increased size of the MSDB database.  The following query helped me to do this:

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

 

After this, I tried using sp_maintplan_delete_log but it failed because the transaction log had consumed all the space on the drive.  Moreover, we can’t take the SQL Server database engine offline so the next best option available is only to truncate the MSDB database specific tables and shrink the database. The following script helped me to truncate the sysmaintplan_logdetail table.

 

ALTER TABLE [dbo].[sysmaintplan_log] DROP CONSTRAINT [FK_sysmaintplan_log_subplan_id];

ALTER TABLE [dbo].[sysmaintplan_logdetail] DROP CONSTRAINT [FK_sysmaintplan_log_detail_task_id];

Truncate table msdb.dbo.sysmaintplan_logdetail;

Truncate table msdb.dbo.sysmaintplan_log;

 ALTER TABLE [dbo].[sysmaintplan_log] WITH CHECK ADD CONSTRAINT [FK_sysmaintplan_log_subplan_id] FOREIGN KEY([subplan_id]) REFERENCES [dbo].[sysmaintplan_subplans] ([subplan_id]);

 ALTER TABLE [dbo].[sysmaintplan_logdetail] WITH CHECK ADD CONSTRAINT [FK_sysmaintplan_log_detail_task_id] FOREIGN KEY([task_detail_id]) REFERENCES [dbo].[sysmaintplan_log] ([task_detail_id]) ON DELETE CASCADE;

 Now, I did the shrinking of the MSDB data and log files with the following script

 USE MSDB

GO

DBCC SHRINKFILE(MSDBLog, 512)

GO

DBCC SHRINKFILE(MSDBData, 512)

GO

 After the database shrinking, you can verify that a good amount of disk space is reclaimed. Then we need to rebuild the indexes and update the statistics as well.

 --The following script rebuilds all the indexes…

USE MSDB

GO

EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"

GO

-- UPDATE STATISTICS 

EXEC sp_updatestats

EXEC sp_helpdb @dbname= 'MSDB'

 

Next Steps:

-       Create a maintenance plan to clean up the SQL Agent history.

-       Right click on the SQL Server Agent in SSMS and select properties and then select History from the left side.  Set the Remove agent history to remove the history.