Brad’s Sure Guide to SQL Server Maintenance Plans- P29: SQL Server has a reputation as being a simple database application to install, configure, and maintain. This is a little misleading. SQL Server is a powerful relational database that can handle the needs of the largest organizations and, as such, its proper maintenance almost certainly requires the attention of an experienced DBA. | Chapter 11 History Cleanup Task The History Cleanup task is very straightforward. It simply removes old data from the msdb database and that s it. Over time as backup and restore jobs run as SQL Server Agents jobs run and as Maintenance Plans run historical data about each of these jobs is stored in tables of the msdb database. In the short term data stored in msdb can be useful. For example if you are having problems with SQL Server Agent jobs or Maintenance Plans then this past msdb data can be used to help troubleshoot what went wrong. Also SSMS uses the data stored in msdb about backup jobs to make it easier for you to use SSMS to restore databases or log files. Of course you can also restore backups and logs using T-SQL commands that don t require this data. Ultimately however this data has a limited life span and once it gets old there is no point in keeping it around. An Overview of the History Cleanup Task The msdb database is often referred to as the SQL Agent database since SQL Server Agent uses it to store all sorts of information about the jobs it runs. Unfortunately SQL Server doesn t do any kind of its own internal clean up of this data so over time the msdb database can grow and grow and grow. It can even lead to some minor performance problems. As such the DBA is responsible for cleaning up old records from msdb that are no longer of any value and the History Cleanup task is designed to make that job easy. When the History Cleanup task runs using its default settings it executes the following T-SQL code declare @dt datetime select @dt cast N 2009-07-22T14 19 13 as datetime EXEC @dt GO EXEC @oldest_date 2009-07-22T14 19 13 GO EXECUTE null null 2009-07-22T14 19 13 141 Chapter 11 History Cleanup Task As you can see this task runs three different system-stored procedures one each to clean up historical data for backups jobs and maintenance plans. Each of the three
