dbcc checkdb('XYZ', REPAIR_ALLOW_DATA_LOSS) indicating that XYZ is already open and can only have one user at a time

Question

I have a DB, XYZ, that had a corrupt log file and now since the log file is totally unusable this is rendering this specific DB unusable and I need to be able to rebuild it.

I've already done a lot of research into this process but am getting stuck on the dbcc checkdb.

I ran the command

alter database xyz SET single_user with immediate rollback

and subsequently

use master
dbcc checkdb('xyz',REPAIR_ALLOW_DATA_LOSS)

But I keep running into this error:

Msg 924, Level 14, State 1, Line 2
Database 'XYZ' is already open and can only have one user at a time.

Everything I've researched has indicating that the DB needs to be in emergency mode and then that DB needs to be in single user mode. If I revert the DB to multi-user mode it indicates that the DB needs to be in single-user mode. Okay well I do that and then get this issue.

I've run

select spid from master..sysprocesses where dbid = DB_ID('XYZ') and spid <> @@spid

to check for any processes using the DB but no rows are returned and the logs do not show anything about the database being recovered or anything of that nature. The DB does show as "Emergency".

Any ideas?

Solution

Revert the database back to multi user mode and try something like the following.

alter database xyz SET single_user with rollback immediate 

dbcc checkdb('xyz',REPAIR_ALLOW_DATA_LOSS)

Likely another process is grabbing the database connection before you get in there. Combining both statements should ensure you get that connection.

Comments

[Christopher Bruce]

Perfect, you're the bomb.com.