4

I know this question has been asked several times and I know the solution, but I am trying to understand the underlying cause of the problem:

I have the following code to perform database backups.

DECLARE @Filename   VARCHAR(256)
DECLARE @FileDate   VARCHAR(15) 
DECLARE @Path       VARCHAR(50) 
DECLARE @Name       VARCHAR(50) 

-- specify database backup directory
SET @Path = '\MyPath'

-- specify filename date
SELECT @FileDate = CONVERT(VARCHAR(20), GETDATE(), 112) + '_' + REPLACE(CONVERT(VARCHAR(20), GETDATE(), 108),':','')

DECLARE db_cursor CURSOR FOR
    SELECT [name]
        FROM master.sys.databases
        WHERE [name] NOT IN ('master', 'msdb', 'model', 'tempdb')
            AND [state_desc] = 'ONLINE'

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @Name

WHILE @@FETCH_STATUS = 0
BEGIN

    SET @Filename = @Path + @Name + '_Full_Backup_' + @FileDate + '.bak'
    BACKUP DATABASE @Name
    TO DISK = @Filename
    WITH CHECKSUM,
        COMPRESSION

    FETCH NEXT FROM db_cursor INTO @Name
END

CLOSE db_cursor
DEALLOCATE db_cursor

Sometimes, only some databases are backed up, suggesting that the cursor is not iterating over all of the rows returned or that the query itself is not returning the names of all the databases it should be.

I am trying to understand why this happens. I know that the fix is to use a STATIC cursor, suggesting the issue is with the results in the underlying query

SELECT [name]
    FROM master.sys.databases
    WHERE [name] NOT IN ('master', 'msdb', 'model', 'tempdb')
        AND [state_desc] = 'ONLINE'

are changing, but I can't see what would be changing (no database names would change and the Error log doesn't suggest that the database state has changed)

0

1 Answer 1

8

sys.databases is a complex view. On SQL Server 2016 it is:

