I recently came across this message in the SQL Error Logs. This message indicates that another process, such as OS or another application on the database server is taking memory away from SQL Server.
Thankfully, finding the problem was a simple process for me (you may not be so lucky!). The error only occured out of hours and the times were relatively consistent. A quick call to the infrastructure team and I discovered they had turned on a replication process (HP StorageWorks Storage Mirroring) without letting me know. A quick rant and the process was disabled. I checked the logs the following morning and the errors had gone.
I had not seen the error before so I natually googled the issue and read a number of interesting forums and blogs. As my resolution was pretty straightforward, I have added some of the more interesting links that I read.
http://support.microsoft.com/kb/918483
http://blogs.msdn.com/psssql/archive/2007/05/31/the-sql-server-working-set-message.aspx
http://weblogs.asp.net/omarzabir/archive/2007/10/19/a-significant-part-of-sql-server-process-memory-has-been-paged-out-this-may-result-in-performance-degradation.aspx
I’ve seen a few posts about daily checks recently and thought I’d chip in.
What do I check and a very brief explanation why I perform this check.
Backups
- Have they all succeeded?
Disk Space
- Do we have plenty of space available for normal operations during business hours?
Database Size
- Have we seen a major increase in size (I monitor this because we manage client databases)
Scheduled Jobs
- Have any failed? Or do any of the steps have warnings?
Event Viewer (application, security, server)
- Any errors at all relating to Security, SQL Server, the OS, hardware etc
SQL Error Log
- Check the logs to ensure there are no errors, events raised which may be of concern
Cluster Log
- Are there any errors, warnings?
Replication Logs
- Are all subscriptions in sync?
Maintenance Reports
- Automated rebuilds/reorganization of indexes and updating of stats. What was done? What wasn’t done?
Monitoring System eMails
- Any alerts over night from any of the database servers, web servers, application servers?
Overnight processing eMails
- Did all batch jobs run as expected?
Most of these are now automated processes. I capture the data on a daily basis in a seperate database for trends analysis & capacity management.
I have been asked by people why do I do all this? Quite simply, to ensure the stability of my production environment. Now, we do use a monitoring tool which alerts us to low disk space, errors in logs etc, but if we did not have this, then how would I ever know that my drives were running low on space or how would I know that an overnight report for Sales, for example, has not run?
It can be a mundane process, for sure, but I’ve been performing these kind of checks for long enough, it has just become routine for me in all of my jobs.