Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Database (In Recovery)

Author  Topic 

chrisjkelly
Starting Member

6 Posts

Posted - 2010-05-09 : 09:48:51
Hi,

I'm after some urgent assistance.

I was running scripts aimed at clearing large amounts of data from a database on our server. This subsequently seemed to cause issues (I suspect due to free disk space)and the db went into 'recovery'.

In management studio (SQL 2005) the database appeared as databasename(in recovery). I have subsequently gone back in to check and the in recovery is gone but I have lost the ability to expand the database tree, to view tables etc.

Checking the error logs it appears it got to 27% and then ran out of disk space. I cannot perform a restore of the db as this option is greyed out. Can anyone suggest next actions?

I will arrange for more disk space as a priority but will the rocovery continue trying and once disk space is available complete? Or is it now stuck in a loop?

I would appreciate any advice.

Thanks.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-05-09 : 10:23:39
What's the current state of the database? (query sys.databases)

If you clear space and restart SQL, the recovery might restart. It depends on exactly what happened and what state the DB is now in. Otherwise, drop the DB and restore from backup.

--
Gail Shaw
SQL Server MVP
Go to Top of Page

chrisjkelly
Starting Member

6 Posts

Posted - 2010-05-09 : 10:52:48
quote:
Originally posted by chrisjkelly

quote:
Originally posted by GilaMonster

What's the current state of the database? (query sys.databases)

If you clear space and restart SQL, the recovery might restart. It depends on exactly what happened and what state the DB is now in. Otherwise, drop the DB and restore from backup.

--
Gail Shaw
SQL Server MVP





Gail,

Really appreciate your response. Could you give me an idea of how to query the sys databases to find out the state of the database and if I need to how I drop and restore from backup?

I'm not a DBA by any stretch of the imaginiation.

Thanks
Go to Top of Page

chrisjkelly
Starting Member

6 Posts

Posted - 2010-05-09 : 10:56:35
Gail,

A google solved that one ... the database status is 'recovery pending'?

Could you suggest best next steps?

One of the major problems we have is the free space on the G: which hosts the database files is now down to 9MB. I guess why the recover fell over.

Thanks,
Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-05-09 : 12:07:31
Stop the SQL service.
Free up some space, make sure there's enough space there for the recovery to complete.
Restart the SQL service.

with the DB in recovery pending, SQL will try again, restarting the recovery process from scratch, next time it starts up.

--
Gail Shaw
SQL Server MVP
Go to Top of Page

chrisjkelly
Starting Member

6 Posts

Posted - 2010-05-09 : 12:33:28
Thanks Gail - thats super helpful. Unfortunately the partition is completely full; its a 123GB partition with the MDF at 80GB and the LDF at 40GB. I think we'll have to actually create more space for it. Really appreciate you taking the time to get respond.
Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-05-09 : 14:32:21
Do not try to detach that database recovery-pending database. It may not reattach.

Do you have another drive in the server with more space? Can you expand that partition without damaging the files on it?

--
Gail Shaw
SQL Server MVP
Go to Top of Page

chrisjkelly
Starting Member

6 Posts

Posted - 2010-05-09 : 14:45:35
We dont have any others with free space (at least at the volumes required) at the minute but we could add another. I'm not sure if we have any tools which would allow me to extend the partition without formatting. Although I know some are available.
Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-05-09 : 16:36:32
Let me consult an expert....

--
Gail Shaw
SQL Server MVP
Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-05-09 : 18:32:03
I'm told that you asked this over at MSDN as well, and got a whole load of good advice from some good people. I suggest that you continue with that thread rather than this one.

--
Gail Shaw
SQL Server MVP
Go to Top of Page

Meshax
Starting Member

1 Post

Posted - 2011-06-02 : 05:40:30
Hi guys, my c drive is full and the SQL database cant open because of space. is there a way i can purge the the LDF file? PLZ HELP

Guru
Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-06-02 : 06:29:09
Please post new questions in a new thread. Thank you.

--
Gail Shaw
SQL Server MVP
Go to Top of Page

johnycruen
Starting Member

2 Posts

Posted - 2011-07-21 : 14:13:35
hi meshax,

Have you kept any backup of the SQL database which is present as user database by SQL Restore process?
Go to Top of Page

jayesh.bhandari
Starting Member

1 Post

Posted - 2013-02-12 : 03:07:21
hi gila,
same issue,, only changes is i have space i have used DBCC shrink command after completion of command. i had hard reboot the server.
after reboot Error appeared.

Pls help me with these.


thanks with regards
Jayesh Bhandari
Go to Top of Page

fadric
Starting Member

1 Post

Posted - 2013-04-30 : 04:28:18
unspammed
Go to Top of Page

laminalikwo
Starting Member

1 Post

Posted - 2014-04-13 : 11:12:48
See here: http://blog.sqlauthority.com/2014/02/11/sql-server-systools-sql-recovery-software-an-experiment-to-recover-database-corruption/ & recover sql databases from suspect due to full disk space which has a very nice explanation
Go to Top of Page

barts814
Starting Member

2 Posts

Posted - 2014-04-21 : 03:31:49
To retrieve your lost and damaged SQL database from SQL server. You can try RecoveyFix for SQL database recovery software tool to retrieve your lost files. For more details: unspammed
Go to Top of Page

jermysingh
Starting Member

2 Posts

Posted - 2014-04-26 : 08:54:20
Here is the blog post, where you can read How to Repair SQL database manually. Read carefully this post and try yourself to solve problem.
unspammed

I hope this post will help you.
Thanks
Go to Top of Page

elliswhite
Starting Member

36 Posts

Posted - 2014-05-02 : 05:27:01
u can use online market available free tool which will certainly recover your sql database all items and u will be able to open tables data easily.
Go to Top of Page
   

- Advertisement -