Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 8 down vote favorite
7

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)

share|improve this question

6 Answers

up vote 2 down vote accepted

  1. Backups

    • Check for backup emails
    • How long did the backup take to run (database backup duration)
    • Verify that all databases are being backed up according to a maintenance plan

  2. Disk free space. Note significant variations from previous check. Log files may be affected dramatically by monthly jobs

  3. Job failures. Filter job activity for failures

  4. System checks. Look in sql logs for any critical errors.

    • Application logs

  5. Performance

    • Check performance statistics on all servers
    • Check that counters are in normal range on all production servers

  6. Connectivity

    • Verify the customer application can get data from the database
    • Verify acceptable speed of access data

  7. Replication. Verify that the each publication and distributor is running for each subscription

SQL Server DBA Checklist

Brad's Sure DBA Checklist

Oracle DBA Checklist (maybe useful)

SQL Server DBA database management checklist

DBA Morning Check List

MS SQL Server DBA Checklist (many checklists)

SQL Server DBA Checklist

share|improve this answer
up vote 3 down vote

Frequently check I/O queue length for bottlenecking.

share|improve this answer
up vote 3 down vote

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.

share|improve this answer
up vote 2 down vote

DISCLAIMER : Not a SQL Server DBA

If possible, you may want to check monthly for indexes that are not being used by any queries. This you would definitely want to do for

  • very large tables
  • tables with many indexes
  • indexes with many columns (3 or more)
share|improve this answer
3  
Just be sure that "not being used" reflects an entire business cycle. I have heard of multiple cases where the DBA decided to drop an index that hadn't been used in a couple of months, and the next day the CFO's quarterly report takes hours instead of seconds... you can't rely on the index_usage_stats DMVs, especially if your server is restarted periodically, so I would only do this if you are keeping your own usage statistics over time... – Aaron Bertrand Aug 20 '11 at 1:14
up vote 1 down vote

Something to help out with accomplishing it...Idera put out a free tool for reviewing SQL Server Jobs that I have used a few times. It is very good for getting a good overview, although it does have a few limitations since it is free. Worth checking: http://www.idera.com/Products/Free-Tools/SQL-job-manager/

Something I would add for the security side of the house...A trace file specifically for capturing logon activity for user accounts. This will allow you to find inactive accounts easily. Then also script that monitors when someone is added to fixed server/database roles. Especially sysadmin, if you are not the only one managing the server/instance.

share|improve this answer
Is a trace file the best way to do this? – Thomas Stringer Aug 19 '11 at 20:14
it is the easiest way that I know to get the info. Unless you put in a trigger to capture the information to a table or log maybe. If using SQL 2008 Policy management can be used for this purpose. – Shawn Melton Aug 19 '11 at 20:23
A trace may be the best way, @ShawnMelton. There's a way to modify the registry (sqlservercentral.com/articles/security/sqlserverauditingpart1/…) so that SQL Server audits all logins (successful and failures). I'm not sure what the best means is, but I have just always been apprehensive about keeping a trace running indefinitely. Your thoughts? – Thomas Stringer Aug 20 '11 at 2:09
I have never had issue running trace files where they effected performance that much. Enabling C2 auditing though I have, I don't like enabling that. Extended Events offer an alternative and are supposed to be the preferred method to using trace files, more power with them. You might check into those to see if there is an option for login events, I'm sure there is. From what I understand about them, they are excluded somehow from causing a hit with performance. – Shawn Melton Aug 20 '11 at 3:07
nice. I am inclined to agree with you. And yes, C2 is definitely one of those only-use-it-if-you-need-to situations. – Thomas Stringer Aug 20 '11 at 3:18
show 1 more comment
up vote 0 down vote
  • check SQL Server and SQL Server Agent Error Log
  • check the status of mirrored servers (principal and mirror)
  • check jobs execution time changes
  • check the active node in clustered sql server
  • check DISK SPACE
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.