![\"\"](\"http:\/\/databaserefresh.com\/wp-content\/uploads\/2015\/02\/bp3_img1.gif\" "\\"bp3_img1\\"")
<\/a><\/p>\nBut there are circumstances when you may need to do this manually, for example: SQL Server Agent is disabled on SQL Server; you inherited old SQL Server where history has not been cleaned up since server setup. Also, keep in mind that \u201cHistory Cleanup Task\u201d does not include Database Mail logs.<\/p>\n
<\/p>\n
Manual Cleanup<\/strong><\/h2>\nAudit requirements in this example will be to keep 6 months of historical data and 6 months of backup history (based on Service Level Agreements). This is not real life scenario that we will be only using it to demonstrate database size changes after the cleanup.<\/p>\n
I would like to keep email sending log for the last 6 months as well as evidence for auditors that I get e-mails about critical system errors.<\/p>\n
Here is the msdb database size on SQL Server with 50 databases and 1 year of history:<\/p>\n
![\"\"](\"http:\/\/databaserefresh.com\/wp-content\/uploads\/2015\/02\/bp3_img2.gif\" "\\"bp3_img2\\"")
<\/a> <\/p>\n Let\u2019s run SSMS report showing Disk Usage by Top Tables:<\/p>\n
![\"\"](\"http:\/\/databaserefresh.com\/wp-content\/uploads\/2015\/02\/bp3_img3.gif\" "\\"bp3_img3\\"")
<\/a><\/p>\n <\/p>\n
Step 1: Backup-restore history cleanup<\/span><\/strong><\/p>\nThis is done by executing sp_delete_backuphistory<\/em><\/strong> stored procedure. Here are the tables affected (as per BOL):<\/p>\n\u201c\u2026 sp_delete_backuphistory<\/strong> must be run from the msdb<\/strong> database and affects the following tables:<\/em><\/p>\n\n- backupfile <\/em><\/li>\n
- backupfilegroup <\/em><\/li>\n
- backupmediafamily <\/em><\/li>\n
- backupmediaset <\/em><\/li>\n
- backupset <\/em><\/li>\n
- restorefile <\/em><\/li>\n
- restorefilegroup <\/em><\/li>\n
- restorehistory <\/em><\/li>\n<\/ul>\n
The physical backup files are preserved, even if all the history is deleted.\u201d<\/em><\/p>\n <\/p>\n
The stored procedure\u2019s parameters: <\/p>\n
sp_delete_backuphistory [ @oldest_date =<\/strong> ] ‘oldest_date’<\/p>\n <\/p>\n
The script to cleanup backup-restore history for our example scenario:<\/p>\n
EXEC<\/span> msdb.dbo.sp_delete_backuphistory <\/span> @oldest_date = ‘January 1, 2012’<\/span><\/em><\/p>\n <\/p>\n
Step 2: Mail history cleanup<\/span><\/strong><\/p>\nThis is done by executing sysmail_delete_log_sp<\/em> and sysmail_delete_mailitems_sp <\/em>stored procedures.<\/p>\n <\/p>\n
The sysmail_delete_log_sp<\/em><\/strong> stored procedure deletes entries from the Database Mail log (msdb.dbo.sysmail_log table).<\/p>\n <\/p>\n
The stored procedure\u2019s parameters: <\/p>\n
sysmail_delete_log_sp [ [ @logged_before = ] <\/strong>‘logged_before’ ]<\/strong><\/p>\n [, [ @event_type = ] <\/strong>‘event_type’ ]<\/strong><\/p>\n <\/p>\n
The script to cleanup old mail log records for our example:<\/p>\n
EXEC<\/span> msdb.dbo.sysmail_delete_log_sp<\/span> @logged_before = ‘January 1, 2012’<\/span><\/em><\/p>\n\n
The sysmail_delete_mailitems_sp<\/em><\/strong> stored procedure deletes e-mail messages from the Database Mail internal tables (msdb.dbo.sysmail_allitems table).<\/p>\n <\/p>\n
The stored procedure\u2019s parameters: <\/p>\n
sysmail_delete_mailitems_sp [ [ @sent_before = ] <\/strong>‘sent_before’ ]<\/strong><\/p>\n [ , [ @sent_status = ] <\/strong>‘sent_status’ ]<\/strong><\/p>\n <\/p>\n
The script to cleanup old mail items for our example scenario:<\/p>\n
EXEC<\/span> msdb.dbo.sysmail_delete_mailitems_sp <\/span> @sent_before = ‘January 1, 2012’<\/span> , @sent_status = NULL — all statuses<\/span><\/em><\/p>\n <\/p>\n
Step 3: Maintenance plans log cleanup<\/span><\/strong><\/p>\nThis is done by executing sp_maintplan_delete_log<\/em> stored procedure.<\/p>\nThe sp_maintplan_delete_log<\/em><\/strong> stored procedure permanently deletes entries and details from the Maintenance Plans log (msdb.dbo.sysmaintplan_logdetail and msdb.dbo.sysmaintplan_log tables).<\/p>\nThe stored procedure\u2019s parameters: <\/p>\n
sp_maintplan_delete_log [ [@plan_id = ] <\/strong>‘plan_id’ ]<\/strong><\/p>\n [, [@subplan_id = ] <\/strong>‘subplan_id’ ] <\/strong><\/p>\n [, [@oldest_time = ] <\/strong>‘oldest_time’ ]<\/strong><\/p>\n <\/strong> <\/p>\nThe script to cleanup old maintenance plans log records for our example scenario:<\/p>\n
EXEC<\/span> msdb.dbo.sp_maintplan_delete_log <\/span> @oldest_time = ‘January 1, 2012’<\/span><\/em><\/p>\n <\/p>\n
Step 4: Job history cleanup<\/span><\/strong><\/p>\nThis is done by executing sp_purge_jobhistory<\/em> stored procedure.<\/p>\nThe sp_purge_jobhistory<\/em><\/strong> stored procedure permanently deletes entries and details from the job history (msdb.dbo.sysjobhistory table).<\/p>\nThe stored procedure\u2019s parameters:<\/p>\n
sp_maintplan_delete_log [ [@plan_id = ] <\/strong>‘plan_id’ ]<\/strong><\/p>\n [, [@subplan_id = ] <\/strong>‘subplan_id’ ] <\/strong><\/p>\n [, [@oldest_time = ]<\/strong> ‘oldest_time’ ]<\/strong><\/p>\n <\/strong> <\/p>\nThe script to cleanup old maintenance plans log records for our example scenario:<\/p>\n
EXEC<\/span> msdb.dbo.sp_maintplan_delete_log<\/span> @oldest_time = ‘January 1, 2012’<\/span><\/em><\/p>\n\n
All steps in one script <\/h2>\n
DECLARE<\/span> @date DATE<\/span><\/em><\/p>\nSET<\/span> @date = ‘January 1, 2012’<\/span><\/em><\/p>\n <\/em><\/p>\nEXEC<\/span> msdb.dbo.sp_delete_backuphistory <\/span> @oldest_date = @date<\/em><\/p>\nEXEC<\/span> msdb.dbo.sysmail_delete_log_sp <\/span> @logged_before = @date<\/em><\/p>\nEXEC<\/span> msdb.dbo.sysmail_delete_mailitems_sp <\/span> @sent_before = @date , @sent_status = NULL<\/span><\/em><\/p>\nEXEC<\/span> msdb.dbo.sp_maintplan_delete_log <\/span> @oldest_time = @date<\/em><\/p>\nEXEC<\/span> msdb.dbo.sp_purge_jobhistory <\/span> @oldest_date = @date<\/em><\/p>\n <\/p>\n
Now if we run the same database reports from SSMS as before we will see the difference:<\/p>\n
![\"\"](\"http:\/\/databaserefresh.com\/wp-content\/uploads\/2015\/02\/bp3_img4.gif\" "\\"bp3_img4\\"")
<\/a><\/p>\n ![\"\"](\"http:\/\/databaserefresh.com\/wp-content\/uploads\/2015\/02\/bp3_img5.gif\" "\\"bp3_img5\\"")
<\/a><\/p>\n The database\u2019s size reduced by almost 64%!<\/p>\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
Disk space these days is not as expensive as it used to be, but as a DBA I like to keep things tidy and optimize as much things as possible. Cleaning up databases and old backups, archiving databases are some … Continue reading
<\/a> <\/p>\nLet\u2019s run SSMS report showing Disk Usage by Top Tables:<\/p>\n
<\/p>\n Step 1: Backup-restore history cleanup<\/span><\/strong><\/p>\n This is done by executing sp_delete_backuphistory<\/em><\/strong> stored procedure. Here are the tables affected (as per BOL):<\/p>\n \u201c\u2026 sp_delete_backuphistory<\/strong> must be run from the msdb<\/strong> database and affects the following tables:<\/em><\/p>\n The physical backup files are preserved, even if all the history is deleted.\u201d<\/em><\/p>\n <\/p>\n The stored procedure\u2019s parameters: <\/p>\n sp_delete_backuphistory [ @oldest_date =<\/strong> ] ‘oldest_date’<\/p>\n <\/p>\n The script to cleanup backup-restore history for our example scenario:<\/p>\n EXEC<\/span> msdb.dbo.sp_delete_backuphistory <\/span> @oldest_date = ‘January 1, 2012’<\/span><\/em><\/p>\n <\/p>\n Step 2: Mail history cleanup<\/span><\/strong><\/p>\n This is done by executing sysmail_delete_log_sp<\/em> and sysmail_delete_mailitems_sp <\/em>stored procedures.<\/p>\n <\/p>\n The sysmail_delete_log_sp<\/em><\/strong> stored procedure deletes entries from the Database Mail log (msdb.dbo.sysmail_log table).<\/p>\n <\/p>\n The stored procedure\u2019s parameters: <\/p>\n sysmail_delete_log_sp [ [ @logged_before = ] <\/strong>‘logged_before’ ]<\/strong><\/p>\n [, [ @event_type = ] <\/strong>‘event_type’ ]<\/strong><\/p>\n <\/p>\n The script to cleanup old mail log records for our example:<\/p>\n EXEC<\/span> msdb.dbo.sysmail_delete_log_sp<\/span> @logged_before = ‘January 1, 2012’<\/span><\/em><\/p>\n \n The sysmail_delete_mailitems_sp<\/em><\/strong> stored procedure deletes e-mail messages from the Database Mail internal tables (msdb.dbo.sysmail_allitems table).<\/p>\n <\/p>\n The stored procedure\u2019s parameters: <\/p>\n sysmail_delete_mailitems_sp [ [ @sent_before = ] <\/strong>‘sent_before’ ]<\/strong><\/p>\n [ , [ @sent_status = ] <\/strong>‘sent_status’ ]<\/strong><\/p>\n <\/p>\n The script to cleanup old mail items for our example scenario:<\/p>\n EXEC<\/span> msdb.dbo.sysmail_delete_mailitems_sp <\/span> @sent_before = ‘January 1, 2012’<\/span> , @sent_status = NULL — all statuses<\/span><\/em><\/p>\n <\/p>\n Step 3: Maintenance plans log cleanup<\/span><\/strong><\/p>\n This is done by executing sp_maintplan_delete_log<\/em> stored procedure.<\/p>\n The sp_maintplan_delete_log<\/em><\/strong> stored procedure permanently deletes entries and details from the Maintenance Plans log (msdb.dbo.sysmaintplan_logdetail and msdb.dbo.sysmaintplan_log tables).<\/p>\n The stored procedure\u2019s parameters: <\/p>\n sp_maintplan_delete_log [ [@plan_id = ] <\/strong>‘plan_id’ ]<\/strong><\/p>\n [, [@subplan_id = ] <\/strong>‘subplan_id’ ] <\/strong><\/p>\n [, [@oldest_time = ] <\/strong>‘oldest_time’ ]<\/strong><\/p>\n <\/strong> <\/p>\n The script to cleanup old maintenance plans log records for our example scenario:<\/p>\n EXEC<\/span> msdb.dbo.sp_maintplan_delete_log <\/span> @oldest_time = ‘January 1, 2012’<\/span><\/em><\/p>\n <\/p>\n Step 4: Job history cleanup<\/span><\/strong><\/p>\n This is done by executing sp_purge_jobhistory<\/em> stored procedure.<\/p>\n The sp_purge_jobhistory<\/em><\/strong> stored procedure permanently deletes entries and details from the job history (msdb.dbo.sysjobhistory table).<\/p>\n The stored procedure\u2019s parameters:<\/p>\n sp_maintplan_delete_log [ [@plan_id = ] <\/strong>‘plan_id’ ]<\/strong><\/p>\n [, [@subplan_id = ] <\/strong>‘subplan_id’ ] <\/strong><\/p>\n [, [@oldest_time = ]<\/strong> ‘oldest_time’ ]<\/strong><\/p>\n <\/strong> <\/p>\n The script to cleanup old maintenance plans log records for our example scenario:<\/p>\n EXEC<\/span> msdb.dbo.sp_maintplan_delete_log<\/span> @oldest_time = ‘January 1, 2012’<\/span><\/em><\/p>\n \n DECLARE<\/span> @date DATE<\/span><\/em><\/p>\n SET<\/span> @date = ‘January 1, 2012’<\/span><\/em><\/p>\n <\/em><\/p>\n EXEC<\/span> msdb.dbo.sp_delete_backuphistory <\/span> @oldest_date = @date<\/em><\/p>\n EXEC<\/span> msdb.dbo.sysmail_delete_log_sp <\/span> @logged_before = @date<\/em><\/p>\n EXEC<\/span> msdb.dbo.sysmail_delete_mailitems_sp <\/span> @sent_before = @date , @sent_status = NULL<\/span><\/em><\/p>\n EXEC<\/span> msdb.dbo.sp_maintplan_delete_log <\/span> @oldest_time = @date<\/em><\/p>\n EXEC<\/span> msdb.dbo.sp_purge_jobhistory <\/span> @oldest_date = @date<\/em><\/p>\n <\/p>\n Now if we run the same database reports from SSMS as before we will see the difference:<\/p>\n The database\u2019s size reduced by almost 64%!<\/p>\n <\/p>\n","protected":false},"excerpt":{"rendered":" Disk space these days is not as expensive as it used to be, but as a DBA I like to keep things tidy and optimize as much things as possible. Cleaning up databases and old backups, archiving databases are some … Continue reading <\/a><\/p>\n\n
All steps in one script <\/h2>\n
<\/a><\/p>\n<\/a><\/p>\n