Friday 1 October 2010

SCSM - What's using my DB Space?

Useful query pulled from the Service Manager forums where a user (Victor Gacitua) stated they were having issues with a rather large Service Manager DB.

This will show the top 100 items in the DB and the page counts etc they use.
TOP 100, OBJECT_NAME(i.object_id), dps.used_page_count, dps.in_row_used_page_count, dps.lob_used_page_count, dps.row_overflow_used_page_count, dps.row_count
FROMsys.dm_db_partition_stats dps
JOIN sys.partitions p
ON dps.partition_id = p.partition_id
JOIN sys.indexes i
ON p.index_id = i.index_id AND p.object_id = i.object_id
ORDERBY dps.used_page_count DESC

No comments: