The title is adapted from a child-hood movie of mine and is my daily (database-related) WTF moment and it is my entry for this month’s T-SQL Tuesday found: http://www.pontop.dk/single-post/2017/03/07/Announcing-T-SQL-Tuesday-88-%E2%80%93-The-daily-database-related-WTF

Having a database in Recovery Pending mode is no laughing matter, it means that it knows that recovery has to be run but something is stopping it from doing so, I just want it online ASAP.

Naturally I start running DBCC CHECKDB.

 DBCC CHECKDB ('FAT') WITH NO_INFOMSGS

Msg 945, Level 14, State 2, Line 3

Database ‘FAT’ cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details. So I check the error log.

Looks like I had open transactions while my transaction log got lost during an outage. I tried switching the database online but that failed.

Msg 5181, Level 16, State 5, Line 4 Could not restart database “FAT”. Reverting to the previous status. Msg 5069, Level 16, State 1, Line 4 ALTER DATABASE statement failed.

Accessing the database is the real challenge now.

I am in a mess and I have no backups! I am just desperate to get into the database I have no choice but to go with my last option, emergency repair. Now if I switched to emergency mode and that failed I probably would’ve passed out and would be free to watch YouTube for the rest of the day.

Here is the last resort.

ALTER DATABASE [fat] SET SINGLE_USER
ALTER DATABASE [fat] SET EMERGENCY;
GO
DBCC CHECKDB (N'fat', REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS, NO_INFOMSGS;

Warning: The log for database ‘FAT’ has been rebuilt. Transactional consistency has been lost. The RESTORE chain was broken, and the server no longer has context on the previous log files, so you will need to know what they were. You should run DBCC CHECKDB to validate physical consistency.. When you are ready to make the database available for use, you will need to reset database options and delete any extra log files.

So I follow the instructions from the message because it rebuilt my transaction log.

 ALTER DATABASE [fat] SET MULTI_USER
GO
DBCC CHECKDB ('FAT')

CHECKDB found 0 allocation errors and 0 consistency errors in database ‘FAT’.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Also you should run DBCC CHECKCONSTRAINTS after this sort of repair and now is a great time to re-establish those backups

So why is this a horror story? Firstly I had no backups available and secondly I had to rely on a command which you should not really be relying on ( I could’ve also gone down the data extract route too)…