CREATE VIEW sys.databases AS
    SELECT d.name, 
        d.id AS database_id,
        r.indepid AS source_database_id,
        d.sid AS owner_sid,
        d.crdate AS create_date,
        d.cmptlevel AS compatibility_level,
        -- coll.value = null means that a collation was not specified for the DB and the server default is used instead
        convert(sysname, case when serverproperty('EngineEdition') = 5 AND d.id = 1 then serverproperty('collation')
                                 else CollationPropertyFromID(convert(int, isnull(coll.value, p.cid)), 'name') end) AS collation_name,
        iif ((serverproperty('EngineEdition') = 5) AND (sysconv(bit, d.status & 0x10000000) = 1), cast (3 as tinyint), p.user_access) AS user_access,
        iif ((serverproperty('EngineEdition') = 5) AND (sysconv(bit, d.status & 0x10000000) = 1), 'NO_ACCESS', ua.name) AS user_access_desc,
        sysconv(bit, d.status & 0x400) AS is_read_only,         -- DBR_RDONLY
        sysconv(bit, d.status & 1) AS is_auto_close_on,         -- DBR_CLOSE_ON_EXIT
        sysconv(bit, d.status & 0x400000) AS is_auto_shrink_on,     -- DBR_AUTOSHRINK
        case when (serverproperty('EngineEdition') = 5) AND (sysconv(bit, d.status & 0x00000020) = 1) then cast (1 as tinyint) -- RESTORING
             when (serverproperty('EngineEdition') = 5) AND (sysconv(bit, d.status & 0x00000080) = 1) then cast (7 as tinyint) -- COPYING
             when (serverproperty('EngineEdition') = 5) AND (sysconv(bit, d.status & 0x00000100) = 1) then cast (4 as tinyint) -- SUSPECT
             when (serverproperty('EngineEdition') = 5) AND (sysconv(bit, d.status & 0x08000000) = 1) then cast (8 as tinyint) -- QUORUM_RECOVERY_PENDING
             when (serverproperty('EngineEdition') = 5) AND (sysconv(bit, d.status & 0x04000000) = 1) then cast (9 as tinyint) -- CREATING
             else p.state 
             end AS state, -- 7 is COPYING and 4 is SUSPECT state for database copy (UNDO: Need to have a clean way to set states in dbtable for a user db)
        case when (serverproperty('EngineEdition') = 5) AND (sysconv(bit, d.status & 0x00000020) = 1) then 'RESTORING' 
             when (serverproperty('EngineEdition') = 5) AND (sysconv(bit, d.status & 0x00000080) = 1) then 'COPYING' 
             when (serverproperty('EngineEdition') = 5) AND (sysconv(bit, d.status & 0x00000100) = 1) then 'SUSPECT'
             when (serverproperty('EngineEdition') = 5) AND (sysconv(bit, d.status & 0x08000000) = 1) then CONVERT(nvarchar(60), N'QUORUM_RECOVERY_PENDING')
             when (serverproperty('EngineEdition') = 5) AND (sysconv(bit, d.status & 0x04000000) = 1) then 'CREATING'
             else st.name 
             end AS state_desc,
        sysconv(bit, d.status & 0x200000) AS is_in_standby,     -- DBR_STANDBY
        case when serverproperty('EngineEdition') = 5 then convert(bit, 0) else p.is_cleanly_shutdown end AS is_cleanly_shutdown,
        sysconv(bit, d.status & 0x80000000) AS is_supplemental_logging_enabled, -- DBR_SUPPLEMENT_LOG
        case when (serverproperty('EngineEdition') = 5) then sysconv(tinyint, sysconv(bit,(d.status & 0x00100000)))
             else p.snapshot_isolation_state end AS snapshot_isolation_state,
        case when (serverproperty('EngineEdition') = 5) and (sysconv(bit, d.status & 0x00100000) = 1) then 'ON'
             when (serverproperty('EngineEdition') = 5) and (sysconv(bit, d.status & 0x00100000) = 0) then 'OFF'
             else si.name end AS snapshot_isolation_state_desc,     
        sysconv(bit, d.status & 0x800000) AS is_read_committed_snapshot_on,     -- DBR_READCOMMITTED_SNAPSHOT
        case when (serverproperty('EngineEdition') = 5) 
             then case 
                    when sysconv(bit,(d.status & 0x00000008)) = 1
                        then cast(3 as tinyint)
                    when sysconv(bit,(d.status & 0x00000004)) = 1
                        then cast(2 as tinyint)
                    else
                        cast(1 as tinyint)
                   end  
              else p.recovery_model 
        end AS recovery_model,
        case when (serverproperty('EngineEdition') = 5) 
             then case 
                    when sysconv(bit,(d.status & 0x00000008)) = 1
                        then CONVERT(nvarchar(60), N'SIMPLE')
                    when sysconv(bit,(d.status & 0x00000004)) = 1
                        then CONVERT(nvarchar(60), N'BULK_LOGGED')
                    else
                        CONVERT(nvarchar(60), N'FULL')
                  end   
              else ro.name 
        end AS recovery_model_desc,              
        p.page_verify_option, pv.name AS page_verify_option_desc,
        sysconv(bit, d.status2 & 0x1000000) AS is_auto_create_stats_on,         -- DBR_AUTOCRTSTATS
        sysconv(bit, d.status2 & 0x00400000) AS is_auto_create_stats_incremental_on,    -- DBR_AUTOCRTSTATSINC
        sysconv(bit, d.status2 & 0x40000000) AS is_auto_update_stats_on,        -- DBR_AUTOUPDSTATS
        sysconv(bit, d.status2 & 0x80000000) AS is_auto_update_stats_async_on,  -- DBR_AUTOUPDSTATSASYNC
        sysconv(bit, d.status2 & 0x4000) AS is_ansi_null_default_on,            -- DBR_ANSINULLDFLT
        sysconv(bit, d.status2 & 0x4000000) AS is_ansi_nulls_on,                -- DBR_ANSINULLS
        sysconv(bit, d.status2 & 0x2000) AS is_ansi_padding_on,                 -- DBR_ANSIPADDING
        sysconv(bit, d.status2 & 0x10000000) AS is_ansi_warnings_on,            -- DBR_ANSIWARNINGS
        sysconv(bit, d.status2 & 0x1000) AS is_arithabort_on,                   -- DBR_ARITHABORT
        sysconv(bit, d.status2 & 0x10000) AS is_concat_null_yields_null_on,     -- DBR_CATNULL
        sysconv(bit, d.status2 & 0x800) AS is_numeric_roundabort_on,            -- DBR_NUMEABORT
        sysconv(bit, d.status2 & 0x800000) AS is_quoted_identifier_on,          -- DBR_QUOTEDIDENT
        sysconv(bit, d.status2 & 0x20000) AS is_recursive_triggers_on,          -- DBR_RECURTRIG
        sysconv(bit, d.status2 & 0x2000000) AS is_cursor_close_on_commit_on,    -- DBR_CURSCLOSEONCOM
        sysconv(bit, d.status2 & 0x100000) AS is_local_cursor_default,          -- DBR_DEFLOCALCURS
        sysconv(bit, d.status2 & 0x20000000) AS is_fulltext_enabled,            -- DBR_FTENABLED
        sysconv(bit, d.status2 & 0x200) AS is_trustworthy_on,               -- DBR_TRUSTWORTHY
        sysconv(bit, d.status2 & 0x400) AS is_db_chaining_on,               -- DBR_DBCHAINING
        sysconv(bit, d.status2 & 0x08000000) AS is_parameterization_forced, -- DBR_UNIVERSALAUTOPARAM
        sysconv(bit, d.status2 & 64) AS is_master_key_encrypted_by_server,  -- DBR_MASTKEY
        sysconv(bit, d.status2 & 0x00000010) AS is_query_store_on,          -- DBR_QDSENABLED
        sysconv(bit, d.category & 1) AS is_published,
        sysconv(bit, d.category & 2) AS is_subscribed,
        sysconv(bit, d.category & 4) AS is_merge_published,
        sysconv(bit, d.category & 16) AS is_distributor,
        sysconv(bit, d.category & 32) AS is_sync_with_backup,
        d.svcbrkrguid AS service_broker_guid,
        sysconv(bit, case when d.scope = 0 then 1 else 0 end) AS is_broker_enabled,
        p.log_reuse_wait, lr.name AS log_reuse_wait_desc,
        sysconv(bit, d.status2 & 4) AS is_date_correlation_on,      -- DBR_DATECORRELATIONOPT
        sysconv(bit, d.category & 64) AS is_cdc_enabled,
        case 
            when (d.id = db_id('tempdb')) then sysconv(bit, p.is_db_encrypted)
            else sysconv(bit, d.status2 & 0x100)                    -- DBR_ENCRYPTION
            end AS is_encrypted,
        convert(bit, d.status2 & 0x8) AS is_honor_broker_priority_on,               -- DBR_HONORBRKPRI
        sgr.guid AS replica_id,
        sgr2.guid AS group_database_id,
        ssr.indepid AS resource_pool_id,
        default_language_lcid = case when ((d.status2 & 0x80000)=0x80000 AND p.containment = 1) then convert(smallint, p.default_language) else null end,
        default_language_name = case when ((d.status2 & 0x80000)=0x80000 AND p.containment = 1) then convert(sysname, sld.name) else null end,
        default_fulltext_language_lcid = case when ((d.status2 & 0x80000)=0x80000 AND p.containment = 1) then convert(int, p.default_fulltext_language) else null end,
        default_fulltext_language_name = case when ((d.status2 & 0x80000)=0x80000 AND p.containment = 1) then convert(sysname, slft.name) else null end,
        is_nested_triggers_on = case when ((d.status2 & 0x80000)=0x80000 AND p.containment = 1) then convert(bit, p.allow_nested_triggers) else null end,
        is_transform_noise_words_on = case when ((d.status2 & 0x80000)=0x80000 AND p.containment = 1) then convert(bit, p.transform_noise_words) else null end,
        two_digit_year_cutoff = case when ((d.status2 & 0x80000)=0x80000 AND p.containment = 1) then convert(smallint, p.two_digit_year_cutoff) else null end,
        containment = sysconv(tinyint, (d.status2 & 0x80000)/0x80000), -- DBR_IS_CDB
        containment_desc = convert(nvarchar(60), cdb.name),
        p.recovery_seconds AS target_recovery_time_in_seconds,
        p.delayed_durability,
        case when (p.delayed_durability = 0) then CAST('DISABLED' AS nvarchar(60)) -- LCOPT_DISABLED
             when (p.delayed_durability = 1) then CAST('ALLOWED' AS nvarchar(60)) -- LCOPT_ALLOWED
             when (p.delayed_durability = 2) then CAST('FORCED' AS nvarchar(60)) -- LCOPT_FORCED
             else NULL
             end AS delayed_durability_desc,
        convert(bit, d.status2 & 0x80) AS 
        is_memory_optimized_elevate_to_snapshot_on,             -- DBR_HKELEVATETOSNAPSHOT
        sysconv(bit, d.category & 0x100) AS is_federation_member,
        convert(bit, isnull(rda.value, 0)) AS is_remote_data_archive_enabled,
        convert(bit, p.is_mixed_page_allocation_on) AS is_mixed_page_allocation_on
    FROM sys.sysdbreg d OUTER APPLY OpenRowset(TABLE DBPROP, (case when serverproperty('EngineEdition') = 5 then DB_ID() else d.id end)) p
    LEFT JOIN sys.syssingleobjrefs r ON r.depid = d.id AND r.class = 96 AND r.depsubid = 0  -- SRC_VIEWPOINTDB
    LEFT JOIN sys.syspalvalues st ON st.class = 'DBST' AND st.value = p.state
    LEFT JOIN sys.syspalvalues ua ON ua.class = 'DBUA' AND ua.value = p.user_access
    LEFT JOIN sys.syspalvalues si ON si.class = 'DBSI' AND si.value = p.snapshot_isolation_state
    LEFT JOIN sys.syspalvalues ro ON ro.class = 'DBRO' AND ro.value = p.recovery_model
    LEFT JOIN sys.syspalvalues pv ON pv.class = 'DBPV' AND pv.value = p.page_verify_option
    LEFT JOIN sys.syspalvalues lr ON lr.class = 'LRWT' AND lr.value = p.log_reuse_wait
    LEFT JOIN sys.syssingleobjrefs agdb ON agdb.depid = d.id AND agdb.class = 104 AND agdb.depsubid = 0 -- SRC_AVAILABILITYGROUP 
    LEFT JOIN master.sys.syssingleobjrefs ssr ON ssr.class = 108 AND ssr.depid = d.id -- SRC_RG_DB_TO_POOL
    LEFT JOIN master.sys.sysclsobjs  ag ON ag.id = agdb.indepid AND ag.class = 67 -- SOC_AVAILABILITY_GROUP
    LEFT JOIN master.sys.sysguidrefs sgr ON sgr.class = 8 AND sgr.id = ag.id AND sgr.subid = 1 -- GRC_AGGUID / AGGUID_REPLICA_ID
    LEFT JOIN master.sys.sysguidrefs sgr2 ON sgr2.class = 9 AND sgr2.id = ag.id AND sgr2.subid = d.id -- GRC_AGDBGUID
    LEFT JOIN sys.syspalvalues cdb ON cdb.class = 'DCDB' AND cdb.value = CASE WHEN (d.status2 & 0x80000)=0x80000 THEN 1 ELSE 0 END
    LEFT JOIN sys.syslanguages sld ON sld.lcid = p.default_language
    LEFT JOIN sys.fulltext_languages slft ON slft.lcid = p.default_fulltext_language
    LEFT JOIN sys.sysobjvalues coll ON coll.valclass = 102 AND coll.subobjid = 0 AND coll.objid = d.id  -- SVC_DATACOLLATION
    LEFT JOIN sys.sysobjvalues rda ON rda.valclass = 116 AND rda.objid = d.id AND rda.valnum = 0 -- SVC_STRETCH & STRETCH_DB_IS_STRETCHED
    WHERE d.id < 0x7fff
        AND has_access('DB', (case when serverproperty('EngineEdition') = 5 then DB_ID() else d.id end)) = 1

