asked
Purge SQL Server Job History causes MSDB to grow and Consume entire drive
My MSDB file is located on the C drive, everytime I try to purge the SQL Server Job History it causes the MSDB to grow and consume the entire C drive. How can I deleting old job history without the MSDB consuming the entire drive?
I have tried removing month by month but the MSDB fills up the drive. Below is the information on what is taking the most space up on the MSDB. I was thinking about truncating the table sysjobhistory (No column name) TableSizeInMBs row_count sysjobhistory 8385 6942702 sysjobhistory 430 6942700 backupfile 47 135826 backupfilegroup 7 67913 backupset 41 67913 backupset 55 67913 backupset 2 67913 backupmediaset 6 67778 backupmediaset 58 67778 backupmediafamily 18 67778 backupmediafamily 61 67778 sysmaintplan_logdetail 1726 59365 sysmaintplan_log 3 25955 sysmail_log 5 18637 sysjobactivity 2 13854 sysmultiobjrefs 0 4424 sysmultiobjrefs 0 4424 syscolpars 0 3967 syscolpars 0 3967 sysjobschedules 0 3598 sysjobschedules 0 3598
The site works from votes. For all helpful answers click on the thumbs up next to them. If any one answer helped solve the problem, click on the check mark next to that answer.
Purge the data in very small pieces. Rather than trying to remove it all, only remove a month or so at a time. That way the log won't have to be larger to support the larger transaction that you're creating. Unless we're talking about a development machine, it's generally considered a very bad practice to have any of the databases on the C:\ drive.
8GB, that was large! If you really wan't to go "crazy" and truncate the table and loose all of your backup info, have a look at Brent Ozars article on how he did it: [brents-backup-bottleneck-msdb][1] If you decide not to go that route and continue with deleting a week at a time. Do not forget that there are a couple of indexes that are missing if you wan't to speed up the delete process. Now since you are almost out of space on C:, I would suggest moving MSDB to a another disk **before** adding any indexes to speed up the delete process. [MSDB-Performance-Tuning][2] [1]:
http://www.brentozar.com/archive/2009/05/brents-backup-bottleneck-msdb/ [2]:
http://weblogs.sqlteam.com/geoffh/archive/2008/01/21/MSDB-Performance-Tuning.aspx
you can limit the job history log size by setting history row size in SSMS.
17 People are following this question.
