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?
Best Answer
Revert the database back to multi user mode and try something like the following.
Likely another process is grabbing the database connection before you get in there. Combining both statements should ensure you get that connection.