Monitoring maintenance plans for Project Server 2007
Updated: September 19, 2008
A database maintenance plan is only as good as its integrity is preserved, which requires monitoring. Operationally, maintenance tasks must be monitored for success and duration, and system performance must be monitored to identify system degradation before users report it. Microsoft recommends the use of solution monitoring software, such as System Center Operations Manager. Microsoft has released management packs for it that provide monitoring according to best practices for specific products involved in the Microsoft Office Project Server 2007 architecture, such as Microsoft SQL Server, Windows SharePoint Services 3.0, Microsoft Office SharePoint Server 2007, and Office Project Server 2007.
Some maintenance tasks, such as nightly backups or hourly transactional log backups, may have a substantial impact on system performance or data integrity if they fail to operate. Therefore, it is important to monitor their execution. Most operational environments have an extant alarming system — Project Server database maintenance plan execution should be integrated with these if possible. If this is not possible, basic alarming can be quickly implemented through SQL Server Agent's Operator mechanism combined with SQLMail via SMTP. The SQL Server Agent jobs created when a maintenance plan is scheduled can be edited to add operator notification upon maintenance task completion, success, or failure. Depending on the solution scale and operational maturity of the environment, a mixture of positive ("the task ran successfully") and negative ("an error occurred") notifications are suggested. This mixture may change over time — during initial implementation and test periods, it is reassuring to receive regular notifications, which may not be necessary as the system matures.
For more information, see the following articles:
Using SQL Server Profiler (http://go.microsoft.com/fwlink/?LinkId=128509&clcid=0x409)
Sample profiler trace template for Office Project Server 2007 (http://blogs.msdn.com/chrisfie/archive/2008/02/29/running-sql-server-profiler-with-project-server.aspx)