How to Resolve Error 952: Database is in Transition

Today when I tried to run sp_who2 when troubleshooting one issue, however I received the following error message.  

      Database '***' is in transition. Try the statement later.
      Error: 952, Severity: 16, State: 1

What would happen when DBA couldn’t run sp_who/sp_who2 or even select statement from sysprocesses table. Panicking? Or restarting SQL Services? Nope and nope! Read the error message and find what this leads you to. Those are the steps that I have taken to resolve the issue.

1.       Find which spid cause the database in transition.

-- Try to find which SPIDs are running against the trouble database and check if there are any blockings

select * from sys.dm_exec_requests

The database was in transition, because someone tried to set the database offline, but the database was in used by a lot of other processes that blocked the alter database statement.

2.       Kill the process(es) that caused issue

3.       Set database offline properly by running the following queries.

--Replace *** with the database name

Alter Database *** Set Offline With Rollback Immediate

The problem was resolved after those steps.

List Table Sizes on All Databases

This post is for Julian...One of my dear trouble makers! :)))

Junlian came to DBA team to ask for a report of table size of all tables on all databases. My instant thought was to use the standard report of disk usage by table. However, no no no, he wanted more. The report only provides the table size for each database and doesn’t list all databases’ table sizes. And then I tried to combine sp_MSforeachdb and sp_MSforeachtable mentioned in the previous post to check all the tables on all the databases. Bad news! They don't work together well. OK, more work, and lets write a script from scratch then. How exciting!

Finally I came up with this solution.

IF Object_id('tempdb..#DatabaseTables') IS NOT NULL

  DROP TABLE #databasetables;

CREATE TABLE #databasetables

  (

     id        INT IDENTITY(1, 1),

     [dbname]  SYSNAME,

     tablename NVARCHAR(200)

  )

IF Object_id('tempdb..#TableSizeStaging') IS NOT NULL

  DROP TABLE #TableSizeStaging;

CREATE TABLE #TableSizeStaging

  (

     tablename  NVARCHAR(200),

     rows       CHAR(11),

     reserved   VARCHAR(18),

     data       VARCHAR(18),

     index_size VARCHAR(18),

     unused     VARCHAR(18)

  )