{"id":3258,"date":"2012-12-03T20:10:47","date_gmt":"2012-12-03T20:10:47","guid":{"rendered":"http:\/\/craftydba.com\/?p=3258"},"modified":"2024-02-17T17:16:00","modified_gmt":"2024-02-17T17:16:00","slug":"maintenance-history-cleanup-tasks","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=3258","title":{"rendered":"Maintenance & History Cleanup Tasks"},"content":{"rendered":"

\"\"<\/a><\/p>\n

Today, I will be continuing the series of articles on the control flow tasks available in SQL Server Integration Services (SSIS) to perform Database Administration (DBA) tasks. We will be designing a SSIS package that executes a data retention policy in regards to backup files and [msdb] history records.<\/p>\n

One of the most important job functions of a DBA is to have a recent backup chain in which a point in time restore can be performed. A backup chain consists of the most recent FULL<\/a> backup, differential (DIFF<\/a>) backup, and transaction LOG<\/a> backup. <\/p>\n

I am going to start up Visual Studio and create a project named “cleanup-backup-info”<\/span> under the “c:\\ssis-depot\\” directory. Upon having a blank package, we will be adding and configuring several objects. I am going to list each of the objects we need to solve this business solution.<\/p>\n

cmMySqlServer<\/span> – The local database server connection information.<\/p>\n

cmMySmtpServer<\/span> – The local mail server connection information.<\/p>\n

tskRemoveOldBakFiles<\/span> – A maintenance cleanup task for FULL & DIFF backups.<\/p>\n

tskRemoveOldTrnFiles<\/span> – A maintenance cleanup task for LOG backups.<\/p>\n

tskCleanupMsdbHistory<\/span> – A history cleanup task for [msdb] database.<\/p>\n

tskEmailSuccess<\/span> – Mail notice is sent when the package completes successfully.<\/p>\n

tskEmailFailure1<\/span> – Mail notice is sent when package fails to remove old “bak” files.<\/p>\n

tskEmailFailure2<\/span> – Mail notice is sent when package fails to remove old “trn” files.<\/p>\n

tskEmailFailure3<\/span> – Mail notice is sent when package fails to clean up [msdb] history.<\/p>\n

The key items to configure the Maintenance Cleanup task<\/a> for removing old “bak” files are the location of the root directory, whether to search sub-directories, the file extension to look for, and the number of weeks to retain files.<\/p>\n

\"\"<\/a><\/p>\n

Again, we need to configure the Maintenance Cleanup task to apply a retention policy for the “trn” files.<\/p>\n

\"\"<\/a><\/p>\n

The History Cleanup task <\/a>is even easier to configure. Select the SQL Server connection, all historical data items you want to cull, and the number of weeks to retain entries.<\/p>\n

\"\"<\/a><\/p>\n

Make sure the package looks like mine with the precedence constraints correctly configured. Now, lets run the package to completion.<\/p>\n

\"\"<\/a><\/p>\n

I just noticed a success email that was sent to my email account and it appear in MS Outlook application.<\/p>\n

\"\"<\/a><\/p>\n

I wonder what the SSIS package is actually doing to perform the backup? To discover what T-SQL is being sent to the server, we can open a client\/server side trace using SQL Server Profiler<\/a>. I am going to skip over the mechanics of starting a profiler trace and running the SSIS package in debug mode.<\/p>\n

The whole trace file (“cleanup-backup-info.trc”<\/span>) along with the SSIS package (“cleanup-backup-info.dtsx”<\/span>) are included as a download bundle at the end of the article.<\/p>\n

Basically, the TSQL commands can be broken down into three parts: remove old backup files, remove old transaction files, and cleanup [msdb] history.<\/p>\n

I cleaned up the code that was sent by the SSIS package to the database.<\/p>\n

\r\n--\r\n-- TASK #1\r\n-- \r\n\r\nEXEC master.dbo.xp_delete_file 0,N'C:\\mssql\\backup',N'bak',N'2012-11-03T09:15:56',1\r\n\r\n--\r\n-- TASK #2\r\n-- \r\n\r\nEXEC master.dbo.xp_delete_file 0,N'C:\\mssql\\backup',N'trn',N'2012-11-03T09:15:56',1\r\n\r\n--\r\n-- TASK #3\r\n-- \r\n\r\nDECLARE @dt datetime select @dt = cast(N'2012-11-03T09:15:56' as datetime) \r\nEXEC msdb.dbo.sp_delete_backuphistory @dt\r\nEXEC msdb.dbo.sp_purge_jobhistory  @oldest_date='2012-11-03T09:15:56'\r\nEXEC msdb..sp_maintplan_delete_log null,null,'2012-11-03T09:15:56'\r\n<\/pre>\n
In summary, the Maintenance Cleanup task can be used to apply a data retention policy to the backup files that are physically on disk.    The History Cleanup task can be used to apply a record retention policy to backup\/restore jobs, job history, and maintenance plan history inside the [msdb] database.<\/p>\n
Like any ETL package, this one can be made more re-usable by parameterizing the arguments needed by each control flow object.  In my next article, we will exploring how to use the Check Database Integrity task to validate our databases.<\/p>\n
cleanup backup info bundle<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
Today, I will be continuing the series of articles on the control flow tasks available in SQL Server Integration Services (SSIS) to perform Database Administration (DBA) tasks. We will be designing a SSIS package that executes a data retention policy in regards to backup files and [msdb] history records. One of the most important job functions of a DBA is to have a recent backup chain in which a point in time restore can be performed. A backup chain consists of the most recent FULL backup, differential (DIFF) backup, and…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7],"tags":[12,649,15,648,638,652,654,653,28,642,636,650,651],"class_list":["post-3258","post","type-post","status-publish","format-standard","hentry","category-other","tag-free-code","tag-history-cleanup-task","tag-john-f-miner-iii","tag-maintenance-cleanup-task","tag-send-mail-task","tag-sp_delete_backuphistory","tag-sp_maintplan_delete_log","tag-sp_purge_jobhistory","tag-sql-server","tag-sql-server-profiler","tag-ssis-developer","tag-trace-file--tsql","tag-xp_delete_file"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/3258","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3258"}],"version-history":[{"count":1,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/3258\/revisions"}],"predecessor-version":[{"id":8951,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/3258\/revisions\/8951"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3258"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3258"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3258"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}