getting database in recovery exception while executing stored procedure

Question

Post reply

getting database in recovery exception while executing stored procedure

dharti_as

SSC Enthusiast

Points: 123
More actions
March 21, 2008 at 4:10 am

#188343

Hi All,
I am getting a peculiar exception of database in recovery while executing the stored procedure. :w00t:
MathContent is the database I am using.
System.Data.SqlClient.SqlException: Database 'MathContent' is being recovered. Waiting until recovery is finished. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at WorksheetDistribution.WorksheetDistribution.DistributeWSDueForDistribution(SqlCommand MGgObjSqlCommand)
The frequency of the occurance of this exception is totally random. Someday it may not appear at all and the other day it may appear 20-30 times.
The server is equipped with SqlServer2005
I would be thankful if anyone would help me out.
Thanks,
Harsha

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135375 More actions · Answer 1

Did you enable "auto close" for this database ?

If you did, the database will be closed by sqlserver if it is not being used for some time.

If the next users wants to use it, SQLserver will need to re-open the database and will have to check db consistency (redo/undo).

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Deepak-405225 SSCertifiable Points: 6120 More actions · Answer 2

The common reasons why a database would start recovering on its own are:

1. The SQL Service was shutdown from the Service Control Manager or due a server shutdown (which you have mentioned is not your case)

2. A fatal error occurred on the database which forced SQL Server to shut down the database and recover it

3. Someone manually initiated a recovery on the database using RESTORE WITH RECOVERY command

4. A database backup was restored onto the database

During this phase your database will not respond to any user requests. Only once the recovery phase is complete will the database be accessible to users.

To find out why this happened you might want to check the SQL Server ERRORLOG and find out what find right before the recovery started on the database. Any fatal errors or database restore operations would be logged in the SQL Server ERRORLOG.

Also as mentioned above check if Auto Close option is enabled for your database. If thats true you need to turn off that option as follows,

1.Right click the database.

2.Select Properties

3.Click on Options.

4.AutoClose is the first option and ensure that it is marked as False.

[font="Verdana"]- Deepak[/font]

dharti_as SSC Enthusiast Points: 123 More actions · Answer 3

Thanks both of you...:)

The Auto Close option for my database was somehow set to true that I have made it as false.

Harsha

dharti_as SSC Enthusiast Points: 123 More actions · Answer 4

Hi All,

I turned off the AutoClose option, and the exception didnt appear for some days. But after some days it showed up again. When I checked the option, it was again set to true. After few observations, I came to conclusion that the option gets on after I run a particular job.

Now this job takes backup of the database and do some updation to the existing database. So does the backup option causes the AutoClose option to make it true????

I am doing it with following command

RESTORE DATABASE [MathContent] FROM DISK = path

Thanks

Harsha

Johan Bijnens SSC Guru Points: 135375 More actions · Answer 5

You are performing a RESTORE operation, meaning you put back a copy of the database.

If that copy has been produced when the database was in autoclose mode, that's the reason why it "reappears".

A database is always restored in the same state/mode as it has been backed up.

Best is to:

- or create a backup of the db with autoclose off

- or add an extra "alter database xyz set auto_close off " after the restore.