DBA Checklist

To stay safe, a DBA of the SQL Server is required to be familiar with all the critical aspects. After knowing these key aspects he can create an environment in which nothing would be lost before the news or generating alerts.

Following is the basic SQL Server’s DBA Checklist and I found very detailed Checklist on Simple-Talk. 

• Connectivity:

1.    Check all SQL Server services are running

2.    Polls all servers ‘select @@servername’ every 10 minutes to check server availability

3.    Make sure each database is available and accessible both by logging in via user applications, as well as running test scripts.

• Backups:

1.    Check databases full backup(Get list of not recently backed up databases in last 7 days)

2.    Log backups

3.    Archiving

4.    Offsite storage

• Events:

1.    Check errors in your SQL Server Error Log and operating system event viewer and for errors or warnings.
2.  All database logs

3.    Application logs

4.    System logs

5.    Agent history

6.    Device logs

7.    NIC logs etc. Investigate any job failures

8.    Keep a log of any configuration changes you make to the servers.

9.    CRUD (Create, Read, Update, Delete) statements about database, table, view, UDF, triggers, stored procedures.

• Processes:

1.    Check that all required processes on the server are running

2.    Count of total SQL Agent Jobs and successfully run are equal.

3.    Make sure that SQL Mail, SQL Agent Mail, Database Mail or xp_SMTP_Sendmail is working correctly

4.    Export and then Delete all details of backup and restore processes created before a specific time

5.    Replication (If any )

• Integrity:

1.    Perform all database and server integrity checks; look for objects that break rules

(DBCC commands dbcc checkdb / dbreindex / checkcatalog / inputbuffer, sp_who, sp_kill)

• Indexes:

1.    Check on indexes to see if they are being used

2.    Need re-creation or modification in indexes are required

3.    If any are missing

• Volumetric:

1.    Check resources on the server such as files sizes (running out of space on any of disks)

2.    Disk space, CPU usage and memory (running low on server memory for SQL Server)

3.    Monitor growth

4.    Enough space available for all types of backup files

• Performance:

1.    Check application performance, long running or "hung" processes, especially those tied to a SQL Agent job

2.    Regular monitor Performance Counters

3.    Regular check on Blocking issues

4.     Count of Total requests  processing ( Total Batch Count)

5.    To avoid the AUTOGROWTH of the data files during the busiest time, manually increase their size.

6.    Performance statistics, using the Permanence tool; research and resolve any issues.

7.    For best I/O performance, locate the database files (.mdf) and log files (.ldf) on separate volumes on your server to isolate random writes from sequential ones

8.    “Auto create statistics” and “auto update statistics”  options should be on for all Databases in other case must manually update the statistics.

9.    If application supports snapshot isolation level, this will result in a Temp DB contention so it is recommended to divide the tempdb database into multiple files based on the number of CPU cores and to place it on a separate drive.

• Procedures:

1.    Check all Disaster Recovery Plans

• Security:

1.    Look for security policy violations(security plan according to needs)

2.    SQL Agent Job modifications, any schedule modification can have negative impact. Use a report that shows which jobs were modified and when, unfortunately it does not reveal "who",

3.    Count to Total connections to SQL Server instance