Login or Sign Up to become a member!

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

From Wiki

Contents [Hide] 1 Introduction 2 Monitor Daily 3 Monitor Weekly 4 Monitor Monthly 5 Monitor Quarterly 6 Monitor Yearly

Introduction

This is just a quick checklist of all the things that a DBA needs to monitor on a daily, weekly, monthly, quarterly and yearly basis. Of course there are events that you want to know about immediately, for those events you can setup alerts or you can invest in a tool which will monitor SQL Server and alert you when something goes wrong

Monitor Daily

Check the SQL Server error log
Check the error log daily or better yet several times per day. If you can set up alerts when things get written to the error log with a certain severity then try to do so

Check for failed backups
The last thing you want to know is that your backup process has failed the day that you need to restore a database

Check for free disk/file space
You don't want users to tell you that their transactions are failing because the log or data file is full

Error log/windows event logs
Catch problems early, don't wait for the whole system to crash, if you catch it early enough you could prevent a disaster

Monitor Buffer cache hit ratio and Page life expectancy
Both of these counters are an indicator that you need more memory

Check for failed SQL Agent jobs
Make sure that you get notified when a job fails, some jobs might be time sensitive or a pain in the neck to run the next day because they might be configured in such a way that they expect to work only for the current date

Monitor Weekly

Cycle The SQL Server Error Log
You don't want files that are huge, keep your log files small by recycling them once a week

Test your Full Recovery model by restoring backups
This is similar to make sure that your backups work, how do you know that files even if completed successfully can actually be restored

DBCC checks
Make sure that you do a DBCC CHECKDB regularly, this will catch corrupted tables and indexes and the overall health of your databases

Update statistics, check if statistics are stale
If you don't have auto update statistics enabled then you need to make sure that your statitics are not stale, otherwise your queries might be slow


Index maintenance
Make sure that indexex are not fragmented, here are some scripts that show you how you can find fragmented indexes: Finding Fragmentation Of An Index And Fixing It

Monitor Monthly

Disaster Recovery testing
Does your fail over strategy work, have you tried a mirror fail over? What happens if your whole datacenter goes down, do you have redundancy?

Monitor Quarterly

Capacity planning
Is your server in good shape to handle the extra data that will be stored in the next 6 months, do you have enough disk space, are the CPUs fast enough to hanle the load, do you need more CPUs? These are all question that you have to ask yourself before it is too late

Perfmon metrics (trending)
Did you create a baseline for your server, do you know what a normal load is? The Creating a baseline for SQL Server has a couple of ideas

Security audit
Are users still allowed in that should not have, are users account still on the server even though they have left the company?

Monitor Yearly

Data center/Hardware/Server planning
Do you have enough rack space and servers for the next two years?

Contributed by: --SQLDenis 19:04, 18 October 2011 (GMT)

Part of SQL Server Admin Hacks