A real head-scratcher

I’ve been using my RYO Maintenance scripts for a little while now, and they’ve been working great. Almost. One day I noticed that the backup script hadn’t backed up all of the databases on my instance. I checked the job history, no errors. I checked the SQL Server log, no errors. I checked the Windows event logs, nothing. Odd, and a little disconcerting (to put it mildly). So, I asked the Great Gazoogle.

Apparently, I’m not the only one with this problem. Others have run into the same odd behavior, and while the fix was simple, I’ve yet to find an explanation as to the cause. There have been some hypotheses thrown out, but nothing definitive.

The common theory is that something about the backup process is changing records in the sys.databases catalog. When the cursor tries to move to the next row, it can’t. So far, due to the random nature of the problem, I’ve not been able to reproduce and debug it. If anyone out there has found the cause, I’d love to hear from you.

In the meantime, the fix: use a STATIC cursor. Like so:

DECLARE getdbs CURSOR STATIC FOR
    SELECT d.database_id, d.name FROM sys.databases d
    WHERE d.name <> 'tempdb'
    AND d.state = 0
    AND d.name LIKE @dbs
    AND d.database_id > @diff_dbid       --no system dbs for diff backups
    AND d.recovery_model < @trn_rm       --no simple mode dbs for log backups
 

This will create a temporary, static, copy of the rows your cursor will be stepping through. That way, if the data in sys.databases is changing, it won’t effect your cursor. Another workaround would be to explicitly create a temporary table for your sys.databases records and cursor through that. Potayto. Potahto.

I’ve updated the script in my original post to use the STATIC cursor, but I wanted to bring this to your attention, just in case you happened to run into the same problem.