Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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: 2134 | Views in the last 30 days: 125
 
Related Articles
FORUM

Unable to create Maintenance Plan

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

ARTICLE

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

ARTICLE

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

BLOG

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

BLOG

Why I don’t like maintenance plans

If you create a maintenance plan (SQL 9.0.3042) to backup databases and select All databases as be...

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones