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).
