Command Log Cleanup

What this check looks for

A CommandLog table that has grown large without being trimmed. The table is created by the widely used maintenance solution that logs every command it runs. The check is skipped on Amazon RDS.

Why it matters

CommandLog records every maintenance command executed: every index rebuild, every reorganize, every statistics update, every backup, every integrity check, with its start time, end time and outcome.

That is genuinely valuable data. It is how you answer:

• Which index rebuild is taking two hours of the window?
• Did the statistics update on this table run last night, and how long did it take?
• When did the maintenance window start overrunning?
• Which command failed, and with what error?

And it grows quickly, because the logging is per object rather than per job. A nightly index maintenance run across 30 databases with a few thousand indexes writes a row per index it touches. That is tens of thousands of rows a night on a large estate.

What it costs once it is large:

• Space in whatever database it lives in, usually master or a dedicated DBA database.
• The maintenance jobs slow down, since each one inserts into a growing table.
• Querying it becomes slow, which defeats the purpose of having it.
• If it is in master, which is the default installation location, it makes master larger than it should be, and master is meant to be small.

The fix is almost always trivial, which is why this is low severity: the maintenance solution ships with a CommandLog Cleanup job, and in most cases that job already exists on the instance and simply has no schedule. It was created at installation and nobody enabled it.

So the first thing to check is whether the job is there, and the fix is usually one sp_add_jobschedule call.

Two things worth getting right while you are there. First, do not purge too aggressively: the value of this table is in comparing last night against last month, and a seven day retention throws that away. Second, if the table is very large, purge in batches rather than in one delete, because a single delete of millions of rows generates a great deal of transaction log in whatever database it lives in.

How to confirm it yourself

Find the table, since it can be in master or in a dedicated database:

EXEC sp_MSforeachdb N'
USE [?];
IF OBJECT_ID(''dbo.CommandLog'') IS NOT NULL
SELECT DB_NAME()          AS [database_name],
       COUNT(*)           AS [rows],
       MIN([StartTime])   AS [oldest],
       MAX([StartTime])   AS [newest]
  FROM dbo.CommandLog WITH (NOLOCK);';

Its size:

USE [master];   -- or wherever it lives
GO
SELECT OBJECT_NAME(p.[object_id]) AS [table_name],
       SUM(p.[rows])              AS [rows],
       CAST(SUM(a.[total_pages]) * 8.0 / 1024 AS DECIMAL(12,1)) AS [size_mb]
  FROM sys.partitions            AS p WITH (NOLOCK)
 INNER JOIN sys.allocation_units AS a WITH (NOLOCK) ON a.[container_id] = p.[partition_id]
 WHERE OBJECT_NAME(p.[object_id]) = 'CommandLog'
   AND p.[index_id] IN (0, 1)
 GROUP BY p.[object_id];

Whether the cleanup job exists and is scheduled, which is the question this check really asks:

SELECT j.[name]        AS [job_name],
       j.[enabled]     AS [job_enabled],
       s.[name]        AS [schedule_name],
       s.[enabled]     AS [schedule_enabled],
       s.[active_start_time]
  FROM msdb.dbo.sysjobs           AS j
  LEFT JOIN msdb.dbo.sysjobschedules AS js ON js.[job_id] = j.[job_id]
  LEFT JOIN msdb.dbo.sysschedules    AS s  ON s.[schedule_id] = js.[schedule_id]
 WHERE j.[name] LIKE '%CommandLog%'
    OR j.[name] LIKE '%Cleanup%'
 ORDER BY j.[name];

A job present with a NULL schedule name is the finding, and the fix is one statement.

Before purging, get the value out of it, which is the part worth doing:

-- what takes the longest in the maintenance window
SELECT TOP (25)
       [DatabaseName], [SchemaName], [ObjectName], [IndexName],
       [CommandType],
       AVG(DATEDIFF(SECOND, [StartTime], [EndTime])) AS [avg_seconds],
       COUNT(*) AS [runs]
  FROM dbo.CommandLog WITH (NOLOCK)
 WHERE [EndTime] IS NOT NULL
 GROUP BY [DatabaseName], [SchemaName], [ObjectName], [IndexName], [CommandType]
 ORDER BY [avg_seconds] DESC;

