Free SQL Help for Devs, DBAs, and the Curious

The life of a Database Administrator (DBA) revolves around ensuring that database systems run efficiently and securely. A crucial part of this is daily monitoring and maintenance. In this post, we will be discussing a checklist that a DBA should follow every morning. Whether you're using SQL Server 2016, SQL Server 2017, MySQL 5.7, DB2 11.1, or Azure SQL, this checklist will guide you through the key areas that need to be monitored to ensure your databases are functioning optimally.

The first item to check is the server's health. This involves checking the server's CPU utilization, memory usage, disk space, and network performance. High CPU usage or low disk space could indicate a problem that needs immediate attention. For SQL Server, you can use the 'sys.dm_os_performance_counters' dynamic management view to check these parameters. For example:

SELECT object_name, counter_name, instance_name, cntr_value 
FROM sys.dm_os_performance_counters 
WHERE counter_name IN ('Processor Time', 'Available Bytes', 'Disk Queue Length', 'SQL Server: Buffer Manager');

Next, check for failed or long-running jobs. SQL Server Agent jobs, MySQL Events or DB2 Tasks that didn’t complete successfully or are taking longer than usual to complete are a red flag. For SQL Server, you can use the 'sysjobs' and 'sysjobhistory' tables in the 'msdb' database to check for failed or long-running jobs. Here's an example query:

SELECT j.name, h.run_status, h.run_date, h.run_time, h.run_duration 
FROM msdb.dbo.sysjobs j 
JOIN msdb.dbo.sysjobhistory h 
ON j.job_id = h.job_id
WHERE h.run_status != 1;

The third item to monitor is database growth. An unexpected increase in database size could indicate a problem. Use the 'sp_MSforeachdb' stored procedure in SQL Server, the 'information_schema.tables' table in MySQL, or the 'GET_DBSIZE_INFO' procedure in DB2 to monitor database size. Here's a SQL Server example:

EXEC sp_MSforeachdb 'USE [?]; EXEC sp_spaceused';

Finally, monitor the error logs for any unusual entries. Error logs can provide valuable information about issues that might not be immediately noticeable from the above checks. SQL Server, MySQL, and DB2 all provide system views or functions to access the error logs. For SQL Server, you can use the 'sp_readerrorlog' stored procedure.

EXEC xp_readerrorlog;

In conclusion, a DBA's morning checklist includes monitoring server health, checking for failed or long-running jobs, monitoring database growth, and reviewing error logs. Following this routine will help ensure that your databases remain healthy and run efficiently. Remember to adjust these checks as necessary to fit your specific environment and requirements.