Preventing error 952 Database in Transition - sys.dm_exec_requests.wait_resource is blank, what's the best workaround?

• Nadrek

  SSC-Insane

  Points: 20039

  More actions

  October 1, 2012 at 11:06 am

  #263079

  Normally, when we're going to perform operations on databases, we only want to perform them on ONLINE databases (and often READ_WRITE, ONLINE databases). Unfortunately, databases in the rollback segment of being taken OFFLINE are still listed as ONLINE (sys.databases.state = 0), and yet attempting to do many operations on them results in a 952 error, Database in transition.

  From 2005 to 2008 R2, we could use sys.dm_exec_requests.wait_resource and expect it to contain 'DATABASE: <dbid>' showing which database is in that transition state for at least most of the rollback segment, though not necessarily for quite the entire transition state where sys.databases.state = 0.

  For 2012 RTM, sys.dm_exec_requests.wait_resource is simply the empty string. So far, the workaround I've seen is going to sys.dm_tran_locks with a set of conditions that seem less than fully documented.

  SELECT DISTINCT

  RTRIM(RIGHT(sder.wait_resource,LEN(sder.wait_resource)-(CHARINDEX(' ',sder.wait_resource)-1))) AS DBIDInTransitionSQL2005_2008R2

  ,sdtl.resource_database_id AS DBIDInTransitionSQL2012

  FROM sys.dm_exec_requests sder

  INNER JOIN sys.dm_tran_locks sdtl

  ON sdtl.request_session_id = sder.session_id

  AND sdtl.request_status = 'GRANT'

  AND sdtl.resource_type = 'DATABASE'

  AND sdtl.request_owner_type = 'EXCLUSIVE_TRANSACTION_WORKSPACE'

  AND sdtl.request_mode = 'U'

  CROSS APPLY sys.dm_exec_sql_text(sder.sql_handle) sdest

  WHERE sder.command = 'ALTER DATABASE'

  AND sdest.text LIKE '%OFFLINE%'

  Or, alternately

  IF CONVERT(TINYINT,LEFT(CONVERT(VARCHAR(8000),SERVERPROPERTY('productversion')),CHARINDEX('.',CONVERT(VARCHAR(8000),SERVERPROPERTY('productversion')))-1)) < 11

  BEGIN

  SELECT DISTINCT

  -- SQL 2005 through 2008R2 make it easy

  DB_NAME(RTRIM(RIGHT(sder.wait_resource,LEN(sder.wait_resource)-(CHARINDEX(' ',sder.wait_resource)-1)))) AS DBNameInTransitionSQL2005_2012

  FROM sys.dm_exec_requests sder

  WHERE sder.command = 'ALTER DATABASE'

  AND sder.wait_resource LIKE 'DATABASE:%' -- without the wait_resource check, tempdb will show up as DBNameInTransitionSQL2005_2012 during the very last few moments of taking the database offline.

  END

  ELSE

  BEGIN

  SELECT DISTINCT

  -- SQL 2012 makes it hard, see CONNECT item https://connect.microsoft.com/SQLServer/feedback/details/765305/sys-dm-exec-requests-wait-resource-blank-for-alter-database-db-set-offline-with-rollback-immediate-during-rollback-phase

  DB_NAME(sdtl.resource_database_id) AS DBNameInTransitionSQL2005_2012

  FROM sys.dm_exec_requests sder

  INNER JOIN sys.dm_tran_locks sdtl

  ON sdtl.request_session_id = sder.session_id

  AND sdtl.request_status = 'GRANT'

  AND sdtl.resource_type = 'DATABASE'

  AND sdtl.request_owner_type = 'EXCLUSIVE_TRANSACTION_WORKSPACE'

  AND sdtl.request_mode = 'U'

  WHERE sder.command = 'ALTER DATABASE'

  END

  Does anyone have better suggestions on how to figure out which databases are not only online, but are not transitioning to an offline (or read only, etc.) state?

  Please reference the Connect item:

  https://connect.microsoft.com/SQLServer/feedback/details/765305/sys-dm-exec-requests-wait-resource-blank-for-alter-database-db-set-offline-with-rollback-immediate-during-rollback-phase