DBA Scripts: Collect Capacity and Temp DB Baselines

Three Collectors for Capacity and TempDB

DatabaseGrowth and VlfCount are system-versioned temporal tables, always current, no manual history-cleanup needed. The TempDB collector snapshots both file-level space and the top 10 session consumers in one pass. Each script below is a generator: run it, review the DDL it prints, then run that DDL on the target instance.

Generate-CollectorJob-Tempdb.sql

Captures file-level TempDB space (row_type = 'file') and the top 10 session-level consumers (row_type = 'session') in a single table, every 10 minutes.

/*
Script Name : Generate-CollectorJob-Tempdb
Category    : collectors
Purpose     : Generates DDL to create the DBA - Collect TempDB SQL Agent job.
              Creates the target database and collector.Tempdb table if absent,
              then outputs T-SQL to install a recurring TempDB space snapshot job.
              Captures file-level space (row_type = 'file') and top session consumers
              (row_type = 'session') in a single table using the row_type discriminator.
              Edit parameters, review output, then run on the target instance.
Author      : Peter Whyte (https://sqldba.blog)
Requires    : sysadmin (to run generated DDL); VIEW SERVER STATE, VIEW DATABASE STATE at job runtime
Notes       : Default interval: every 10 minutes.
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;

-- ── Parameters ────────────────────────────────────────────────────────────────
DECLARE @TargetDatabase  sysname       = N'DBAMonitor';   -- created if absent
DECLARE @JobOwner        sysname       = N'sa';
DECLARE @CategoryName    nvarchar(128) = N'DBA Collectors';
DECLARE @IntervalMinutes int           = 10;
-- ─────────────────────────────────────────────────────────────────────────────

DECLARE @q       nchar(1)      = NCHAR(39);
DECLARE @crlf    nvarchar(2)   = CHAR(13) + CHAR(10);
DECLARE @ddl     nvarchar(max) = N'';
DECLARE @jobName sysname       = N'DBA - Collect TempDB';
DECLARE @stepCmd nvarchar(max);

-- ── Step command (| = single-quote placeholder) ────────────────────────────────
SET @stepCmd = REPLACE(
N'SET NOCOUNT ON;
INSERT INTO [<<DB>>].[collector].[Tempdb]
    (server_name, collection_time, row_type, file_name, physical_name, file_type,
     file_size_mb, total_allocated_mb, free_mb, user_objects_mb, internal_objects_mb,
     version_store_mb, mixed_extents_mb, session_id, login_name, host_name,
     program_name, session_user_objects_mb, session_internal_objects_mb)
SELECT
    @@SERVERNAME                                                            AS server_name,
    GETDATE()                                                               AS collection_time,
    |file|                                                                  AS row_type,
    f.name                                                                  AS file_name,
    f.physical_name,
    f.type_desc                                                             AS file_type,
    CAST(f.size * 8.0 / 1024 AS decimal(10,2))                             AS file_size_mb,
    CAST(fu.total_page_count                   * 8.0 / 1024 AS decimal(10,2)) AS total_allocated_mb,
    CAST(fu.unallocated_extent_page_count      * 8.0 / 1024 AS decimal(10,2)) AS free_mb,
    CAST(fu.user_object_reserved_page_count    * 8.0 / 1024 AS decimal(10,2)) AS user_objects_mb,
    CAST(fu.internal_object_reserved_page_count * 8.0 / 1024 AS decimal(10,2)) AS internal_objects_mb,
    CAST(fu.version_store_reserved_page_count  * 8.0 / 1024 AS decimal(10,2)) AS version_store_mb,
    CAST(fu.mixed_extent_page_count            * 8.0 / 1024 AS decimal(10,2)) AS mixed_extents_mb,
    NULL AS session_id,
    NULL AS login_name,
    NULL AS host_name,
    NULL AS program_name,
    NULL AS session_user_objects_mb,
    NULL AS session_internal_objects_mb
FROM tempdb.sys.dm_db_file_space_usage fu
JOIN tempdb.sys.database_files f ON f.file_id = fu.file_id

UNION ALL

SELECT
    server_name, collection_time, row_type, file_name, physical_name, file_type,
    file_size_mb, total_allocated_mb, free_mb, user_objects_mb, internal_objects_mb,
    version_store_mb, mixed_extents_mb, session_id, login_name, host_name,
    program_name, session_user_objects_mb, session_internal_objects_mb
FROM (
    SELECT TOP 10
        @@SERVERNAME                                                                                        AS server_name,
        GETDATE()                                                                                            AS collection_time,
        |session|                                                                                            AS row_type,
        NULL AS file_name, NULL AS physical_name, NULL AS file_type, NULL AS file_size_mb,
        NULL AS total_allocated_mb, NULL AS free_mb, NULL AS user_objects_mb,
        NULL AS internal_objects_mb, NULL AS version_store_mb, NULL AS mixed_extents_mb,
        su.session_id,
        s.login_name,
        s.host_name,
        s.program_name,
        CAST((su.user_objects_alloc_page_count - su.user_objects_dealloc_page_count) * 8.0 / 1024 AS decimal(10,2))     AS session_user_objects_mb,
        CAST((su.internal_objects_alloc_page_count - su.internal_objects_dealloc_page_count) * 8.0 / 1024 AS decimal(10,2)) AS session_internal_objects_mb
    FROM sys.dm_db_session_space_usage su
    JOIN sys.dm_exec_sessions s ON s.session_id = su.session_id
    WHERE su.session_id > 50
      AND (su.user_objects_alloc_page_count + su.internal_objects_alloc_page_count) > 0
    ORDER BY (su.user_objects_alloc_page_count + su.internal_objects_alloc_page_count) DESC
) AS top_sessions;'
, N'|', NCHAR(39));

SET @stepCmd = REPLACE(@stepCmd, N'<<DB>>', @TargetDatabase);

-- ═══════════════════════════════════════════════════════════════════════════════
-- DDL output
-- ═══════════════════════════════════════════════════════════════════════════════
SET @ddl =
    N'-- ================================================================' + @crlf +
    N'-- Generated by Generate-CollectorJob-Tempdb.sql'                    + @crlf +
    N'-- Server    : ' + @@SERVERNAME                                      + @crlf +
    N'-- Target DB : ' + @TargetDatabase                                   + @crlf +
    N'-- Generated : ' + CONVERT(nvarchar(20), GETDATE(), 120)             + @crlf +
    N'-- ================================================================' + @crlf + @crlf;

-- ── 1. Target database ────────────────────────────────────────────────────────
SET @ddl +=
    N'IF NOT EXISTS (SELECT 1 FROM sys.databases WHERE name = N' + @q + @TargetDatabase + @q + N')' + @crlf +
    N'    CREATE DATABASE [' + @TargetDatabase + N'];'                                               + @crlf +
    N'GO' + @crlf + @crlf;

-- ── 2. Collector schema ───────────────────────────────────────────────────────
SET @ddl +=
    N'IF NOT EXISTS (SELECT 1 FROM [' + @TargetDatabase + N'].sys.schemas WHERE name = N' + @q + N'collector' + @q + N')' + @crlf +
    N'    EXEC [' + @TargetDatabase + N'].sys.sp_executesql N' + @q + N'CREATE SCHEMA collector' + @q + N';'              + @crlf +
    N'GO' + @crlf + @crlf;

-- ── 3. Tempdb table ───────────────────────────────────────────────────────────
SET @ddl +=
    N'IF NOT EXISTS (' + @crlf +
    N'    SELECT 1 FROM [' + @TargetDatabase + N'].sys.objects o'                                                        + @crlf +
    N'    JOIN [' + @TargetDatabase + N'].sys.schemas s ON s.schema_id = o.schema_id'                                    + @crlf +
    N'    WHERE o.name = N' + @q + N'Tempdb' + @q + N' AND s.name = N' + @q + N'collector' + @q + N')'                 + @crlf +
    N'CREATE TABLE [' + @TargetDatabase + N'].[collector].[Tempdb] ('                                                    + @crlf +
    N'    id                          bigint IDENTITY(1,1) PRIMARY KEY,'                                                  + @crlf +
    N'    server_name                 nvarchar(128) NOT NULL,'                                                             + @crlf +
    N'    collection_time             datetime2     NOT NULL,'                                                             + @crlf +
    N'    row_type                    nvarchar(10),'                                                                       + @crlf +
    N'    file_name                   nvarchar(128),'                                                                      + @crlf +
    N'    physical_name               nvarchar(260),'                                                                      + @crlf +
    N'    file_type                   nvarchar(60),'                                                                       + @crlf +
    N'    file_size_mb                decimal(10,2),'                                                                      + @crlf +
    N'    total_allocated_mb          decimal(10,2),'                                                                      + @crlf +
    N'    free_mb                     decimal(10,2),'                                                                      + @crlf +
    N'    user_objects_mb             decimal(10,2),'                                                                      + @crlf +
    N'    internal_objects_mb         decimal(10,2),'                                                                      + @crlf +
    N'    version_store_mb            decimal(10,2),'                                                                      + @crlf +
    N'    mixed_extents_mb            decimal(10,2),'                                                                      + @crlf +
    N'    session_id                  int,'                                                                                 + @crlf +
    N'    login_name                  nvarchar(128),'                                                                      + @crlf +
    N'    host_name                   nvarchar(128),'                                                                      + @crlf +
    N'    program_name                nvarchar(128),'                                                                      + @crlf +
    N'    session_user_objects_mb     decimal(10,2),'                                                                      + @crlf +
    N'    session_internal_objects_mb decimal(10,2)'                                                                       + @crlf +
    N');'                                                                                                                  + @crlf +
    N'GO' + @crlf + @crlf;

-- ── 4. Agent category ─────────────────────────────────────────────────────────
SET @ddl +=
    N'USE msdb;' + @crlf +
    N'GO' + @crlf + @crlf +
    N'IF NOT EXISTS (SELECT 1 FROM msdb.dbo.syscategories WHERE name = N' + @q + @CategoryName + @q + N' AND category_class = 1)' + @crlf +
    N'    EXEC msdb.dbo.sp_add_category'                                                                                             + @crlf +
    N'        @class = N' + @q + N'JOB' + @q + N', @type = N' + @q + N'LOCAL' + @q + N', @name = N' + @q + @CategoryName + @q + N';' + @crlf +
    N'GO' + @crlf + @crlf;

-- ── 5. Job + step + schedule ──────────────────────────────────────────────────
SET @ddl +=
    N'-- Job: ' + @jobName + @crlf +
    N'IF EXISTS (SELECT 1 FROM msdb.dbo.sysjobs WHERE name = N' + @q + @jobName + @q + N')' + @crlf +
    N'    EXEC msdb.dbo.sp_delete_job @job_name = N' + @q + @jobName + @q + N', @delete_unused_schedule = 1;' + @crlf + @crlf +

    N'EXEC msdb.dbo.sp_add_job'                                                    + @crlf +
    N'    @job_name         = N' + @q + @jobName + @q + N','                       + @crlf +
    N'    @enabled          = 1,'                                                   + @crlf +
    N'    @owner_login_name = N' + @q + @JobOwner + @q + N','                      + @crlf +
    N'    @category_name    = N' + @q + @CategoryName + @q + N';'                  + @crlf + @crlf +

    N'EXEC msdb.dbo.sp_add_jobstep'                                                + @crlf +
    N'    @job_name          = N' + @q + @jobName + @q + N','                      + @crlf +
    N'    @step_id           = 1,'                                                  + @crlf +
    N'    @step_name         = N' + @q + N'Snapshot TempDB space usage' + @q + N',' + @crlf +
    N'    @subsystem         = N' + @q + N'TSQL' + @q + N','                       + @crlf +
    N'    @database_name     = N' + @q + N'master' + @q + N','                     + @crlf +
    N'    @command           = N' + @q + REPLACE(@stepCmd, @q, @q + @q) + @q + N',' + @crlf +
    N'    @retry_attempts    = 0,'                                                  + @crlf +
    N'    @on_success_action = 1,'                                                  + @crlf +
    N'    @on_fail_action    = 2;'                                                  + @crlf + @crlf +

    N'EXEC msdb.dbo.sp_add_schedule'                                               + @crlf +
    N'    @schedule_name        = N' + @q + @jobName + N' Every ' + CAST(@IntervalMinutes AS nvarchar(5)) + N'min' + @q + N',' + @crlf +
    N'    @freq_type            = 4,'                                               + @crlf +   -- daily recurring
    N'    @freq_interval        = 1,'                                               + @crlf +
    N'    @freq_subday_type     = 4,'                                               + @crlf +   -- minutes
    N'    @freq_subday_interval = ' + CAST(@IntervalMinutes AS nvarchar(5)) + N';' + @crlf + @crlf +

    N'EXEC msdb.dbo.sp_attach_schedule'                                            + @crlf +
    N'    @job_name      = N' + @q + @jobName + @q + N','                          + @crlf +
    N'    @schedule_name = N' + @q + @jobName + N' Every ' + CAST(@IntervalMinutes AS nvarchar(5)) + N'min' + @q + N';' + @crlf + @crlf +

    N'EXEC msdb.dbo.sp_add_jobserver @job_name = N' + @q + @jobName + @q + N';'   + @crlf +
    N'GO' + @crlf;

SELECT @ddl AS ddl;

Real Output

file_name  file_type  file_size_mb  total_allocated_mb  free_mb  user_objects_mb
tempdev    ROWS       8.00          8.00                5.13     1.38
temp2      ROWS       8.00          8.00                7.81     0.00
temp3      ROWS       8.00          8.00                7.88     0.00
temp4      ROWS       8.00          8.00                7.88     0.06
temp5      ROWS       8.00          8.00                7.69     0.06

Eight rows, one per TempDB file. The free_mb column is the one to watch: these files are close to full, which is exactly the condition this collector exists to catch before a query fails rather than after.

Generate-CollectorJob-VlfCount.sql

Merges current VLF count, recovery model, and log reuse wait per database into collector.VlfCountCurrent, a system-versioned table, every run keeps exactly one current row per database with full history retained automatically.

/*
Script Name : Generate-CollectorJob-VlfCount
Category    : collectors
Purpose     : Generates DDL to create the DBA - Collect VLF Count SQL Agent job.
              Creates the target database and a system-versioned (temporal) collector table
              if absent, then outputs T-SQL to install a daily VLF count MERGE job.
              Each run upserts current VLF counts per database — SQL Server automatically
              records every change in the paired history table, capturing exactly when
              VLF counts spiked (autogrowth) or dropped (log maintenance).
              Edit parameters, review output, then run on the target instance.
Author      : Peter Whyte (https://sqldba.blog)
Requires    : sysadmin (to run generated DDL); VIEW SERVER STATE, VIEW DATABASE STATE at job runtime
Notes       : Default schedule: daily at 02:00. Requires SQL Server 2016+ (sys.dm_db_log_info).
              Thresholds: <100 OK, 100-999 MONITOR, 1000+ WARNING, 10000+ CRITICAL.
              If upgrading from the non-temporal version, drop collector.VlfCount manually first.
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;

-- ── Parameters ────────────────────────────────────────────────────────────────
DECLARE @TargetDatabase  sysname  = N'DBAMonitor';
DECLARE @JobOwner        sysname  = N'sa';
DECLARE @CategoryName    nvarchar(128) = N'DBA Collectors';
DECLARE @RunHour         tinyint  = 2;
-- ─────────────────────────────────────────────────────────────────────────────

DECLARE @q        nchar(1)      = NCHAR(39);
DECLARE @crlf     nvarchar(2)   = CHAR(13) + CHAR(10);
DECLARE @ddl      nvarchar(max) = N'';
DECLARE @jobName  sysname       = N'DBA - Collect VLF Count';
DECLARE @schedTS  int           = @RunHour * 10000;
DECLARE @stepCmd  nvarchar(max);

-- ── Step command (| = single-quote placeholder) ────────────────────────────────
SET @stepCmd = REPLACE(
N'SET NOCOUNT ON;
MERGE [<<DB>>].[collector].[VlfCountCurrent] AS target
USING (
    SELECT
        @@SERVERNAME                                        AS server_name,
        d.name COLLATE DATABASE_DEFAULT                     AS database_name,
        d.recovery_model_desc COLLATE DATABASE_DEFAULT      AS recovery_model_desc,
        d.log_reuse_wait_desc COLLATE DATABASE_DEFAULT      AS log_reuse_wait_desc,
        COUNT(li.file_id)                                   AS vlf_count,
        CAST(mf.size * 8.0 / 1024 AS decimal(10,2))        AS log_file_size_mb,
        CASE
            WHEN COUNT(li.file_id) >= 10000 THEN |CRITICAL|
            WHEN COUNT(li.file_id) >= 1000  THEN |WARNING|
            WHEN COUNT(li.file_id) >= 100   THEN |MONITOR|
            ELSE |OK|
        END                                                 AS vlf_status
    FROM sys.databases d
    JOIN sys.master_files mf
        ON mf.database_id = d.database_id
       AND mf.type = 1
    CROSS APPLY sys.dm_db_log_info(d.database_id) li
    WHERE d.state_desc = |ONLINE|
      AND d.database_id > 4
    GROUP BY d.name, d.recovery_model_desc, d.log_reuse_wait_desc, mf.size
) AS source
ON  target.server_name   = source.server_name
AND target.database_name = source.database_name
WHEN MATCHED AND (
    target.vlf_count    <> source.vlf_count    OR
    target.vlf_status   <> source.vlf_status   OR
    ISNULL(target.log_reuse_wait_desc, |_|) <> ISNULL(source.log_reuse_wait_desc, |_|)
) THEN UPDATE SET
    recovery_model_desc  = source.recovery_model_desc,
    log_reuse_wait_desc  = source.log_reuse_wait_desc,
    vlf_count            = source.vlf_count,
    log_file_size_mb     = source.log_file_size_mb,
    vlf_status           = source.vlf_status
WHEN NOT MATCHED BY TARGET THEN
    INSERT (server_name, database_name, recovery_model_desc, log_reuse_wait_desc,
            vlf_count, log_file_size_mb, vlf_status)
    VALUES (source.server_name, source.database_name, source.recovery_model_desc,
            source.log_reuse_wait_desc, source.vlf_count, source.log_file_size_mb,
            source.vlf_status);'
, N'|', NCHAR(39));

SET @stepCmd = REPLACE(@stepCmd, N'<<DB>>', @TargetDatabase);

-- ═══════════════════════════════════════════════════════════════════════════════
-- DDL output
-- ═══════════════════════════════════════════════════════════════════════════════
SET @ddl =
    N'-- ================================================================' + @crlf +
    N'-- Generated by Generate-CollectorJob-VlfCount.sql'                  + @crlf +
    N'-- Server    : ' + @@SERVERNAME                                      + @crlf +
    N'-- Target DB : ' + @TargetDatabase                                   + @crlf +
    N'-- Generated : ' + CONVERT(nvarchar(20), GETDATE(), 120)             + @crlf +
    N'-- ================================================================' + @crlf + @crlf;

-- ── 1. Target database ────────────────────────────────────────────────────────
SET @ddl +=
    N'IF NOT EXISTS (SELECT 1 FROM sys.databases WHERE name = N' + @q + @TargetDatabase + @q + N')' + @crlf +
    N'    CREATE DATABASE [' + @TargetDatabase + N'];'                                               + @crlf +
    N'GO' + @crlf + @crlf;

-- ── 2. Collector schema ───────────────────────────────────────────────────────
SET @ddl +=
    N'IF NOT EXISTS (SELECT 1 FROM [' + @TargetDatabase + N'].sys.schemas WHERE name = N' + @q + N'collector' + @q + N')' + @crlf +
    N'    EXEC [' + @TargetDatabase + N'].sys.sp_executesql N' + @q + N'CREATE SCHEMA collector' + @q + N';'              + @crlf +
    N'GO' + @crlf + @crlf;

-- ── 3. VlfCountCurrent temporal table ─────────────────────────────────────────
SET @ddl +=
    N'IF NOT EXISTS (' + @crlf +
    N'    SELECT 1 FROM [' + @TargetDatabase + N'].sys.objects o'                                                              + @crlf +
    N'    JOIN [' + @TargetDatabase + N'].sys.schemas s ON s.schema_id = o.schema_id'                                          + @crlf +
    N'    WHERE o.name = N' + @q + N'VlfCountCurrent' + @q + N' AND s.name = N' + @q + N'collector' + @q + N')'              + @crlf +
    N'BEGIN' + @crlf +
    N'CREATE TABLE [' + @TargetDatabase + N'].[collector].[VlfCountCurrent] ('                 + @crlf +
    N'    server_name          nvarchar(128)  NOT NULL,'                                        + @crlf +
    N'    database_name        nvarchar(128)  NOT NULL,'                                        + @crlf +
    N'    recovery_model_desc  nvarchar(60)   NULL,'                                            + @crlf +
    N'    log_reuse_wait_desc  nvarchar(60)   NULL,'                                            + @crlf +
    N'    vlf_count            int            NULL,'                                            + @crlf +
    N'    log_file_size_mb     decimal(10,2)  NULL,'                                            + @crlf +
    N'    vlf_status           nvarchar(20)   NULL,'                                            + @crlf +
    N'    SysStartTime         datetime2(2)   GENERATED ALWAYS AS ROW START NOT NULL,'         + @crlf +
    N'    SysEndTime           datetime2(2)   GENERATED ALWAYS AS ROW END   NOT NULL,'         + @crlf +
    N'    PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime),'                                  + @crlf +
    N'    CONSTRAINT [PK_VlfCountCurrent]'                                                     + @crlf +
    N'        PRIMARY KEY (server_name, database_name)'                                        + @crlf +
    N') WITH (SYSTEM_VERSIONING = ON ('                                                        + @crlf +
    N'    HISTORY_TABLE        = [collector].[VlfCountHistory],'                               + @crlf +
    N'    DATA_CONSISTENCY_CHECK = ON));'                                                       + @crlf +
    N'END' + @crlf +
    N'GO' + @crlf + @crlf;

-- ── 4. Agent category ─────────────────────────────────────────────────────────
SET @ddl +=
    N'USE msdb;' + @crlf +
    N'GO' + @crlf + @crlf +
    N'IF NOT EXISTS (SELECT 1 FROM msdb.dbo.syscategories WHERE name = N' + @q + @CategoryName + @q + N' AND category_class = 1)' + @crlf +
    N'    EXEC msdb.dbo.sp_add_category'                                                                                             + @crlf +
    N'        @class = N' + @q + N'JOB' + @q + N', @type = N' + @q + N'LOCAL' + @q + N', @name = N' + @q + @CategoryName + @q + N';' + @crlf +
    N'GO' + @crlf + @crlf;

-- ── 5. Job + step + schedule ──────────────────────────────────────────────────
SET @ddl +=
    N'-- Job: ' + @jobName + @crlf +
    N'IF EXISTS (SELECT 1 FROM msdb.dbo.sysjobs WHERE name = N' + @q + @jobName + @q + N')' + @crlf +
    N'    EXEC msdb.dbo.sp_delete_job @job_name = N' + @q + @jobName + @q + N', @delete_unused_schedule = 1;' + @crlf + @crlf +

    N'EXEC msdb.dbo.sp_add_job'                                                    + @crlf +
    N'    @job_name         = N' + @q + @jobName + @q + N','                       + @crlf +
    N'    @enabled          = 1,'                                                   + @crlf +
    N'    @owner_login_name = N' + @q + @JobOwner + @q + N','                      + @crlf +
    N'    @category_name    = N' + @q + @CategoryName + @q + N';'                  + @crlf + @crlf +

    N'EXEC msdb.dbo.sp_add_jobstep'                                                + @crlf +
    N'    @job_name          = N' + @q + @jobName + @q + N','                      + @crlf +
    N'    @step_id           = 1,'                                                  + @crlf +
    N'    @step_name         = N' + @q + N'Merge VLF counts' + @q + N','           + @crlf +
    N'    @subsystem         = N' + @q + N'TSQL' + @q + N','                       + @crlf +
    N'    @database_name     = N' + @q + N'master' + @q + N','                     + @crlf +
    N'    @command           = N' + @q + REPLACE(@stepCmd, @q, @q + @q) + @q + N',' + @crlf +
    N'    @retry_attempts    = 0,'                                                  + @crlf +
    N'    @on_success_action = 1,'                                                  + @crlf +
    N'    @on_fail_action    = 2;'                                                  + @crlf + @crlf +

    N'EXEC msdb.dbo.sp_add_schedule'                                               + @crlf +
    N'    @schedule_name          = N' + @q + @jobName + N' Daily ' + CAST(@RunHour AS nvarchar(2)) + N':00' + @q + N',' + @crlf +
    N'    @freq_type              = 4,'                                             + @crlf +
    N'    @freq_interval          = 1,'                                             + @crlf +
    N'    @freq_subday_type       = 1,'                                             + @crlf +
    N'    @active_start_time      = ' + CAST(@schedTS AS nvarchar(10)) + N';'      + @crlf + @crlf +

    N'EXEC msdb.dbo.sp_attach_schedule'                                            + @crlf +
    N'    @job_name      = N' + @q + @jobName + @q + N','                          + @crlf +
    N'    @schedule_name = N' + @q + @jobName + N' Daily ' + CAST(@RunHour AS nvarchar(2)) + N':00' + @q + N';' + @crlf + @crlf +

    N'EXEC msdb.dbo.sp_add_jobserver @job_name = N' + @q + @jobName + @q + N';'   + @crlf +
    N'GO' + @crlf;

SELECT @ddl AS ddl;

Real Output

database_name    recovery_model_desc  vlf_count  vlf_status  log_file_size_mb
migdb_37A45A06   FULL                 19         OK          160.00
migdb_7049216A   FULL                 19         OK          160.00
DemoDatabase     FULL                 16         OK          512.00
DBAMonitor       FULL                 4          OK          8.00

7 databases captured, real VLF counts against this instance’s actual databases.

Generate-CollectorJob-DatabaseGrowth.sql

Merges current file size, growth settings, and distance-to-limit per database file into collector.DatabaseGrowthCurrent, same system-versioned pattern as VLF Count.

/*
Script Name : Generate-CollectorJob-DatabaseGrowth
Category    : collectors
Purpose     : Generates DDL to create the DBA - Collect Database Growth SQL Agent job.
              Creates the target database and a system-versioned (temporal) collector table
              if absent, then outputs T-SQL to install a recurring database file size MERGE job.
              Each run upserts current file sizes into DatabaseGrowthCurrent — SQL Server
              automatically records every change in the paired history table.
              Query DatabaseGrowthCurrent FOR SYSTEM_TIME BETWEEN to retrieve historical
              file sizes for trend analysis and growth forecasting.
              Edit parameters, review output, then run on the target instance.
Author      : Peter Whyte (https://sqldba.blog)
Requires    : sysadmin (to run generated DDL); VIEW ANY DATABASE, VIEW DATABASE STATE at job runtime
Notes       : Default interval: every 60 minutes. growth_status flags AT_LIMIT / NEAR_LIMIT / UNLIMITED.
              Requires SQL Server 2016 or later (temporal table support).
              If upgrading from the non-temporal version, drop collector.DatabaseGrowth manually first.
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;

-- ── Parameters ────────────────────────────────────────────────────────────────
DECLARE @TargetDatabase  sysname       = N'DBAMonitor';
DECLARE @JobOwner        sysname       = N'sa';
DECLARE @CategoryName    nvarchar(128) = N'DBA Collectors';
DECLARE @IntervalMinutes int           = 60;
-- ─────────────────────────────────────────────────────────────────────────────

DECLARE @q       nchar(1)      = NCHAR(39);
DECLARE @crlf    nvarchar(2)   = CHAR(13) + CHAR(10);
DECLARE @ddl     nvarchar(max) = N'';
DECLARE @jobName sysname       = N'DBA - Collect Database Growth';
DECLARE @stepCmd nvarchar(max);

-- ── Step command (| = single-quote placeholder) ────────────────────────────────
SET @stepCmd = REPLACE(
N'SET NOCOUNT ON;
MERGE [<<DB>>].[collector].[DatabaseGrowthCurrent] AS target
USING (
    SELECT
        @@SERVERNAME                                                     AS server_name,
        d.name COLLATE DATABASE_DEFAULT                                  AS database_name,
        d.state_desc COLLATE DATABASE_DEFAULT                            AS database_state,
        d.recovery_model_desc COLLATE DATABASE_DEFAULT                   AS recovery_model_desc,
        mf.name COLLATE DATABASE_DEFAULT                                 AS logical_name,
        mf.physical_name,
        mf.type_desc                                                     AS file_type,
        CAST(mf.size * 8.0 / 1024 AS decimal(10,2))                     AS file_size_mb,
        CASE WHEN mf.max_size IN (-1, 268435456)
             THEN NULL
             ELSE CAST((mf.max_size - mf.size) * 8.0 / 1024 AS decimal(10,2))
             END                                                         AS space_to_limit_mb,
        CASE WHEN mf.is_percent_growth = 1
             THEN CAST(mf.growth AS varchar(10)) + |%|
             ELSE CAST(mf.growth * 8 / 1024 AS varchar(10)) + | MB|
             END                                                         AS autogrowth,
        mf.is_percent_growth,
        CASE WHEN mf.max_size IN (-1, 268435456)
             THEN NULL
             ELSE CAST(mf.max_size * 8.0 / 1024 AS decimal(10,2))
             END                                                         AS growth_limit_mb,
        CASE
            WHEN mf.max_size IN (-1, 268435456)                         THEN |UNLIMITED|
            WHEN mf.size >= mf.max_size                                 THEN |AT_LIMIT|
            WHEN (mf.max_size - mf.size) * 8.0 / 1024 < 1024
                 AND mf.max_size NOT IN (-1, 268435456)                 THEN |NEAR_LIMIT|
            ELSE |OK|
        END                                                              AS growth_status
    FROM sys.master_files mf
    JOIN sys.databases    d  ON d.database_id = mf.database_id
    WHERE d.state_desc = |ONLINE|
) AS source
ON  target.server_name   = source.server_name
AND target.database_name = source.database_name
AND target.logical_name  = source.logical_name
WHEN MATCHED AND (
    target.file_size_mb   <> source.file_size_mb   OR
    target.growth_status  <> source.growth_status  OR
    target.database_state <> source.database_state OR
    ISNULL(target.growth_limit_mb,    -1) <> ISNULL(source.growth_limit_mb,    -1) OR
    ISNULL(target.space_to_limit_mb,  -1) <> ISNULL(source.space_to_limit_mb,  -1)
) THEN UPDATE SET
    database_state      = source.database_state,
    recovery_model_desc = source.recovery_model_desc,
    physical_name       = source.physical_name,
    file_type           = source.file_type,
    file_size_mb        = source.file_size_mb,
    space_to_limit_mb   = source.space_to_limit_mb,
    autogrowth          = source.autogrowth,
    is_percent_growth   = source.is_percent_growth,
    growth_limit_mb     = source.growth_limit_mb,
    growth_status       = source.growth_status
WHEN NOT MATCHED BY TARGET THEN
    INSERT (server_name, database_name, database_state, recovery_model_desc,
            logical_name, physical_name, file_type, file_size_mb,
            space_to_limit_mb, autogrowth, is_percent_growth,
            growth_limit_mb, growth_status)
    VALUES (source.server_name, source.database_name, source.database_state,
            source.recovery_model_desc, source.logical_name, source.physical_name,
            source.file_type, source.file_size_mb, source.space_to_limit_mb,
            source.autogrowth, source.is_percent_growth, source.growth_limit_mb,
            source.growth_status);'
, N'|', NCHAR(39));

SET @stepCmd = REPLACE(@stepCmd, N'<<DB>>', @TargetDatabase);

-- ═══════════════════════════════════════════════════════════════════════════════
-- DDL output
-- ═══════════════════════════════════════════════════════════════════════════════
SET @ddl =
    N'-- ================================================================' + @crlf +
    N'-- Generated by Generate-CollectorJob-DatabaseGrowth.sql'            + @crlf +
    N'-- Server    : ' + @@SERVERNAME                                      + @crlf +
    N'-- Target DB : ' + @TargetDatabase                                   + @crlf +
    N'-- Generated : ' + CONVERT(nvarchar(20), GETDATE(), 120)             + @crlf +
    N'-- ================================================================' + @crlf + @crlf;

-- ── 1. Target database ────────────────────────────────────────────────────────
SET @ddl +=
    N'IF NOT EXISTS (SELECT 1 FROM sys.databases WHERE name = N' + @q + @TargetDatabase + @q + N')' + @crlf +
    N'    CREATE DATABASE [' + @TargetDatabase + N'];'                                               + @crlf +
    N'GO' + @crlf + @crlf;

-- ── 2. Collector schema ───────────────────────────────────────────────────────
SET @ddl +=
    N'IF NOT EXISTS (SELECT 1 FROM [' + @TargetDatabase + N'].sys.schemas WHERE name = N' + @q + N'collector' + @q + N')' + @crlf +
    N'    EXEC [' + @TargetDatabase + N'].sys.sp_executesql N' + @q + N'CREATE SCHEMA collector' + @q + N';'              + @crlf +
    N'GO' + @crlf + @crlf;

-- ── 3. DatabaseGrowthCurrent temporal table ───────────────────────────────────
SET @ddl +=
    N'IF NOT EXISTS (' + @crlf +
    N'    SELECT 1 FROM [' + @TargetDatabase + N'].sys.objects o'                                                                    + @crlf +
    N'    JOIN [' + @TargetDatabase + N'].sys.schemas s ON s.schema_id = o.schema_id'                                                + @crlf +
    N'    WHERE o.name = N' + @q + N'DatabaseGrowthCurrent' + @q + N' AND s.name = N' + @q + N'collector' + @q + N')'              + @crlf +
    N'BEGIN' + @crlf +
    N'CREATE TABLE [' + @TargetDatabase + N'].[collector].[DatabaseGrowthCurrent] ('                  + @crlf +
    N'    server_name          nvarchar(128)  NOT NULL,'                                               + @crlf +
    N'    database_name        nvarchar(128)  NOT NULL,'                                               + @crlf +
    N'    logical_name         nvarchar(128)  NOT NULL,'                                               + @crlf +
    N'    database_state       nvarchar(60)   NULL,'                                                   + @crlf +
    N'    recovery_model_desc  nvarchar(60)   NULL,'                                                   + @crlf +
    N'    physical_name        nvarchar(260)  NULL,'                                                   + @crlf +
    N'    file_type            nvarchar(60)   NULL,'                                                   + @crlf +
    N'    file_size_mb         decimal(10,2)  NULL,'                                                   + @crlf +
    N'    space_to_limit_mb    decimal(10,2)  NULL,'                                                   + @crlf +
    N'    autogrowth           nvarchar(20)   NULL,'                                                   + @crlf +
    N'    is_percent_growth    bit            NULL,'                                                   + @crlf +
    N'    growth_limit_mb      decimal(10,2)  NULL,'                                                   + @crlf +
    N'    growth_status        nvarchar(20)   NULL,'                                                   + @crlf +
    N'    SysStartTime         datetime2(2)   GENERATED ALWAYS AS ROW START NOT NULL,'                + @crlf +
    N'    SysEndTime           datetime2(2)   GENERATED ALWAYS AS ROW END   NOT NULL,'                + @crlf +
    N'    PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime),'                                         + @crlf +
    N'    CONSTRAINT [PK_DatabaseGrowthCurrent]'                                                      + @crlf +
    N'        PRIMARY KEY (server_name, database_name, logical_name)'                                 + @crlf +
    N') WITH (SYSTEM_VERSIONING = ON ('                                                               + @crlf +
    N'    HISTORY_TABLE        = [collector].[DatabaseGrowthHistory],'                                + @crlf +
    N'    DATA_CONSISTENCY_CHECK = ON));'                                                              + @crlf +
    N'END' + @crlf +
    N'GO' + @crlf + @crlf;

-- ── 4. Agent category ─────────────────────────────────────────────────────────
SET @ddl +=
    N'USE msdb;' + @crlf +
    N'GO' + @crlf + @crlf +
    N'IF NOT EXISTS (SELECT 1 FROM msdb.dbo.syscategories WHERE name = N' + @q + @CategoryName + @q + N' AND category_class = 1)' + @crlf +
    N'    EXEC msdb.dbo.sp_add_category'                                                                                             + @crlf +
    N'        @class = N' + @q + N'JOB' + @q + N', @type = N' + @q + N'LOCAL' + @q + N', @name = N' + @q + @CategoryName + @q + N';' + @crlf +
    N'GO' + @crlf + @crlf;

-- ── 5. Job + step + schedule ──────────────────────────────────────────────────
SET @ddl +=
    N'-- Job: ' + @jobName + @crlf +
    N'IF EXISTS (SELECT 1 FROM msdb.dbo.sysjobs WHERE name = N' + @q + @jobName + @q + N')' + @crlf +
    N'    EXEC msdb.dbo.sp_delete_job @job_name = N' + @q + @jobName + @q + N', @delete_unused_schedule = 1;' + @crlf + @crlf +

    N'EXEC msdb.dbo.sp_add_job'                                                    + @crlf +
    N'    @job_name         = N' + @q + @jobName + @q + N','                       + @crlf +
    N'    @enabled          = 1,'                                                   + @crlf +
    N'    @owner_login_name = N' + @q + @JobOwner + @q + N','                      + @crlf +
    N'    @category_name    = N' + @q + @CategoryName + @q + N';'                  + @crlf + @crlf +

    N'EXEC msdb.dbo.sp_add_jobstep'                                                + @crlf +
    N'    @job_name          = N' + @q + @jobName + @q + N','                      + @crlf +
    N'    @step_id           = 1,'                                                  + @crlf +
    N'    @step_name         = N' + @q + N'Merge database file sizes' + @q + N',' + @crlf +
    N'    @subsystem         = N' + @q + N'TSQL' + @q + N','                       + @crlf +
    N'    @database_name     = N' + @q + N'master' + @q + N','                     + @crlf +
    N'    @command           = N' + @q + REPLACE(@stepCmd, @q, @q + @q) + @q + N',' + @crlf +
    N'    @retry_attempts    = 0,'                                                  + @crlf +
    N'    @on_success_action = 1,'                                                  + @crlf +
    N'    @on_fail_action    = 2;'                                                  + @crlf + @crlf +

    N'EXEC msdb.dbo.sp_add_schedule'                                               + @crlf +
    N'    @schedule_name        = N' + @q + @jobName + N' Every ' + CAST(@IntervalMinutes AS nvarchar(5)) + N'min' + @q + N',' + @crlf +
    N'    @freq_type            = 4,'                                               + @crlf +
    N'    @freq_interval        = 1,'                                               + @crlf +
    N'    @freq_subday_type     = 4,'                                               + @crlf +
    N'    @freq_subday_interval = ' + CAST(@IntervalMinutes AS nvarchar(5)) + N';' + @crlf + @crlf +

    N'EXEC msdb.dbo.sp_attach_schedule'                                            + @crlf +
    N'    @job_name      = N' + @q + @jobName + @q + N','                          + @crlf +
    N'    @schedule_name = N' + @q + @jobName + N' Every ' + CAST(@IntervalMinutes AS nvarchar(5)) + N'min' + @q + N';' + @crlf + @crlf +

    N'EXEC msdb.dbo.sp_add_jobserver @job_name = N' + @q + @jobName + @q + N';'   + @crlf +
    N'GO' + @crlf;

SELECT @ddl AS ddl;

Real Output

database_name    logical_name          file_type  file_size_mb  growth_status
DemoDatabase     DemoDatabase_Data     ROWS       2048.00       UNLIMITED
migdb_37A45A06   migdb_37A45A06_data   ROWS       790.00        UNLIMITED
migdb_7049216A   migdb_7049216A_data   ROWS       790.00        UNLIMITED

29 rows captured across every database file on the instance.

How To Run From The Repo

git clone https://github.com/peterwhyte-lgtm/dba-tools
cd dba-tools
.\Initialize-Environment.ps1

.\run.ps1 Generate-CollectorJob-Tempdb
.\run.ps1 Generate-CollectorJob-VlfCount
.\run.ps1 Generate-CollectorJob-DatabaseGrowth
# Review the generated DDL, then run it on the target instance

These scripts live in the repo at:

Understanding the Results

A collation-conflict error comparing a sys.databases or sys.master_files column against an ordinary table is a known SQL Server trap, not a one-off bug. Any custom query joining catalog view name columns against your own tables can hit this, add COLLATE DATABASE_DEFAULT defensively when it does.
UNION‘s ORDER BY only applies to the final combined result. A TOP N ... ORDER BY meant to apply to one branch of a union needs its own derived table, referencing that branch’s table alias after the union simply doesn’t resolve.
VLF Count and Database Growth being system-versioned means there’s no “latest snapshot” concept to filter on, the Current table already is the latest, query it directly rather than looking for a collection_time column that doesn’t exist.

Best Practices

Add COLLATE DATABASE_DEFAULT to catalog view name/description columns any time you’re comparing them against a plain table column, cheap insurance against a trap that isn’t specific to this one instance.
When a UNION ALL branch needs its own TOP and ORDER BY, wrap that branch in a derived table explicitly rather than trusting operator precedence.
Treat the VLF Count and Database Growth “Current” tables as always-current, no staleness check needed, and use their paired “History” tables when you specifically need a point-in-time trend instead.
Run the TempDB collector’s session-consumer branch during genuine load if you’re diagnosing a specific TempDB contention incident, an idle instance’s top 10 sessions tell you very little.

Related Scripts

You may also find these scripts useful:

Collectors and Baseline Infrastructure (hub)
Generate Collector Alerts
Storage and Capacity (pillar)
Get Temp DB Hotspots
Collation, Case Sensitivity and the Cross-Database Trap, why sys.databases.name needs COLLATE DATABASE_DEFAULT when you compare it to your own tables
DBA Scripts: The Complete Guide, the map across every script on this site

Frequently Asked Questions

Why does comparing a sys.databases column against my own table throw a collation error?

sys.databases.name (and several other catalog view columns) use a fixed system collation regardless of your server’s actual default collation. Any comparison against an ordinary table column, which uses the database’s real default collation, can conflict. Add COLLATE DATABASE_DEFAULT to the catalog-sourced column to resolve it.

The VLF Count and Database Growth tables don’t have a timestamp I can filter on, how do I see history?

Query the paired VlfCountHistory / DatabaseGrowthHistory tables instead, system versioning writes every prior state there automatically. The Current tables intentionally hold only the latest row per key.

Summary

Three capacity collectors: database growth, VLF count, and TempDB space with its top session consumers. Growth and VLF count are system-versioned, so SQL Server keeps the history for you and there is no cleanup job to schedule. Install all three and you stop answering “when did this database start growing” from memory.

Start Here

The Pillars

Blog Categories

Latest Posts

Blog Tags

Comments

Leave a Reply Cancel reply