This post will focus on the reporting and logging option of maintenance plans.
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.