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.
Thanks, this worked. Someone set a db to SINGLE USER but it just sat there for 90 minutes. Killed the query and everything worked fine.. I didn't need to update the db to online state. FYI, SQL profiler can pull SPID info when other queries don't work, just get all columns and filter by SPID.
ReplyDelete-Chad Belote