.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Database in recovery

Posted By:      Posted Date: September 30, 2010    Points: 0   Category :Sql Server

I have a database currently showing to be in recovery.  I have not been able to find a GUI method to monitor the progress (to determine when it might complete) so is there a command line T-SQL method to monitor the recovery process?


View Complete Post

More Related Resource Links

execute update timedout expires , table with trigger due to recovery of database


I have table A in database A  , in table A i have trigger to insert record to database B table A, each update and insert of database A -table A, triiger fire and insert record in database B table A, when i try to update some times asp.net application gives error

"Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding."

and then error log says System.Data.SqlClient.SqlException: Database 'database B 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 ret

Database in recovery

Our server ran out of disk space during a stored procedure process. We were able to free up some space, stopped all SQL services, restarted all SQL services and now SQL Management Studio shows the database to be in recovery.  Is there a way to monitor this to ensure progress is being made and to estimate when it might complete?

My Sql server 2005 Database in recovery mode for 3 -4 hours every day

Hi , Some of our Sql Server 2005 databases remain in recovery mode after midnight for almost 3 to 4 hours. So when my application access them error "Databse in recovery" and for same database this happens again and again every night. Please suggest why and how this can be solved?

Wait on the Database Engine recovery handle failed. Check the SQL Server error log for potential cau

Attempting to upgrade from SQL 2005 EE to SQL 2008 EE on Vmware ESX 3.5 virtual server. below is info from Detail.txt related to successful install with failures... 2010-09-02 13:50:46 Slp: Sco: Attempting to check if file 'D:\Data\MSSQL.1\MSSQL\LOG\ERRORLOG' exists 2010-09-02 13:50:46 Slp: Sco: Returning security descriptor O:BAG:SYD:AI(A;ID;FA;;;S-1-5-21-3788118842-658835432-3441893140-1006)(A;ID;FA;;;S-1-5-21-3788118842-658835432-3441893140-1007)(A;ID;FA;;;BA)(A;ID;FA;;;SY)S:AI(AU;IDFA;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;WD) 2010-09-02 13:50:46 Slp: Configuration action failed for feature SQL_Engine_Core_Inst during timing ConfigRC and scenario ConfigRC. 2010-09-02 13:50:46 Slp: Wait on the Database Engine recovery handle failed. Check the SQL Server error log for potential causes. 2010-09-02 13:50:46 Slp: Configuration action failed for feature SQL_Engine_Core_Inst during timing ConfigRC and scenario ConfigRC. 2010-09-02 13:50:46 Slp: Microsoft.SqlServer.Configuration.SqlEngine.SqlEngineConfigException: Wait on the Database Engine recovery handle failed. Check the SQL Server error log for potential causes. 2010-09-02 13:50:46 Slp:    at Microsoft.SqlServer.Configuration.SqlEngine.SqlServerServiceBase.WaitSqlServerStart(Process processSql) 2010-09-02 13:50:46 Slp:    at Microsoft.SqlServer.Configuration.SqlEngine.SqlServerServiceSCM.StartSqlServer(St

Recovery of Database Disabled SQL Agent, Cannot Detach Database, Etc... Please Help

I am a beginner SQL user, as the company laid off the IT guy and I am stuck with this.  So I really need some help on this one.  Any ideas would be appreciated. I was doing a merge join command to put data from one database to another.  These are 60GB databases with valuable data.  Halfway through I was told by client to just stop the query.  I did this but it left the good database with a large 80GB log file. When I rebooted the machine the recovery of this database took 5-10 minutes...  Thus the SQL Agent disabled itself waiting for recovery.  After recovery was 100% complete with no errors SQL Agent will not restart.  I can do a force turn on using a "1" statement, but the SQL Agent log file says it is still waiting for recovery. In any event, I cannot detach the database because it says 1 user connected.  I cannot backup, drop, etc...  I saw in one forum a guy said to turn service off, copy database to a new location, turn service back on, delete the database, attach copied database...  But I am a little worried about deleting. Once again - I am a SQL newbie and really need a pointer on this one.  I need to free this database and get a it back to a normal sized log file. Advice???

Database peaks CPU after recovery. Stuck in recovery mode

Rather simple problem. The database is stuck in recovery mode and I can't use it.. mssqlserver process want 100% CPU. I left it in recovery mode for almost a week with no luck. My idea was that it would repair itself. But it doesn't help and now I'm about to give up on that database. It seems quite odd that the last log entries in the ERRORLOG says: "Recovery complete..  phase 2 of 3" and then it says "Recovery 97% complete ..phase 3 of 3" After that, CPU peaks. Any ideas? Thank you! ERRORLOG: 2009-09-14 15:52:26.89 Server      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)     Nov 24 2008 13:01:59     Copyright (c) 1988-2005 Microsoft Corporation     Express Edition on Windows NT 5.2 (Build 3790: Service Pack 2) 2009-09-14 15:52:26.89 Server      (c) 2005 Microsoft Corporation. 2009-09-14 15:52:26.89 Server      All rights reserved. 2009-09-14 15:52:26.89 Server      Server process ID is 8860. 2009-09-14 15:52:26.89 Server      Authentication mode is WINDOWS-ONLY. 2009-09-14 15:52:26.89 Server      Logging SQL Server messages in file 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'. 2009-09-14 15:52:26.89 Server      This instance of SQL Server last reported using a process ID of 7996 at 9/14/2009 3:15:48 PM (local) 9/14/2009 1:15:48 PM (UTC). This is an informational message only; no user action is required. 2009-0

Database Switched to Simple Recovery Model


Hi there,

We came across a situation where the transaction log was full and we extended the disk that holds the transaction log. What happened after that is the log truncated and we noticed the recovery model of the database switched from FULL to SIMPLE. Does anyone come across this behaviour?

Thanks in advance.


Yong Hwee

SQL Server 2005 Database recovery takes long when starting


I have a large database which is consistently taking between 30 and 60 minutes to come up after restarting SQL Server.  How can I speed this up?  Please read below before responding.

I have a Winows 2003, 2 node cluster running 1 instance of SQL Server 2005 enterprise ed.    The server has 64GB of RAM, and 4 Quad core chips (16 logical processors).  The storage is on SAN, and is a mix of RAID 10 and RAID 5 volumes.  The instance has multiple DB's in it but 1 is far and away the largest @ 2TB allocated (about 1TB actual data).   The database in question is broken into many datafiles as follows:


Primary filegroup = 1 10GB datafile

Secondary filegroup = 8 x 100GB datafiles spread evenly over 4 seperate volumes for I/O performance.

Partitioning filegroups = many many files used for a partitioned table (file sizes ranging from 1.5GB to 20GB)

Again, my issue is the following.   This 2TB database takes close to an hour to startup everytime the instance of SQL Server restarts, failsover etc...  

Couple of notes:

1) We have instant file initialization configured

