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)
 Solution for SQL Database recovery pending state

Author  Topic 

ribu.rajan
Starting Member

2 Posts

Posted - 2013-04-10 : 03:03:30

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

5072 Posts

Posted - 2013-04-10 : 11:19:34
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

2 Posts

Posted - 2013-04-11 : 01:12:18
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

5072 Posts

Posted - 2013-04-11 : 05:55:50
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?

Go to Top of Page

prett
Posting Yak Master

212 Posts

Posted - 2013-04-15 : 23:42:54
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

5072 Posts

Posted - 2013-04-15 : 23:51:18
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.
Go to Top of Page

prett
Posting Yak Master

212 Posts

Posted - 2013-04-17 : 01:04:43
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

5072 Posts

Posted - 2013-04-17 : 01:26:04
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

212 Posts

Posted - 2013-04-17 : 06:34:48
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 - 2014-03-29 : 02:16:33
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.
Go to Top of Page

jermysingh
Starting Member

2 Posts

Posted - 2014-04-26 : 08:55:54
This post would be help you in that case. Read carefully.
unspammed

Thanks
Go to Top of Page

elliswhite
Starting Member

36 Posts

Posted - 2014-05-02 : 05:30:15
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
   

- Advertisement -