SQL Server 2008 High volume maintenance plan logs

A nice way to implement simple batch processes in SQL Server is by creating a stored procedure that gets run periodically by a maintenance plan. I’ve used this frequently and it gets the job done. However, if the maintenance plan is executed frequently, the logs quickly fill up. The default behaviour will be to always log, whether the plan succeeded or failed. The logs of all maintenance plans are kept in the ‘Log’ folder of your SQL Server instance installation path. Symptoms of a overloaded log folder are that looking at the history of the maintenance plan takes forever. I’ve been searching for a way to get this toned down.

Ideal situation for me would be:

1. only log errors (with as much detail as possible)
2. log when was the plan last run and if it was succesfull

Unfortunately it doesn’t seem to be possible to only log errors. It’s all or nothing.

Let’s have a look at the possible options.

Option 1: Create logs in different folders to minimize number of log files per folder

The log history is stored in one folder for every maintenance plan. If you’re editing a maintenance plan click the Reporting and Logging button.

After clicking the Reporting and Logging button you will see:

In the dialog there are options to:

– store the logs to a new text file for every execution
– append the logs of every execution to the same file
– send the report to an e-mail recipient (if Database Mail is configured properly)
– Add extended information to the logs
– Log to a remote server

There is no option to only log when an error has occurred.

Option 2: Disable logging

Next my search turned to the properties of the maintenance plan itself.

There is a property called ‘LoggingMode’. To my dismay, when I hit F1 on this property I actually get forwarded to Visual Studio documentation about property pages, so no information about the LoggingMode property! After this grumble, I remembered that a maintenance plan is actually nothing more than a SSIS package that gets executed.
So this property maps to DTSLoggingMode.
This property has three possible values, which basically come down to on or off. So this roads is also a dead end. I don’t want to disable all logging, I just want to disable logging succesfull executions.

Option 3: Keep logs from growing infinitely

As I’ve found no way to tone down down the logging, I’ve decided to search for ways to keep the logs from growing infinitely. There’s a maintenance task called ‘History Cleanup Task’. With this task the history of the maintenance plans can be deleted. This is also all or nothing. I can’t delete only the logs of a certain plan that gets executed very often.

Conclusion

Logging maintenance plans works, but when the volume of the logs get high, fine tuning is almost impossible. I’ve decided to spread the logs of every maintenance plan with high volume logs to a different folder. That should speed up the search for the log history. Also I’ve created a maintenance plan that removes logs older than 3 days. As the stored procedures do their own logging in the database itself, this shouldn’t be a big issue. As the application that uses this database is used very intensely I usually know within 3 days if a serious error has occurred. Although I’d rather keep this window somewhat wider. I could also create an application that copies the logs every three days, so more history is kept in a safe place, should I need it. Finally, I’ve found an application called ‘DTLoggedExec‘ which might solve my problems. I’ll have to dive into this another time, to see if it would fit this issue also.

Leave a Reply

Your email address will not be published. Required fields are marked *