Custom SQL Alerts for SQL Server

On this page:

Alert on SQL Server Database Size

Common metrics that DBAs and storage administrators want to track are how much space a database consumes at a given time and database consumption over time. These metrics can indicate when an individual database begins to consume excessive disk space and by analyzing these metrics you can also better forecast future storage requirements.

The following query returns the total space, data file space, and log file space consumed by each database within a SQL Server instance.

SQL query for database disk consumption

SELECT 
      database_name = DB_NAME(database_id)
    , total_size_mb = CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2))
    , data_size_mb = CAST(SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
    , log_size_mb = CAST(SUM(CASE WHEN type_desc = 'LOG' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
FROM sys.master_files WITH(NOWAIT)
GROUP BY database_id

You can schedule this SQL to run as a custom alert that returns tabular data. For example,

The results of this Custom SQL Alert can trigger an email, if Alert Type is set to Information Only (trigger email) or if Alert Type is set to Normal.

Clicking on the Status icon of the specific alert on the Alerts page reveals the history of the collected metrics.

If you click the Graph History link the historical details of the metrics appear, therefore showing space usage over time.

Note: Just the first two columns of data are charted.

For example, the following shows that the first two columns of data are charted.

Custom SQL Alerts on the Web

The following is a collection of articles on the web for the Microsoft SQL Server platform which contain SQL that you may find useful for creating custom SQL alerts for your environment. You can enter the SQL in the Custom Alerts SQL text box as described in Alert on Performance Metrics.

Alert on SQL Server Database Size

Custom SQL Alerts on the Web

Alert on IO Usage of SQL Data Files

Alert on Log Shipping Related Errors

Alert on Log Space Availability

Alert on Real Time Transactional Replication