Maintain MSDB

Lori Brown points out that there are some SQL Server service tables which can bloat your msdb database:

I recently received a panicked call from a client who had a SQL instance go down because the server’s C drive was full. As the guy looked he found that the msdb database file was 31 GB and was consuming all of the free space on the OS drive causing SQL to shut down. He cleaned up some other old files so that SQL would work again but did not know what to do about msdb.

As we looked at it together I found that the sysmaintplan_logdetail table was taking all the space in the database. The SQL Agent had been set to only keep about 10000 rows of history but for some unknown reason the table never removed history. After consulting MSDN I found this code did the trick for truncating this table.

Lori’s focus here is on SQL Agent history, but don’t forget about things like backup history as well.

Related Posts

When tempdb Spills Attack

Josh Darnell ran into a problem with a SQL Agent job: One of my colleagues reached out to me recently about a production issue where a SQL Server Agent job had failed with this error message: Msg 1105, Level 17, State 2, Line 15Could not allocate space for object ‘dbo.SORT temporary run storage: 140737513062400’ in […]

Read More

Multi-Server Patching with dbatools

Eric Cobb shows how you can use dbatools to upgrade multiple SQL Server instances: Patching SQL Server can sometimes be a time consuming process, especially when you have multiple servers that need to be patched. Remoting in to each box to run through the update wizard is tedious, and if you have multiple patches to […]

Read More

Categories

February 2018
MTWTFSS
« Jan Mar »
 1234
567891011
12131415161718
19202122232425
262728