Just had a SQL Server 2005 database completely becoming unavailable after the drive on which its files were residing on completely ran out of space (thanks god in development). Freeing some space turned out to be a more or less strait-forward step. Now the recovery stage.
Interestingly, out of a dozen databases (all in SQL 2000 compatibility mode) only one became affected. When I started to figure out what is wrong I checked the SQL Server log first and could see that another database’s log file gave a 112 error (No enough space on the disk). Then the database that was affected reported that its transaction log is full followed by an error about an inability to allocate a new page, naturally the log became subsequently unavailable. The database was attempted to restart, but surely failed. This left me with a non-operable database, to such an extent trying to open say its properties in SSIS produced error “Database ‘DB’ cannot be opened due to inaccessible files or insufficient memory or disk space”. Like this:
What a nag!
My first reaction was to attempt to repair the database by issuing a
ALTER DATABASE DB SET EMERGENCY
that went well, then I put it into single user mode, so far so good, but then when I started repairing it with
DBCC CHECKDB (DB, REPAIR_ALLOW_DATA_LOSS)
after the command ran for a while, I received an error “Database is already open and can only have one user at a time”. That was a surprise, well, at that point the database was visually at the same state with a “single-user” icon applied, still no way to connect to it.
In desperation I attempted to google out what could have been the cause, however nobody experienced the same issue. I was puzzled for a moment what to do next.
My next natural reaction was to check who is using the database and I ran
select spid from master..sysprocesses where dbid = db_id('DB’) and spid <> @@spid
just to find out no process or user was actually using it. What is going on? Somehow I stumbled onto the SQL Server log window open and refreshed it. What I saw was some activity going on. Specifically messages “Recovery of database ‘db’ (23) is n% complete” were popping up. It was evident that the DBBC command reported an error, but a background process (confirmed by sp_Who2) kept working on it. Wow!.
That left me with some hope, and indeed, after some time the database recovery emitted 100% completion. Hooray! At that stage I flipped the database to the multiuser mode:
ALTER DATABASE NA_FINANCE SET MULTI_USER WITH NO_WAIT
and off I go.
The moral is – always check the SQL Server log, it is your best friend, dear DBA!