SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Logging in Maintenance Plans

By C.J. Garcia,

Maintenance Plans are pretty new to me, since my previous DBA jobs would just do any database maintenance or backups through scripts and jobs created by the team.  Not that Maintenance Plans are bad, we just felt we had more control over backups and other maintenance tasks by creating our own scripts and jobs.  Here is a little trick to get some more from the logging feature in Maintenance Plans. 

Enabling Logging

For this example, I setup a Maintenance Plan that runs a DBCC CHECKDB statement on a corrupt database. 

First, I double click to edit the Maintenance Plan. Once the Maintenance Plan window opens,  go to the Reporting and Logging window by clicking on the icon highlighted below.

The window shown below should now pop up.  In this scenario, I select the following options:

Generate a text file report and the option to Create a new file.  When creating a new file SQL Server will default to the local host default log folder specified during the setup process. You can change this by clicking on the button with the elipses. 

When you execute the plan, it will create a .TXT file in the folder you specified, with the name of the maintenance plan and subplan along with the date and time, as shown below. Here is the log for one of my plans.

In this case, there is corruption in the database.  The log file will show the details on of the corruption. I have an extract of the log file shown below.

This is in contrast to the SQL Server error log. If I examine this log, it will only show the following:

In this case, the log file from the Maintenance Plan displays the details of the Database Integrity Task, allowing you to better understand the issue. 

The Reporting and Logging window also has an option to Log Extended Information.  In this case, I setup a maintenance plan that backs up a database. I can open the Reporting and Logging tab again and check that box (highlighted below).

By setting the Log Extended Information option, I can see the T-SQL the maintenance plan generated. In this case a great deal more information is in the Maintenance Plan log file (shown below).   

The SQL Server Logs shows the results of the backup and device information.

To sum up, Maintenance Plans can provide some good information when troubleshooting issues in SQL Server.  The more I learn about SQL Server the more I realize, that I may not get all the information I need from one place, but I can put all the pieces together from all the different sources SQL Server provides and determine the issue(s).  

Total article views: 2375 | Views in the last 30 days: 1
Related Articles

Unable to create Maintenance Plan

Absolute path information is required (mscorlib) - Maintenance Plan error


How Maintenance Plans are a Big Plus for SQL Server Administration

SQL Server 2012 makes your database maintenance jobs easy by proving an effective Maintenance Plan W...


VBScript Classes to Query SQL Server for Backup Information

A VBscript class is created that can be used to query the maintenance plans on an SQL server to dete...


How to Use SQL Backup Inside a Maintenance Plan

When you create a SQL Server Maintenance Plan using the Maintenance Plan Wizard or the Maintenance P...


maintenance plans in sql server 2005 64-bits

problem creating maintenance plans in sql server 2005 64-bits