Daily Checklist

 Backups - Check your backups to validate that they were successfully created
per your process.
o MSSQLTips.com Category: Backup and Recovery
 Nightly Processing - Review the nightly or early morning processes.
 SQL Server Error Log - Review the SQL Server error log for any errors or
security issues (successful or failed logins) that are unexpected.
o SQL Server 2005 Exposed - Log File Viewer
 Windows Event Log - Review the Application Event Log at a minimum to find
out if any Windows or hardware related errors or warnings are being written.
o Some of the hardware vendors write warnings to the Windows Event
Log when they anticipate an error is going to occur, so this gives you
the opportunity to be proactive and correct the problem during a
scheduled down time, rather than having a mid day emergency.
o SQL Server 2005 Exposed - Log File Viewer
 SQL Server Agent Jobs - Review for failed SQL Server Agent Jobs.
o Finding SQL Server Agent Job Failures
 HA or DR Logs - Check your high availability and/or disaster recovery process
logs.  Depending on the solution (Log Shipping, Clustering, Replication,
Database Mirroring, CDP, etc.) that you are using dictates what needs to be
checked.
 Performance Logs - Review the performance metrics to determine if your
baseline was exceeded or if you had slow points during the day that need to
be reviewed.
o MSSQLTips.com Category: Performance Tuning
 Security Logs - Review the security logs from a third party solution or from
the SQL Server Error Logs to determine if you had a breach or a violation in
one of your policies.
 Centralized error handling - If you have an application, per SQL Server or
enterprise level logging, then review those logs for any unexpected errors.
o Standardized Error Handling and Centralized Logging
 Storage - Validate you have sufficient storage on your drives to support your
databases, backups, batch processes, etc. in the short term.
 Service Broker - Check the transmission and user defined queues to make
sure data is properly being processed in your applications.
o MSSQLTips.com Category: Service Broker
 Corrective Actions - Take corrective actions based on the issues and/or errors
that you found.
 Improvements - Look for opportunities to improve your environment based on
the review and analysis you have performed.
 Learn something new - Although this review and correction process could be
time consuming, take some time every day to learn something new to
improve your knowledge of the technology you work on every day.

Weekly or Monthly Checklist

 Backup Verification (Comprehensive)- Verify your backups and test on a

regular basis to ensure the overall process works as expected.  What is meant
by this is to:
o Contact your off site tape vendor to obtain a tape
o Validate that the tape goes to the correct office
 o Validate that the vendor delivers the correct tape
o Validate that the vendor delivers the tape in the correct time period
o Validate that the software version you use to perform the restore is
compatible with the version from the tape
o Validate that the tape does not have any restore errors
o Validate that sufficient storage is available to move the backup to the
needed SQL Server
o Validate that the SQL Server versions are compatible to restore the
database
o Validate that no error messages are generated during the restore
process
o Validate that the database is accurately restored and the application
will function properly
 Backup Verification (Simple) - Verify your backups on a regular basis.
o Maintenance Tasks: Automating the RESTORE VERIFYONLY Process
o Verifying Backups with the RESTORE VERIFYONLY Statement
 Windows, SQL Server or Application Updates - Check for service
packs/patches that need to be installed on your SQL Server from either a
hardware, OS, DBMS or application perspective
o MSSQLTips.com Category: Service Packs / Patches
 Capacity Planning - Perform capacity planning to ensure you will have
sufficient storage for a specific period of time such as for 6, 12 or 18 months.
o Easing the Capacity Planning Burden
 Fragmentation - Review the fragmentation for your databases to determine if
you particular indexes must be rebuilt based on analysis from a backup SQL
Server.
o MSSQLTips.com Category: Fragmentation
 Maintenance - Perform database maintenance on a weekly or monthly basis.
o MSSQLTips.com Category: Maintenance
 Security - Remove unneeded logins and users for individuals that have left
the organization, had a change in position, etc.
 Shrink databases - If databases or transaction logs are larger, than necessary
shrink those files to free up disk space.
o SQL Server 2000 to 2005 Crosswalk - Database Maintenance Plan
Wizard to SQL Server Integration Services (SSIS)

Opportunities for Automation

 Setup alerts for specific error levels or error messages that impact your SQL
Servers in order to be notified automatically.
o Database Backup and Restore Failure Notifications
 Setup Jobs to query for specific conditions in your tables to validate data was
loaded or data is being added to specific tables based on your business
processes throughout the day.
 Setup notification on Job success, failure or completion.
o One word of warning is to check your business critical Jobs on a
regular basis just to be sure they are working properly.  Nothing is
worse than finding out a key process has been failing for days, weeks
or months and the reason notifications have not been sent are due to
an incorrect configuration, full mailbox, etc.  It may be 30 minutes on
a weekly basis that is time well spent.
  Setup centralized error handling on a per SQL Server, application or
enterprise basis then determine the business rules for specific error
conditions.

Next Steps

 Based on your environment and your needs, build the daily, weekly and
monthly checklist that is needed.
 As a start it may be easier to manually check for specific business or data
conditions, then as you build your scripts over time work towards assembling
them for an automated process.

Another Article

SQL Server DBA Checklist

March 17, 2008 by JShah

1. Check OS Event Logs, SQL Server Logs, and Security Logs for unusual events.
2. Verify that all scheduled jobs have run successfully.
3. Confirm that backups have been made and successfully saved to a secure location.
4. Monitor disk space to ensure your SQL Servers won’t run out of disk space.
5. Throughout the day, periodically monitor performance using both System Monitor and
Profiler.
6. Use Enterprise Manager/Management Studio to monitor and identify blocking issues.
7. Keep a log of any changes you make to servers, including documentation of any
performance issues you identify and correct.
8. Create SQL Server alerts to notify you of potential problems, and have them emailed
to you. Take actions as needed.
9. Run the SQL Server Best Practices Analyzer on each of your server’s instances on a
periodic basis.
10. Take some time to learn something new as a DBA to further your professional
development.
11. Verify the Backups and Backup file size
12. Verifying Backups with the RESTORE VERIFYONLY Statement
13. In OFF-Peak Hours run the database consistency checker commands if possible