July 8, 2017 at 2:08 pm
Hi,
We have one issue where the data and log file of MSDB are 30 GB each and are eating up the drive,
I wanted to clean it up but it is not allowing me to truncate the tables in question ,not allowing me to drop constaints or even run a select on the tables.
Any idea what indexes I can add to speed up the truncate and shrink the files. Its ok to lose the data
Also as a side question is it ok to make the msdb point to new files from a similar SQL version but with no data ( template ) and will SQL Server start?
MSDBData
MSDBLog
36453184 KB
30435840 KB
object_id obj type total_rows total_size
571149080 dbo.sysmaintplan_logdetail U 70745 24804.83
1339151816 dbo.backupset U 4021543 3029.30
1243151474 dbo.backupmediaset U 4021543 621.89
2027154267 dbo.sysssispackages U 8 3.85
there is blocking when I even try to select table like
sysmaintplan_log
Hoping to hear from Senior DBA;s on this.
Thanks
July 9, 2017 at 11:07 am
Can you try to truncate / delete the backup history information ?
July 9, 2017 at 11:16 am
kaleparag - Sunday, July 9, 2017 11:07 AMCan you try to truncate / delete the backup history information ?
Also pull the in-built report "disk usage by top tables" for tables where maximum disk space can be reclaimed by purging data from those tables.
July 9, 2017 at 2:49 pm
You have a few million rows in your backup tables for the history. If you don't run regular maintenance on msdb, this can happen.
Why don't you try cleaning up some of the backup information by executing sp_delete_backuphistory. You pass it a date parameter for the oldest date to retain. If you delete in batches, you can get things cleaned up. You can find the oldest date querying the backupset table with something like: select min(backup_start_date)
from dbo.backupset
Don't try to clean them all out at once - find the oldest date and just go ahead a few months or so using that for the parameter. Just keep going up more months - a few or more at a time and clean it up.
Then schedule regular cleanup for msdb using that procedure. There are history tables for other things that you want to clean up regularly for database mail, log shipping which have delete procedure for deleting old entries:
sysmail_delete_mailitems_sp
sysmail_delete_log_sp
sp_cleanup_log_shipping_history
Sue
July 10, 2017 at 7:39 am
In addition to Sue_H's answer, check to see if there are indexes on the various tables...2016 has them, older versions do not. Not sure when this changed. I've had to add them in the past (probably not a Microsoft supported choice) to get even one day to delete 🙂
------------------------------------------------------------------------------------------------Standing in the gap between Consultant and ContractorKevin3NFDallasDBAs.com/BlogWhy is my SQL Log File HUGE?!?![/url]The future of the DBA role...[/url]SQL Security Model in Plain English[/url]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply