Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Maintenance Plans Reporting and Logging


By:   |   Last Updated: 2014-05-01   |   Comments (1)   |   Related Tips: More > Maintenance

Problem

Maintenance Plans are very common in a SQL Server environment. They can be used for various things and sometimes makes life easier for a DBA to quickly create a plan for backups, index maintenance, or to run a quick integrity check. One feature that is often overlooked is the maintenance plan reporting and logging. In this tip we’ll discuss how to setup reporting and logging for a maintenance plan and the benefits of using this option.

Solution

This tip will focus on the reporting and logging option of maintenance plans. MSSQLTips.com offers a variety of tips regarding creating maintenance plans, maintenance plan tasks, etc.

When a maintenance plan executes it’s nice to know the results especially in case of a failure. You can view the results in a few different ways that include the following:

Maintenance Plan Reporting and Logging Options

The Maintenance Plan reporting and logging option is enabled by default, but a lot of DBA’s and developers don’t even realize it is an option, much less that it’s enabled.

To configure this option, open a maintenance plan and on the top bar beside Manage Connections…. you’ll notice a little chart/paper icon. It’s not hidden, but it doesn’t jump out at you and that’s probably why a lot of DBA’s don’t pay any attention.

The Maintenance Plan reporting and logging option is enabled by default

If you click the icon you’ll notice there are a few options to choose from:

If you click the icon you’ll notice there are a few options to choose from

Let’s go over each one of these:

Generate a text file report

This option allows you to enable or disable the text file report.

Create a new file

This option allows you to create a new report file each time a maintenance plan is executed. Create a new file is the default option and the default folder location is the folder you specified SQL Server to use for the LOG folder. You can specify a different location if preferred.

Here is a screenshot of Windows Explorer where a new file is created each execution:

Windows Explorer where a new file is created each execution

You may notice that if you run a maintenance plan throughout the day that it could quickly fill up your drive with these 1kb files. Luckily, we don’t have to go in and check our file system and delete these files manually. SQL Server has a task that will automate this for us (see below).

While we are at this point, go ahead and check one of your servers. Check the location of a maintenance plan text file and then check the folder on the server. Or maybe you’ve noticed these files while browsing through your LOG folder and wasn’t sure where they came from.

Append to file

This option allows you to create one text file and append the results to that file. This will reduce the number of files, but will increase the size of the file and it makes it more difficult to read in my opinion.

Send report to an email recipient

This options utilizes Database Mail to send a copy of the report via email. This option is only available if Database Mail is enabled and properly configured. You must also have an operator created. You can view more tips regarding Database Mail here.

Agent Operator

Select the operator that you would like the report sent to.

Log extended information

This option will include more information in the log. Selecting this option will increase the size of the stored maintenance plan history. See below for an example.

Log to remote server

This option allows you to log information to a different server. For example, if you decided you wanted to host all your maintenance plan reports to a central server, you could configure this option on all of your maintenance plans.

Now that we’ve gone over each of the options on the Reporting and Logging windows, let’s look at a sample report:

Sample Output #1

This is an example of a basic maintenance plan that includes Check Database Integrity and Rebuild Indexes tasks that succeeded. You will notice that it displays information such as ServerName, Maintenance Plan name, Duration, Status, and details of each task:

Each of the options on the Reporting and Logging windows

This is an example of basic maintenance plan that failed. It shows basically the same information as the plan that succeeded except it will display the reason why the plan failed. In this example, you’ll notice that during an index rebuild a timeout occurred while waiting for a buffer latch type 4 on a page:

This is an example of basic maintenance plan that failed.

Sample Output #2

This is an example of a basic maintenance plan that includes Check Database Integrity and Rebuild Indexes tasks that succeeded and we enabled Log Extended Information.

If Log Extended Information is selected it will display all the T-SQL code that ran, for example on a Check Database Integrity task, it will show the command used:

If Log Extended Information is selected it will display all the SQL that ran

Here is what it looks like for a Rebuild Indexes task:

Check Database Integrity task

Maintenance Plan Reports Cleanup Task

As mentioned, these report files can accumulate over time, so in our maintenance plans we can add the Maintenance Cleanup Task and it will purge these files automatically:

Add the Maintenance Cleanup Task and it will purge these files automatically

Open the Maintenance Cleanup Task properties and choose "Maintenance Plan text reports", choose the location of the text reports folder, and choose the duration in which you want to begin deleting the reports:

Open the Maintenance Cleanup Task properties and choose “Maintenance Plan text reports”

I usually just add this task to the end of my maintenance plans so I don’t ever have to worry about manually deleting them.

 
Next Steps
  • To view all of MSSQLTips.com maintenance plan tips, click here.
  • Remember to be aware of these text files, it takes a lot to fill up a drive since they are only 1kb, but it’s still good practice to keep these files cleaned up.


Last Updated: 2014-05-01


next webcast button


next tip button



About the author
MSSQLTips author Brady Upton Brady Upton is a Database Administrator and SharePoint superstar in Nashville, TN.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Friday, June 26, 2015 - 3:57:22 PM - Humberto Sa Back To Top

I'm trying to send the text report generated by the maintenance plan by email ONLY WHEN IT FAILS as an attachment.

SSMS gives us the option to mail the report but this would mean for all cases. I just want when it fails.

 

The problem is to find a way to get the exact name of the output text file on disk and send it as an attachment as the job step fails.

 

Do you guys have any idea on how to do this the easy way?

Thanks.

 


Learn more about SQL Server tools