SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Database (In Recovery)
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

chrisjkelly
Starting Member

6 Posts

Posted - 05/09/2010 :  09:48:51  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 05/09/2010 :  10:23:39  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
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 - 05/09/2010 :  10:52:48  Show Profile  Reply with Quote
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 - 05/09/2010 :  10:56:35  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 05/09/2010 :  12:07:31  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
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

Edited by - GilaMonster on 05/09/2010 12:10:42
Go to Top of Page

chrisjkelly
Starting Member

6 Posts

Posted - 05/09/2010 :  12:33:28  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 05/09/2010 :  14:32:21  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
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 - 05/09/2010 :  14:45:35  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 05/09/2010 :  16:36:32  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
Let me consult an expert....

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

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 05/09/2010 :  18:32:03  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
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

Edited by - GilaMonster on 05/09/2010 18:39:04
Go to Top of Page

Meshax
Starting Member

Kenya
1 Posts

Posted - 06/02/2011 :  05:40:30  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 06/02/2011 :  06:29:09  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
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 - 07/21/2011 :  14:13:35  Show Profile  Reply with Quote
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

India
1 Posts

Posted - 02/12/2013 :  03:07:21  Show Profile  Reply with Quote
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 Posts

Posted - 04/30/2013 :  04:28:18  Show Profile  Reply with Quote
unspammed

Edited by - fadric on 04/30/2013 04:30:21
Go to Top of Page

laminalikwo
Starting Member

1 Posts

Posted - 04/13/2014 :  11:12:48  Show Profile  Reply with Quote
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

India
2 Posts

Posted - 04/21/2014 :  03:31:49  Show Profile  Reply with Quote
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

Edited by - barts814 on 04/22/2014 01:03:12
Go to Top of Page

jermysingh
Starting Member

2 Posts

Posted - 04/26/2014 :  08:54:20  Show Profile  Reply with Quote
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 - 05/02/2014 :  05:27:01  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.2 seconds. Powered By: Snitz Forums 2000