Are you keeping your system databases tidy?

SQL Server likes to log things!
Over time this causes the system databases to grow unnecessarily, and potentially make searching for the log you want slower.
Keep things trimmed and tidy, to make it quicker and easier to find things when you actually need them!

Backup history

  • An entry for every backup taken for each database on the instance
  • Useful for looking into failures, capacity management of backup time
  • After a couple of months, not very useful
DECLARE @DeleteOlderEntries DATETIME
SET @DeleteOlderEntries = DATEADD(DAY, -60, GETDATE())
EXEC msdb.dbo.sp_delete_backuphistory @DeleteOlderEntries

Job History

  • An entry for every step for each SQL Agent job on the instance
  • Useful for looking into failures
  • After a couple of months, not very useful.
  • A lot of entries slows down loading the history
DECLARE @DeleteOlderEntries DATETIME
SET @DeleteOlderEntries = DATEADD(DAY, -60, GETDATE())
EXEC msdb.dbo.sp_purge_jobhistory @DeleteOlderEntries

Database Mail history

  • An entry for every mail sent via Database Mail
  • Includes every attachment sent
  • Can make msdb grow very large if a lot of mails are sent
DECLARE @DeleteOlderEntries DATETIME
SET @DeleteOlderEntries = DATEADD(DAY, -60, GETDATE())
EXEC msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @DeleteOlderEntries

SQL Server Error Log

  • By default cycles when it reaches a set size
  • Slow to open as it parses the log file
  • Have to wait for it to get to the day you’re looking for
  • Schedule to run on the first of each month at midnight
EXEC sp_cycle_errorlog

Leave a Reply

Your email address will not be published.