By not specifying specific cursor options, you are implicitly requesting a local, dynamic, updatable, optimistic, forward-only cursor over this view.

SQL Server cannot produce a dynamic cursor plan, so it converts the cursor to a local, keyset, updatable, optimistic, forward-only cursor.

Keyset means the minimal keys needed to locate rows in the underlying system tables are stored in tempdb when the cursor is opened. If any of these key values change in the underlying system tables, a row will not be returned and @@FETCH_STATUS will return -2. Your loop will prematurely exit because @@FETCH_STATUS = 0 fails, and no further databases from the cursor will be processed. An example that could cause a key change is altering the ALLOW_SNAPSHOT_ISOLATION state for a database.

A keyset cursor is unwise when concurrent key changes are possible. We don't control the underlying tables, so using a keyset cursor on sys.databases is asking for trouble. It is also important to check @@FETCH_STATUS for failures, rather than assuming success until the end is reached.

Optimistic cursors use checksums to detect changes to rows since the cursor was opened, so updates are not lost. This is not directly important here, because you are not updating rows through the cursor, but SQL Server doesn't know that. It has to build a plan that collects, stores, and compares checksums.

Side note: Much of the work done in the open and fetch execution plans is redundant because you are only interested in database names and the state_desc column. Most of the redundant system table accesses cannot be removed because the optimizer doesn't have sufficient key relationship information to allow outer join removal. You can work around this by adding DISTINCT name (so the outer joins cannot duplicate rows), but that also forces a cursor downgrade to static (snapshot). Nevertheless, you might be interested to look at the effect on the execution plans of adding DISTINCT.

The lesson here is to specify the type of cursor you want explicitly, and not rely on the defaults. This use of a cursor mandates a static (snapshot) type:

DECLARE db_cursor CURSOR 
    LOCAL FORWARD_ONLY STATIC READ_ONLY
    FOR ...
1
  • 1
    Great answer - thank you. Having looked at sys.databases I suspect log_reuse_wait_desc could be the cause as we have transaction log backups running every 20 mins
    – SE1986
    Commented Mar 10, 2020 at 16:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.