Cursoring over sys.databases skips databases
searle1986 (imported from SE) 
I know this question has been asked been 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)
Top Answer
Paul White 
`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 ...
```
comments
transcript

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.