主页 / dba / 问题 / 261540
Accepted
SEarle1986
Asked: 2020-03-10 09:06:55 +0800 CST

游标 sys.databases 会跳过数据库

  • 772

我知道这个问题已经被问过好几次了，我知道解决方案，但我试图了解问题的根本原因：

我有以下代码来执行数据库备份。

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

有时，只备份了一些数据库，这表明游标没有遍历所有返回的行，或者查询本身没有返回它应该返回的所有数据库的名称。

我试图理解为什么会发生这种情况。我知道解决方法是使用STATIC游标，这表明问题出在基础查询中的结果上

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

正在发生变化，但我看不到会发生什么变化（没有数据库名称会发生​​变化，并且错误日志并不表明数据库状态已发生变化）

sql-server-2016 dmv

1 个回答

  1. Best Answer

    sys.databases是一个复杂的视图。在 SQL Server 2016 上是：

    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

    通过不指定特定的游标选项，您隐式地请求local, dynamic, updatable, optimistic, forward-only此视图上的游标。

    SQL Server 无法生成动态游标计划，因此它将游标转换为本地、键集、可更新、乐观、只进游标。

    键集意味着在打开游标时，在底层系统表中定位行所需的最少键存储在tempdb中。如果底层系统表中的这些键值中的任何一个发生更改，则不会返回一行并将@@FETCH_STATUS返回 -2。您的循环将因失败而过早退出@@FETCH_STATUS = 0，并且不会处理游标中的其他数据库。可能导致密钥更改的一个示例是更改ALLOW_SNAPSHOT_ISOLATION数据库的状态。

    当可能发生并发键更改时，键集游标是不明智的。我们不控制基础表，因此使用键集游标sys.databases是自找麻烦。检查失败也很重要@@FETCH_STATUS，而不是在到达终点之前假设成功。

    乐观游标使用校验和来检测自游标打开以来对行的更改，因此不会丢失更新。这在这里并不直接重要，因为您不是通过游标更新行，但 SQL Server 不知道这一点。它必须制定一个收集、存储和比较校验和的计划。

    旁注：在 open 和 fetch 执行计划中完成的大部分工作都是多余的，因为您只对数据库名称和state_desc列感兴趣。大多数冗余系统表访问无法删除，因为优化器没有足够的键关系信息来允许删除外连接。您可以通过添加来解决此问题DISTINCT name（因此外部连接不能重复行），但这也会强制游标降级为静态（快照）。不过，您可能有兴趣查看添加DISTINCT.

    这里的教训是明确指定您想要的游标类型，而不是依赖默认值。游标的这种使用要求静态（快照）类型：

    DECLARE db_cursor CURSOR 
    LOCAL FORWARD_ONLY STATIC READ_ONLY
    FOR ...
    • 8

相关问题