-- anything that failed
SELECT TOP (50)
       [StartTime], [DatabaseName], [ObjectName], [CommandType],
       [ErrorNumber], [ErrorMessage]
  FROM dbo.CommandLog WITH (NOLOCK)
 WHERE [ErrorNumber] <> 0
 ORDER BY [StartTime] DESC;

Failed maintenance commands nobody has looked at are a better finding than the table size, and this is the only place they are recorded.

How to fix it

Schedule the cleanup job. If the job does not exist, create the purge as a job step.

If the job exists with no schedule, which is the usual case:

EXEC msdb.dbo.sp_add_jobschedule
     @job_name          = N'CommandLog Cleanup',
     @name              = N'Weekly Sunday',
     @freq_type         = 8,          -- weekly
     @freq_interval     = 1,          -- Sunday
     @freq_recurrence_factor = 1,
     @active_start_time = 040000;

EXEC msdb.dbo.sp_update_job @job_name = N'CommandLog Cleanup', @enabled = 1;

Check what retention the job step uses, because the shipped default may not be what you want:

SELECT j.[name], st.[step_name], st.[command]
  FROM msdb.dbo.sysjobsteps AS st
 INNER JOIN msdb.dbo.sysjobs AS j ON j.[job_id] = st.[job_id]
 WHERE j.[name] = N'CommandLog Cleanup';

Keep 90 days rather than 30. The value of this table is comparing a maintenance window against the same window a month or two ago, and a short retention removes exactly that.

If the job does not exist, create one:

EXEC msdb.dbo.sp_add_job
     @job_name = N'CommandLog Cleanup',
     @description = N'Trims dbo.CommandLog to 90 days of maintenance history.';

EXEC msdb.dbo.sp_add_jobstep
     @job_name      = N'CommandLog Cleanup',
     @step_name     = N'Delete old rows',
     @subsystem     = N'TSQL',
     @database_name = N'master',
     @command       = N'DELETE FROM dbo.CommandLog
                         WHERE StartTime < DATEADD(DAY, -90, GETDATE());';

EXEC msdb.dbo.sp_add_jobschedule
     @job_name = N'CommandLog Cleanup',
     @name = N'Weekly Sunday', @freq_type = 8, @freq_interval = 1,
     @freq_recurrence_factor = 1, @active_start_time = 040000;

EXEC msdb.dbo.sp_add_jobserver @job_name = N'CommandLog Cleanup';

If the table is already very large, purge in batches first, so the initial cleanup does not generate an enormous transaction:

DECLARE @rows INT = 1;
WHILE @rows > 0
BEGIN
    DELETE TOP (10000) FROM dbo.CommandLog
     WHERE [StartTime] < DATEADD(DAY, -90, GETDATE());
    SET @rows = @@ROWCOUNT;
END;

Then index it for the queries you actually run, which makes the history usable rather than merely present:

CREATE NONCLUSTERED INDEX [IX_CommandLog_StartTime]
    ON [dbo].[CommandLog] ([StartTime])
    INCLUDE ([DatabaseName], [CommandType], [ErrorNumber]);

That index also makes the purge itself fast, which is the second reason to add it.

Consider moving it out of master. A dedicated DBA database is a better home for maintenance logging, keeps master small, and makes it easy to back up and retain on its own terms.

And make the maintenance jobs notify on failure, since CommandLog recording an error is only useful if somebody reads it.

How long it takes

About half an hour to schedule the job and do the initial batched purge.

Related reports

Related checks

Frequently asked questions

Where does the CommandLog table live? Wherever the maintenance solution was installed, usually master or a dedicated DBA database. The search across all databases above will find it.

How much history should I keep? About 90 days. The value is in comparing a maintenance window against the same window a month or two ago, so a short retention removes the main reason to have it.

The cleanup job exists but never runs. It almost certainly has no schedule. That is the single most common form of this finding, and adding a schedule is the whole fix.

Is it safe to delete the rows? Yes. It is a log of maintenance that has already happened. Read the failure and duration queries first, then purge in batches if the table is large.