The following is the a walk-through guide towards how I resolved a problem with a MSDB that went wild. I strongly recommend never shrinking database files. I am not alone on this. If you need more reasons check here (If you don’t like that example see the ones used in that post).
Unfortunately I am in a situation where I didn’t have another option. We didn’t have a process on a server to delete records from the tables used to log SQL Server Agent job information. This database server in particular has over 400 databases and hourly transactional logs and a full backup. This alone will create 10,000 rows daily when logging the results. We actually reached the point where the data drive free space was consumed.
Backup MSDB
First, step is to do a full backup of MSDB. You should always make a backup before you plan on doing any changes with a system database. You should also have a plan to restore the system database just in case you have to implement it.
So Where’s the Beef?
Next, I needed to know why this database was so huge. I ran the following query to see the file size of all database objects within the MSDB database. You can find this query written by Jeremy Kadlec in MSSQLTIP #1461 I also highly recommend reading that tip as it will provide some more helpful information to troubleshoot a large MSDB.
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
Dam you sysmaintplan_logdetail
Okay, we tried to using sp_maintplan_delete_log and it failed because the transaction log grew and consumed all the space on the drive for transactional log files. We are not allowed to take the SQL Server database engine offline so we go with the next best option truncate the tables in question and shrink the database.
Yes I know…… I said shrink the database. I actually had to slap myself after typing this.
I found this great post on msdn that walks you through the script 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;
Shrink MSDB
Now I know you followed the first step and backed up your MSDB. Once again you should never consider truncating or shrinking data if you don’t have a working backup that can be restored.
Okay now that we are done with the disclaimer I created the following script to truncate the log and data files.
--- SHRINK THE MSDB LOG FILE
USE MSDB
GO
DBCC SHRINKFILE(MSDBLog, 512)
GO
-- SHRINK THE MSDB Data File
USE MSDB
GO
DBCC SHRINKFILE(MSDBData, 1024)
GO
Rebuild Indexes
Now that you did the nasty and shrinked the database data file you need to rebuild your indexes and update your statistics. You can find the scripts to do this below.
-- REBUILD ALL INDEXES
USE MSDB
GO
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
GO
-- UPDATE STATISTICS
EXEC sp_updatestats
EXEC sp_helpdb @dbname= 'MSDB'
How do we prevent this from reoccurring?
Well actually there are quite a few things that could have and should have been done. We could create a maintenance plan to clean up the SQL Agent history, we can also specify in the agent’s properties to also clean up history. I will show you how to do both below.
The following is the maintenance task that will help cleanup the agent history tables.
You can also right click on the SQL Server Agent in SSMS and select properties. Once the properties window comes up select History on the list on the left. You will then be able to remove data by record size or date.
