Shrink MSDB Database

Shrinking a database isnt generally recommended but there may be situations when a compact operation is required. For example, introducing a purging policy of 90 days on backup history when previously there wasn’t one would cause a lot of free space in the msdb, especially if its a high activity instance based on jobs.

Backup MSDB

The 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.

To check what is using the space:

USE [msdb]
go
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

Disable all Agent Jobs

Select * into msdb..DBA_Agent_jobs_Snapshot
FROM msdb..sysjobs
WHERE ENABLED = 1

SELECT 'EXEC msdb.dbo.sp_update_job @job_id=N''' 
+ cast(job_id as varchar(40)) + ''', @enabled=0'
FROM msdb..DBA_Agent_jobs_Snapshot

--Run output from above

Shrink MSDB

USE [msdb]
GO
--1GB was sufficient for this MSDB. 
DBCC SHRINKFILE (N'MSDBData' , 1024)
GO

You can keep an eye on the compact status by running the following for your specific session:

select percent_complete, * from sys.dm_exec_requests
--session+_id of the shrink operation
where session_id = 50

Enable all Agent Jobs

SELECT 'EXEC msdb.dbo.sp_update_job @job_id=N''' 
+ cast(job_id as varchar(40)) + ''', @enabled=1'
FROM msdb..DBA_Agent_jobs_Snapshot

--Run output from above
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: