CODEWITHSUNDEEP
sql-server-2008
Kumar C
Kumar C

Reputation: 132

SQL Server 2008 MSDB Data Huge (700GB")

I have got a SQL Server 2008 (not R2) installation on which I am running my company's production databases.

Recently I have noticed that the MSDBData.mdf size is almost 700 GB. I have not watched this database file before (for size).

I tried google search on how to reduce the size of MSDBData.mdf and found a few suggestions (including on SO). But none of those reduced the size of the MSDBData.mdf.

I have also right clicked on the data base Reports --> Standard reports --> disk Usage by Tables shows that each table is occupying only around 2 MB or so and that is only for 15 tables in MSDB but still the mdf size is 700 GB.

I am not sure what made the mdf so huge since sum of the table sizes as per the Reports --> Standard reports --> disk Usage by Tables does not show so much space.

Kindly Advice on how can I reduce the size of MSDB as I am running out of disk space.

Upvotes: 0

Views: 1362

Answers (1)

Chris Pickford
Chris Pickford

Reputation: 8991

Given the results of sp_spaceused in the question comments, the space must be being taken up by a current object. Run the following query which will give you metrics about the size of objects. Note that it's not only tables that use space, indexes and other objects do also.

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);

You can use this information to make educated decisions about whether you should drop these objects or not. Obviously check before dropping anything that they are not system objects or currently in use for some reason. I'd also recommend doing a full database backup prior to dropping anything.

Upvotes: 1

Related Questions