question

sql_hunter avatar image
sql_hunter asked

how to cut the sizes of the msdb database?

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?
msdb
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Cyborg avatar image
Cyborg answered
You should cleanup some of the old log records of your Maintenance plans and your backup history. There are few ways to do this,[check here][1]. [1]: http://sqlserverpedia.com/blog/sql-server-bloggers/clean-up-maintenance-plan-history/ Delete your old backup logs using
EXEC sp_delete_backuphistory @oldest_date='1/1/2011'
Purge your job history using
Exe sp_purge_jobHistory @oldest_date='1/1/2011'
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Correction Not "Anju", its "Anuj" :-) When you delete records from any tables, the free space will not be released to the OS this will be reserved for the data file for future database growth. you must shrink the database to release free space back to the OS, although shrinking releases free space, this is not a recommended process as it has few drawbacks 1-Results in fragmentation of the indexes. 2-Resource consuming 3-Space gained through SHRINK process could potentially be reused by the data\log files in future as your database grows. But there are situations when you must shrink your database, like when you have a disk space issue, or when you drop\delete huge tables from your database (Read Only). After shrinking the database its a good practice to rebuild\reorganize (update stats) your indexes.
1 Like 1 ·
Thanks Anju, the biggest size is on this table sysmaintplan_logdetail. I have deleted some hundred thousands rows. but the actual data file size has not been reduced. I think this is because the number of page file remained same. it implies to me that there is internal fragmentation. can you please clarify me about this?
0 Likes 0 ·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
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: http://msdn.microsoft.com/en-us/library/ms189493.aspx http://msdn.microsoft.com/en-us/library/ms188388.aspx
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sql_hunter avatar image
sql_hunter answered
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)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.