SQL Server Checklists

Following up from my other question, I would like to start thinking about what I should take a look at on daily/weekly/monthly bases in terms of alerts. I am hoping to be able to see problems coming before they happen (that is the plan)...

So far, I have made a start collecting scripts for the following (no order):

Daily

• Check system uptime (just in case I need to check anything as a DBA)

• Check the last backup

• Check the transaction log backups

• Check the status of SQL Jobs

• Check the average CPU usage for the last 24 hours (or 1140 mins)

Weekly

• Check MSDB backup history

• Check to see when the last time CheckDB was run

• Check index fragmentation

• Check index stats (reads vs writes etc)

• Check for IO bottlenecks

Monthly

• Check missing indexes

• Check indexes that are no longer used

Any other suggestions? (I am new to DBA so any help/advice is always welcome)

Only variation I'd suggest on your check list is to replace the word BACKUP with RESTORE. Checking that backups complete is a good start, what really matters is whether or not you can restore from them. Alert on a backup failure, automate a random sampling of restores so you know your backups are good.

The next step on from a daily/weekly/monthly check list is history. A check on x/y/z performance counters is meaningless without a baseline to compare today with yesterday. Without understanding the today vs yesterday, it's impossible to predict next month.

StackExchange Database Administrators Q#4836, answer score: 4