2) Recovery model = simple

3) Recovery interval = 0

4) There are no active large transactions running when the instance goes down


Database is "in recovery" for 3 hours



I booted a server today and it stuck in recovery state. It is SQL2005.  It is 10TB database. Error log for SQL Server doesn't show the progress. I only found "Starting up database 'MyDB'" .  How may I check what is going on.

-- this query shows percent completed 0 for all processes

select der.session_id, der.command, der.

Is a database online when it is changed from full to simple recovery model?



We have some databases that occaisally have very large ldf files and I have learned that the best way to shrink the ldf file is to change the backup mode to simple, shrink the log file, convert it back to full and do a full database backup so that the log file backups will run again. My question is during this process is the database available to the end users? When you guys do this do you feel it is nessesary to let the end users know or will they not notice what is going on?



Thanks! Kevin

Simple recovery database bloating?

I have a several hundred GB simple recovery dev database bloats by 200-300 GB and does not release the free space to the OS. Sometimes drive runs out of space because of this. I had to shrink database every few months to bring back database to its size and release the space back to OS. Database has autogrow turned on.  Question is what can I do on a dailhy/weekly basis to prevent the database to grow that big so that I do not run out of space on the drive? What makes this grow and not release?

!!!Urgent !!!!!Cannot recovery database without log files (SQL 2008)

I only have single mdf file, the transaction log file was corrupted. When I was trying to attatch the mdf file, I received the following error message. Can someone give me a help..... it's really urgent!

TITLE: Microsoft SQL Server Management Studio

Attach database failed for Server 'DEV8\sqlexpress2008'.  (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.1600.22+((SQL_PreRelease).080709-1414+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476


An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)


Could not open new database 'Smartadmin'. CREATE DATABASE is aborted.
File activation failure. The physical file name "C:\MSSQL\Smartadmin_log.ldf" may be incorrect.
The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure. (Microsoft SQL Serve

Recovery of database is 0%% complete (approximately 1868894 seconds remain).


One of our users left a stored proc running Friday night, the proc was inserting records into a table. The transaction log filled up the drive and is now 2.2 gig. Monday AM the proc was killed and started rolling back. The database is now in recovery mode and "approximately 1868894 seconds remain" and is unusable.

How can I get the database back to a usable state in the shortest amount of time?

Point In Time Recovery of Database with Full-Text Search enabled SQL 2005


Is this possible? 



Backup made at 10:30

changes made in system

Tail of the log backup taken at 10:35

Restore to point in tim 10:33 

At this point I get the error System.data.sqlclient.sqlerror: The File "sysft_yadayada_1" was not fully restored by a database or file restore.  The entire file must be successfully restored before applying this backup set.

BUT when I restore the database to most recent possible I have no issues..

when I do a select * from sys.master_files    sysft_yadayada_1 does not exist in that list, but  sysft_yadayada_5 does... When we were initially configuring fulltext search for this application we had a couple hiccups first till we got it working and thats the filename that ended up sticking.


Multiple database support with Entity Framework

One of the features introduced in Entity Framework is being database independent. Which mean each database provider can support Entity Framework by implementing its provider.

This feature allows you build applications independent from the underplaying database provider. In this post I'm going to show how to build an application that support multiple databases using same conceptual model made by Entity Framework.

Export Visio Database Table Names to Excel

If you use the Enterprise Architect edition of Microsoft Visio for data modeling regularly, then there is a good chance that at some point you've wanted to export just the table names into Excel. You might want to do this to map logical ERD entities to physical data model tables, track project status by entity, or track overlap between database versions.

MS SQL Server: Disconnect Users From Database - Kill User Session

If you ever wanted to restore your database from a SQL backup file (.bak), but there are still users connected to your database, the backup operation will fail causing the error: Exclusive access could not be obtained because the database is in use.
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend