one of my friend's msdb database has grown up to 6 GB. I want to cut some sizes. i have checked which tables are taking larger disk space. I found that sysmaintplan_logdetail is huge and TotalPages UsedPages DataPages TotalSpaceMB UsedSpaceMB DataSpaceMB 631212 631188 23419 4931 931 182 which seems to me that there is some internal page fragmentation. there is not enough free space showing while he ties to shrink the data file. Can anybody put some light on it?
If you have fragmentation and therefore can't shrink the physical file though there's free space in it, you'll want to rebuild or reorganize the indexes. Rebuilding the index does just that - it rebuilds the whole index. If you do that on the clustered index, the actual data pages are moved. If you reorganize, SQL Server will try to defragment the index without rebuilding it. Reorganize only affects the leaf level of the index tree so it won't be as effective as rebuild on largely fragmented indexes. Reorganize will cause a lot of log records in the transaction log. Syntax: ALTER INDEX index_name ON table_name REBUILD; ALTER INDEX index_name ON table_name REORGANIZE; Then you can do DBCC SHRINKFILE to shrink the physical file. Syntax for that: DBCC SHRINKFILE(logical_file_name,desired_size_in_megabytes) More info on DBCC SHRINKFILE and ALTER INDEX:
Thanks Magnus, you are correct. I do also think that i might need to shrink the file. But before shrink, i check from the management Studio how much free space is available. I have seen that there is 0% free space. but if i run this command: sp_spaceused sysmaintplan_logdetail i can see this table reserved a huge disk (5GB) space. name rows reserved data index_size unused sysmaintplan_logdetail 4350 5053424 KB 5049488 KB 3728 KB 208 KB here i am confused!! The below is the query i used to check the largest table in the msdb database. 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)