Large System Databases on SQL Server

This one is an oldie in my blog drafts, but a goodie! So, I will finally post it after nearly three years of sitting around. Thank you, past self, for documenting this so well. All I have to do is tidy this up and publish. If you ever have to clean up large system DBs on your SQL Server, this can hopefully help you out.

First Things First

Always set this on your SQL Servers so you don’t have this problem in the first place. This is in the SQL Server Agent settings. I remember having some agent jobs that used to serve this function that ran on a schedule, which may have been required in older versions of SQL Server.

Under History. Make sure you limit the job or agent history so your msdb doesn’t get really big.

Accessing System DB Size

Let’s say you didn’t have that history in the agent set to limit the size. This happened in our older SQL Servers. Thankfully, we mostly had the system DBs on their own drive, but sometimes not, and they were starting to get really big! I knew I had to clean them out, so I ran this query on master and msdb to see what the culprits were:

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

Results listed for master:

Results listed for msdb:

Since I also had other large system db issues on the same drive, I fixed those first to ensure I had as much room as possible.

Cleaning Up Model and Master

So I shrank model.ldf file because I had no idea how that got so large.

USE [master]
GO
DBCC SHRINKFILE (N'model_log' , 0, TRUNCATEONLY)
GO

We had Ola’s CommandLog table in master, and the job to delete older than 30 days wasn’t enabled, so I had to delete data from the CommandLog table bit by bit and then shrink the master log file.

USE [master]
GO
DBCC SHRINKFILE (N'mastlog' , 0, TRUNCATEONLY)
GO

Then, I also shrunk the master data file way down bc we won’t be storing so much data in there anymore.

USE [master]
GO
DBCC SHRINKFILE (N'master' , 500)
GO

Then, I had more wiggle room to fix the msdb space issue. Note: I made sure master and msdb are in simple recovery mode.

MSDB Cleanup

When I ran sp_delete_backuphistory, I got the error “The transaction log for database ‘msdb’ is full due to ‘ACTIVE_TRANSACTION.'”

exec [dbo].[sp_delete_backuphistory] @oldest_date = '02/25/2021'

It blew up the log file, so 35 GB was added to the drive, making it 50 GB instead of 15 GB. I tried again, and it ran super slow and blew up the log file again.

Don’t Do This

Then I decided to just truncate the tables that the stored proc deletes from because otherwise, this could take forever.

This URL gives a list of the tables that the stored proc deletes from:

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-delete-backuphistory-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15

sp_delete_backuphistory must be run from the msdb database and affects the following tables:

• backupfile
• backupfilegroup
• backupmediafamily
• backupmediaset
• backupset
• restorefile
• restorefilegroup
• restorehistory

The physical backup files are preserved, even if all the history is deleted.

I drop that list of tables into SSMS, and here’s a cool trick: you can use alt-shift to select down a column like so:

Then I typed truncate table on all those tables like so, and yes, for those SQL sticklers, I didn’t put a semicolon at the end because I was lazy:

truncate table backupfile
truncate table backupfilegroup
truncate table backupmediafamily
truncate table backupmediaset
truncate table backupset
truncate table restorefile
truncate table restorefilegroup
truncate table restorehistory

I got this error:

That’s the thing with foreign key constraints because they stopped me from clearing out all the system info, which I didn’t really want to do.

Do This Instead

In the interim, my other query finished after nearly 8 mins:

exec [dbo].[sp_delete_backuphistory] @oldest_date = '01/25/2021'

So that got everything older than 1 month. Then I truncated the msdb log:

USE [msdb]
GO
DBCC SHRINKFILE (N'MSDBLog' , 0, TRUNCATEONLY)
GO

Then shrink msdb back to a smaller size:

USE [msdb]
GO
DBCC SHRINKFILE (N'MSDBData' , 500)
GO

Making Sure MSDB Stays Smaller

Voila, back to a nice size, and then make sure to enable the jobs that do this for you. They should already exist in the agent, but if they don’t, you can recreate them by using the First Things First section at the top.

The post Large System Databases on SQL Server appeared first on sqlkitty.