Quick Scan Report – xp_sqlmaint

What this check looks for

Agent job steps whose command text calls xp_sqlmaint. The check is skipped on Amazon RDS.

Three checks share this page, because they are the same finding narrowed by what the call does:

Why it matters

xp_sqlmaint is the SQL Server 2000 maintenance plan mechanism. It is an extended stored procedure that shells out to sqlmaint.exe, and both have been deprecated since SQL Server 2005.

That it still works is a courtesy, not a commitment. Extended stored procedures as a category are deprecated, and Microsoft has been explicit for twenty years that this one will be removed. A job depending on it is a job that will fail on some future upgrade, and the failure will be opaque: a missing procedure in a job step that has worked since before most of the current team arrived.

Beyond the deprecation, what it actually does is poor:

• It has no fragmentation threshold. Like the maintenance plan tasks it belongs to, it reindexes everything regardless of need, which has its own check.
• Its error handling is minimal. sqlmaint.exe returns a code and the job step interprets it, which means failures are reported coarsely if at all.
• Its logging is a text file rather than a table, so there is no history you can query.
• It cannot be given a time limit, so it runs until it finishes or the window is gone.

The two specific variants deserve separate mention:

Using it to update statistics (issue 67) rebuilds statistics for everything on a fixed sample, with no notion of which statistics are stale. Modern alternatives look at modification_counter and update only what has changed.

Using it to run DBCC CHECKDB (issue 68) is the one that matters most. Integrity checking is the mechanism that finds corruption, and running it through a deprecated shell-out with weak error reporting means a failed check may not be noticed. A CHECKDB that fails to complete is not the same as one that found nothing, and this mechanism makes that distinction hard to see.

How to confirm it yourself

SELECT j.[name]        AS [job_name],
       j.[enabled],
       s.[step_id],
       s.[step_name],
       s.[subsystem],
       s.[command]
  FROM msdb.dbo.sysjobs AS j WITH (NOLOCK)
 INNER JOIN msdb.dbo.sysjobsteps AS s WITH (NOLOCK)
         ON s.[job_id] = j.[job_id]
 WHERE s.[command] LIKE '%xp_sqlmaint%'
    OR s.[command] LIKE '%sqlmaint%'
 ORDER BY j.[name], s.[step_id];

Read the command text. The sqlmaint.exe switches say what it is doing:

-RmUnusedSpace is worth spotting, because it is a shrink on a schedule, which has its own check and is the most harmful thing in the list.

Whether it is even succeeding:

SELECT j.[name],
       MAX(msdb.dbo.agent_datetime(h.[run_date], h.[run_time])) AS [last_run],
       MAX(CASE WHEN h.[run_status] = 1
                THEN msdb.dbo.agent_datetime(h.[run_date], h.[run_time]) END) AS [last_success]
  FROM msdb.dbo.sysjobhistory AS h WITH (NOLOCK)
 INNER JOIN msdb.dbo.sysjobs AS j WITH (NOLOCK) ON j.[job_id] = h.[job_id]
 INNER JOIN msdb.dbo.sysjobsteps AS st WITH (NOLOCK) ON st.[job_id] = j.[job_id]
 WHERE st.[command] LIKE '%sqlmaint%' AND h.[step_id] = 0
 GROUP BY j.[name];

And, for the CHECKDB variant, whether the databases are genuinely being verified:

DBCC DBINFO ('YourDatabase') WITH TABLERESULTS;   -- read dbi_dbccLastKnownGood

That is the authoritative answer. A job that appears to run CHECKDB nightly, against a database whose last known good date is months old, is the finding stated plainly.

How to fix it

Replace the job steps with direct T-SQL or a modern maintenance script. This is straightforward because each sqlmaint switch has an obvious equivalent.

For integrity checking:

DBCC CHECKDB ('YourDatabase') WITH NO_INFOMSGS, ALL_ERRORMSGS;

or, better, Ola Hallengren’s DatabaseIntegrityCheck, which loops database by database, continues past a failure and takes a time limit.

For statistics:

UPDATE STATISTICS [dbo].[YourTable] WITH FULLSCAN;

or IndexOptimize with @UpdateStatistics = 'ALL' and @OnlyModifiedStatistics = 'Y', which is the part sqlmaint cannot do: skipping statistics that have not changed.

For index maintenance: IndexOptimize, which chooses per index rather than rebuilding everything.

For backups: BACKUP DATABASE ... WITH CHECKSUM, COMPRESSION, or DatabaseBackup.

For -RmUnusedSpace: just remove it. Do not replace it. A scheduled shrink fragments every index and gives back space the database is about to take again.

Verify afterwards. Once the replacement runs, confirm dbi_dbccLastKnownGood is moving and the backup history is filling. The point of the change is that failures become visible, so the first week is when you find out what had been failing quietly.

How long it takes

About an hour per job to translate and test. Installing a maintenance solution and moving everything across is a half day and is the better use of the time.

Related reports

Related checks

Frequently asked questions

It has worked for twenty years. Why change it? Because it is deprecated and will be removed, and because its error reporting is weak enough that “it has worked” is harder to verify than it sounds. Check dbi_dbccLastKnownGood before concluding it has.

Which switch is ours using? The command text in the query above shows it. -CkDB, -UpdOptiStats and -RebldIdx are the common three, and -RmUnusedSpace is the one to remove rather than replace.

Is sqlmaint.exe still installed? On most versions, yes, which is why the jobs still run. That is not a commitment to keep it.

Can I replace it gradually? Yes, and integrity checking is the one to move first, because it is the one whose silent failure costs most.