By: Brady Upton | Updated: 2014-05-01 | Comments (1) | Related: > 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 history
- SQL Server Agent Job history
- SQL Server Error Log
- sp_readerrorlog
- Maintenance Plan Reporting and Logging
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.
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:
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:
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:
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:
Here is what it looks like for a Rebuild Indexes 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:
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:
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.
About the author
Brady Upton is a Database Administrator and SharePoint superstar in Nashville, TN.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2014-05-01
