Sql-Server-2016
游標 sys.databases 會跳過數據庫
我知道這個問題已經被問過好幾次了，我知道解決方案，但我試圖了解問題的根本原因：
我有以下程式碼來執行數據庫備份。
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'正在改變，但我看不到會發生什麼變化（沒有數據庫名稱會改變，並且錯誤日誌並不表明數據庫狀態已經改變）
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 ...