Geeks With Blogs

Arthur Zubarev Compudicted

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!

Posted on Wednesday, August 25, 2010 3:07 PM | Back to top


Comments on this post: Recovering a Failed SQL Server Database From Running out of Disk Space

# re: Recovering a Failed SQL Server Database From Running out of Disk Space
Requesting Gravatar...
Thank you really it was a nice article i tried this but its so complicated method to perform recovery simple i used sql recovery software read this:- http://www.sqlrepairtool.org
Left by enrique on Feb 27, 2014 6:34 AM

Your comment:
 (will show your gravatar)


Copyright © Compudicted | Powered by: GeeksWithBlogs.net