Friday, June 9, 2017

MSDB too large

C drive was growing very fast and then saw that the msdb  data file and log file was huge.

I shrank the msdb log file first.
I ran the following query to see the top higly populated table

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

Once running the query on MSDB , I found the follwing large tables


I ran the follwing to clear the history

use msdb
GO
exec sp_maintplan_delete_log @oldest_time = '05/12/2017'


This expands the log file so make sure you shrink file


DBCC SHRINKFILE(MSDBLog, 50) --50MB

GO


Then the following maintenance plan was scheduled to run every week.




No comments:

Post a Comment