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)
 Solution for SQL Database recovery pending state
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ribu.rajan
Starting Member

United Arab Emirates
2 Posts

Posted - 04/10/2013 :  03:03:30  Show Profile  Reply with Quote

Hello

I am facing a problem with one of my SQL database. The problem is database recovery pending.
My database size is around 8.5 GB, it’s a document attachments saving database, I have only 1 table in this database,
I am saving pictures, documents(PDF and word/excel files) as binary in to the table. Whenever its showing recovery pending state, I used to run this script and its working fine.

USE master
GO
ALTER DATABASE ALPHADOC
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE ALPHADOC
SET READ_WRITE
GO
ALTER DATABASE ALPHADOC
SET MULTI_USER
GO

But after a week, its again happening, and I cant tell my users to run this script and I don’t want to involve them in to this issue, because they don’t know about SQL server.
So please help me to find a permanent solution for this or how can I find the exact cause?

I have enough space in my disk, and I have my MDB file and LOG file in the correct path.

Please have a look and give me a solution

Thanks
Ribu




russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 04/10/2013 :  11:19:34  Show Profile  Visit russell's Homepage  Reply with Quote
http://www.sqlskills.com/blogs/paul/search-engine-qa-4-using-emergency-mode-to-access-a-recovery-pending-or-suspect-database/
Go to Top of Page

ribu.rajan
Starting Member

United Arab Emirates
2 Posts

Posted - 04/11/2013 :  01:12:18  Show Profile  Reply with Quote
Thank you russell for your reply.
Could you please tell what is the reason behind this behaviour?

Thanks
Ribu
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 04/11/2013 :  05:55:50  Show Profile  Visit russell's Homepage  Reply with Quote
Most likely caused by some sort of I/O problem.

Run DBCC CHECKDB on the database. And look through the Windows application event logs (start/run/eventvwr)

Do you have good backups handy?


Edited by - russell on 04/11/2013 05:56:46
Go to Top of Page

prett
Posting Yak Master

USA
205 Posts

Posted - 04/15/2013 :  23:42:54  Show Profile  Visit prett's Homepage  Reply with Quote
Most common reason of this issue is that your log file is missing or corrupt. For more information, please check this post
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 04/15/2013 :  23:51:18  Show Profile  Visit russell's Homepage  Reply with Quote
quote:
Originally posted by prett

Most common reason of this issue is that your log file is missing or corrupt. For more information, please check this post



No.

Corrupt files are usually caused by I/O errors.

Is that your blog? Sad.

Edited by - russell on 04/15/2013 23:54:02
Go to Top of Page

prett
Posting Yak Master

USA
205 Posts

Posted - 04/17/2013 :  01:04:43  Show Profile  Visit prett's Homepage  Reply with Quote
quote:
Originally posted by russell

quote:
Originally posted by prett

Most common reason of this issue is that your log file is missing or corrupt. For more information, please check this post



No.

Corrupt files are usually caused by I/O errors.

Is that your blog? Sad.



HI Russell,

Check these post:
http://www.sqlservercentral.com/Forums/Topic730583-146-1.aspx
http://social.msdn.microsoft.com/Forums/en-US/sqlkjmanageability/thread/5260bdba-f9b8-4b24-a406-964d86914dde/
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 04/17/2013 :  01:26:04  Show Profile  Visit russell's Homepage  Reply with Quote
Don't need to. I actually know what I'm talking about. You, on the other hand, have never once posted a real thought here...instead just googling the error message and often posting incorrect advice based on the first result or two you can find.

You do people a dis-service by posing as someone who can help when in reality you can't.

It is clear that you know close to nothing about the subject matter, but it doesn't stop you from posting does it?

Why?

In all honesty, I'm growing tired of constantly having to correct your incorrect and misguided posts.

Please take the time to learn the subject matter before attempting to solve people's problems.
Go to Top of Page

prett
Posting Yak Master

USA
205 Posts

Posted - 04/17/2013 :  06:34:48  Show Profile  Visit prett's Homepage  Reply with Quote
quote:
Originally posted by russell

Don't need to. I actually know what I'm talking about. You, on the other hand, have never once posted a real thought here...instead just googling the error message and often posting incorrect advice based on the first result or two you can find.

You do people a dis-service by posing as someone who can help when in reality you can't.

It is clear that you know close to nothing about the subject matter, but it doesn't stop you from posting does it?

Why?

In all honesty, I'm growing tired of constantly having to correct your incorrect and misguided posts.

Please take the time to learn the subject matter before attempting to solve people's problems.



Hi Russell,

Yes, I am also know what I mention in my post. It is not necessary that user ask any problem & I have also face the same problem. But I search that problem & found the solution for the user.

I never misguided any posts.
Go to Top of Page

bakk
Starting Member

12 Posts

Posted - 03/29/2014 :  02:16:33  Show Profile  Reply with Quote
Hello,

MDF database file of SQL Server gets corrupted due to various reasons such as abrupt shut down of system, virus attack, missing catalog files and any application errors. Hardware reason includes snags in memory, CPU, disk, etc. You can repair it by performing a manual solution by executing DBCC CHECKDB and DBCC DBREPAIR statements as a new query:

EXEC sp_resetstatus <database_name>
ALTER DATABASE <database_name> SET EMERGENCY
DBCC checkdb (<database_name>)
ALTER DATABASE <database_name> SET SINGLE_USER with ROLLBACK IMMEDIATE
DBCC DATABASE (<database_name>, REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE <database_name> SET MULTI_USER

This query may repair your SQL database file and if not then the corruption is severe and you need to make use of third party tool if you don't want to lose any of your data. I have used SysInfoTools software fro other purpose and it worked for me. you can try its unspammed software. You should first try its demo version which is a freeware.

Edited by - bakk on 03/29/2014 02:19:41
Go to Top of Page

jermysingh
Starting Member

2 Posts

Posted - 04/26/2014 :  08:55:54  Show Profile  Reply with Quote
This post would be help you in that case. Read carefully.
unspammed

Thanks
Go to Top of Page

elliswhite
Starting Member

36 Posts

Posted - 05/02/2014 :  05:30:15  Show Profile  Reply with Quote
There might be one reason that your database has been damaged due to virus attacks or some other internal server errors. In this situation u need a efficient recovery tool to recover damaged database.
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.06 seconds. Powered By: Snitz Forums 2000