Daily Health Check Scripts for ETL-Heavy Environments

By Tom Nonmacher

Maintaining a healthy and productive environment is crucial for ETL-heavy environments. Today, we will discuss some daily health check scripts that can be used with SQL Server 2012, SQL Server 2014, MySQL 5.6, DB2 10.5 and Azure SQL to ensure the smooth operation of your databases. These scripts will help you monitor the health of your servers and databases, thus enabling you to resolve any issues before they degrade the performance or even cause an outage.

Let's start with SQL Server. One of the key health check aspects is monitoring the size and growth of your databases. This information can be obtained by querying the system views 'sys.databases' and 'sys.master_files'. Here is a simple script that gets the current size and the remaining free space of each database:

SELECT
    name AS DatabaseName,
    size/128.0 AS CurrentSizeMB,
    size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS FreeSpaceMB
FROM
    sys.master_files
WHERE
    type = 0 -- 0 = rows
ORDER BY
    DatabaseName;

Now, moving on to MySQL. Checking the status of your MySQL server is a good start to ensure the health of your databases. The 'SHOW STATUS' command provides server status information. Here is an example that shows status variables that indicate how many types of each select statement have been performed since the server was started:

SHOW STATUS LIKE 'Com_select';

For DB2, you can use the DB2 Health Monitor that includes several health indicators to monitor the status of your databases. You can easily enable the Health Monitor with the following command:

UPDATE DB CFG FOR SAMPLE USING HEALTH_MON ON;

Lastly, we have Azure SQL. Azure provides a comprehensive set of metrics and alerts that you can configure to monitor the health of your databases. For instance, you can create an alert to get notified when the DTU consumption percentage is above a certain threshold. Here is an example of such an alert:

-- Define the alert criteria
var criteria = new MetricAlertCriteria
{
    MetricName = "dtu_consumption_percent",
    TimeAggregation = "Average",
    Operator = "GreaterThan",
    Threshold = 80,
    DimensionName = "DatabaseName",
    DimensionValue = "YourDatabase"
};

-- Create the alert
var alert = new MetricAlert
{
    Name = "High DTU Consumption",
    Description = "Alert when DTU consumption is over 80%",
    Severity = 1,
    Enabled = true,
    Scopes = new List { $"/subscriptions/{subscriptionId}/resourceGroups/{resourceGroupName}/providers/Microsoft.Sql/servers/{serverName}/databases/{databaseName}" },
    EvaluationFrequency = TimeSpan.FromMinutes(5),
    WindowSize = TimeSpan.FromMinutes(15),
    Criteria = criteria
};

In conclusion, monitoring your database servers and executing daily health checks are essential tasks in maintaining a healthy ETL-heavy environment. While the scripts and commands mentioned in this blog post are a good starting point, you should tailor them according to your specific needs and environments. Remember, proactive monitoring and maintenance are always better than reactive troubleshooting.

Check out the latest articles from all our sites:




6D2D18
Please enter the code from the image above in the box below.