August 23, 2018 at 1:42 am
Good Morning,
I have a SQL 2016 box that has a large MSDB database, the log file it self it 6gb. I have found out that the sysjobhistory is taking up most of the space. When I try to purge the log gets bigger, whats the best way to delete this data and make the MSDB database smaller in size?
August 23, 2018 at 1:56 am
Either back up the transaction log of msdb regularly, or (preferably) set the database to Simple recovery. It should then be safe to implement whatever method you use to purge old job history.
John
August 23, 2018 at 2:04 am
So running a truncate and then database shrink should be ok?
August 23, 2018 at 2:22 am
I don't know what you mean by "truncate". Truncating the transaction log is deprecated and in any case would only be a temporary solution. Set the database to Simple recovery, remove the data you don't need, set the options so it only keeps a sensible amount of history, and then shrink the log and/or database files.
John
August 23, 2018 at 2:25 am
Ok makes sense thank you.
August 23, 2018 at 4:25 am
Typically, the MSDB can grow large over time if there are no maintenance jobs in place to clean up old job history and backup history records. And yes, if these are left unchecked for too long, then cleaning up can become a hassle, both taking a long time and generating lots of TLOG. If log space is an issue, you may need to split up the job into more managable chunks.
Other than that, it's basically like John said: Clean it up, make sure it stays cleaned up from now on and then shrink it to a reasonable size.
Vegard Hagen
Norwegian DBA, occasional blogger and generally a nice guy who believes the world is big enough for all of us.
@vegard_hagen on Twitter
Blog: Vegards corner (No actual SQL stuff here - havent found my niche yet. Maybe some day...)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply