| 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 ShawSQL Server MVP |
 |
|
|
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 ShawSQL 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 |
|
|
|
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, |
|
|
|
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 ShawSQL Server MVP |
|
|
|
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. |
|
|
|
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 ShawSQL Server MVP |
|
|
|
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. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-05-09 : 16:36:32
|
| Let me consult an expert....--Gail ShawSQL Server MVP |
|
|
|
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 ShawSQL Server MVP |
|
|
|
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 HELPGuru |
|
|
|
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 ShawSQL Server MVP |
|
|
|
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? |
|
|
|
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 regardsJayesh Bhandari |
|
|
|
fadric
Starting Member
1 Post |
Posted - 2013-04-30 : 04:28:18
|
| unspammed |
|
|
|
laminalikwo
Starting Member
1 Post |
|
|
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 |
|
|
|
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.unspammedI hope this post will help you.Thanks |
|
|
|
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. |
|
|
|
|
Database (In Recovery)