Cleaning up msdb database is one of the neglected yet most important task of a sql administrator. We have some SSIS packages, full back up once a day and transactional backup in every 15 minutes. We use databaseMail extensively therefore cleaning up msdb database is a good practice.
The msdb database was 241 MB and after cleaning up ( running the following scripts) I am able to bring it down to 153 MB. I am keeping a log of the database growth in a table in every 4 hours. I do not think that the size of the msdb has to be this big. So I am leaving it the way it is and then looking at table where the file growth is captured befor I make the decision if I need to rsize the db to a smaller size.
The scripts that I ran
--removing anything before a month
DECLARE
SET @date = DATEADD(day, -30, CURRENT_TIMESTAMP)
--delete backup history
--delete old mail items
--especially, if you are sending attachements
EXEC msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @date
--delete the log of the sent items
EXEC msdb.dbo.sysmail_delete_log_sp @logged_before = @date
--delete the SQL Server agent job history log
EXEC msdb.dbo.sp_purge_jobhistory @oldest_date = @date
EXEC msdb.dbo.sp_delete_backuphistory @date @date datetime
No comments:
Post a Comment