Using an auto-execute Stored Procedure to resolve 'Recovery Pending' development database

Development environments are quite unpredictable.  Yesterday I was faced with a situation where the storage housing the transaction logs for numerous development databases filled unexpectedly to capacity, and one of the development databases ended up in 'recovery pending' status.  This particular database was the target of numerous SSIS packages, and staging operations.  Plenty of traffic from new connections, and some of those connections would execute cross-database queries.

I took care of the shortage of transaction log space, and turned my attention to the sasquatch_2012 database, in 'recovery pending' mode.  Trying to alter the database to single user mode, or even set the database offline, continued to fail due to pressure from competing connections.

I rigged a couple of loops to kill sessions based on their database ID as recorded in the DMVs, and even a loop that would kill sessions based on the resource database from the sys.dm_tran_locks dmv.  No dice.  There seem to have been sessions queued for resource locks in a manner such that even after the session was killed, it would still come up for the resource in its queuing order and could prevent alter database statements from succeeding.  I came to that conclusion after noting my SSMS session as 58, closing the SSMS session, opening another session and later seeing session 58 (with my Windows host ID and username attached) pop up as a connection in the database.

To recover the database I set the "auto-execute on start" stored procedure to an sp that would set SASQUATCH_2012 offline. 

After waring all developers and testers, I used cluster services manager to take the SQL Server service offline, and then brought it back online.  The sasquatch_2012 database was offline as expected after the SQL Server service restarted.

I set the database to online, used the database, and set to single user.  Ran dbcc checkdb with repair, and after the repair set it to multi-user as below.  Didn't forget to undo the magic 'auto start stored procedure' - I don't want the sasquatch_2012 database to be offline every time SQL Server restartds.  :)



----create the auto stored procedure
create procedure x_sasquatch_2012_offline
as

ALTER DATABASE sasquatch_2012 set offline

----set the stored procedure to execute on startup
sp_procoption @ProcName = 'x_sasquatch_2012_offline' , @OptionName = 'startup' , @OptionValue = 'On'

----then use cluster manager to take the service offline and back online

----SASQUATCH_2012 was offline then as I expected
ALTER DATABASE SASQUATCH_2012 SET ONLINE;
GO
USE SASQUATCH_2012;  --- important to be in the database when setting it to single user :)
ALTER DATABASE SASQUATCH_2012 SET SINGLE_USER;
GO
----Now its single user, do dbcc checkdb
----In a development environment, I'm ok with the risk of data loss during data repair
DBCC CHECKDB ('SASQUATCH_2012',REPAIR_ALLOW_DATA_LOSS)


----abbreviated results from dbcc checkdb
DBCC results for 'SASQUATCH_2012'.
Service Broker Msg 9675, State 1: Message Types analyzed: 14.
Service Broker Msg 9676, State 1: Service Contracts analyzed: 6.
Service Broker Msg 9667, State 1: Services analyzed: 3.
Service Broker Msg 9668, State 1: Service Queues analyzed: 3.
Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0.
Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0.
Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0.
Service Broker Msg 9605, State 1: Conversation Priorities analyzed: 0.
Repair: The page (1:798380) has been deallocated from object ID 0, index ID -1, partition ID 0, alloc unit ID 72057594058899456 (type Unknown).
Repair: The page (1:798381) has been deallocated from object ID 0, index ID -1, partition ID 0, alloc unit ID 72057594058899456 (type Unknown).
Msg 2576, Level 16, State 1, Line 1
The Index Allocation Map (IAM) page (0:0) is pointed to by the previous pointer of IAM page (1:798381) in object ID 0, index ID -1, partition ID 0, alloc unit ID 72057594058899456 (type Unknown), but it was not detected in the scan.
        The error has been repaired.
CHECKDB found 1 allocation errors and 0 consistency errors not associated with any single object.
CHECKDB fixed 1 allocation errors and 0 consistency errors not associated with any single object.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
CHECKDB found 1 allocation errors and 0 consistency errors in database 'SASQUATCH_2012'.
CHECKDB fixed 1 allocation errors and 0 consistency errors in database 'SASQUATCH_2012'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.



----Yay!!!!  Now we can set it back to multi-user and be done.  For now...
ALTER DATABASE SASQUATCH_2012 SET MULTI_USER;
GO


----Finally, undo the little magic
USE MASTER
----set the stored procedure to NOT execute on startup
EXEC sp_procoption @ProcName = 'x_sasquatch_2012_offline' , @OptionName = 'startup' , @OptionValue = 'Off'