7

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?

Christopher Bruce
  • 329
  • 2
  • 4
  • 14

2 Answers2

9

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.

Ian Chamberland
  • 670
  • 3
  • 7
0

Level 14 belongs to permission denied. That means, you cannot access the database because, someone is using it.

You can use sp_who which provides the information about the current users, sessions, and processes in an instance of the Microsoft SQL Server Database Engine.

After that, you can set the database into single user mode and perform the repair action. before performing any action on the DB, please make a copy of your database.

If you are going to play with Repair allow data loss option then, you have to be very careful. It should be the last resort to fix the corruption.

Priyanka
  • 1
  • 1