Corrupted Log File

I received a call from one of my friends saying that he is unable to access his database which is said to be one of the most critical database in the organization.

When he tried to access the database he is getting following error message.

Database ‘ABCD’ cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.

When I asked few question it seems like his log drive was filled to it’s 100% and log got corrupted. Since he didn’t’ have any recent backups , I was little reluctant to delete the log fill and attached the data file. I kept that option as my last resort.

So I asked him to run following statements.

SELECT name,state,state_desc FROM sys.databases 

WHERE  name='ABCD' 

Output for state_desc was RECOVERY_PENDING.

I instructed him to set database to the emergency mode.

ALTER DATABASE ABCD SET EMERGENCY 

Then, set the database to single use mode and started the rollback.

ALTER DATABASE ABCD SET SINGLE_USER 

WITH ROLLBACK IMMEDIATE

Then rebuilt the log by ,

DBCC CHECKDB('ABCD',REPAIR_ALLOW_DATA_LOSS)

Then executed the initial statement and verified that database is ONLINE.

Below execution is to make that every use can log into the database.

ALTER DATABASE ABCD SET MULTI_USER

With this, users were able to login to the database without any issues.