.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

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

Posted By:      Posted Date: May 22, 2011    Points: 0   Category :

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?

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

Database in recovery


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?


AJAX Auto Complete Extender with Database


Hi Guys

I have decided to use the Ajax Auto complete extender with a database, i found some cool articles on google to do this, but i cannot get it to work, I am trying a simple example where i have 1 textbox and i am trying to obtain products from the products table in the northwind database, heres my code (its not working)

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %>

<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="cc1" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <form id="form1" runat="server">
        <asp:ScriptManager ID="ScriptManager1" runat="server">
        <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
    <cc1:AutoCompleteExtender ID="AutoCompleteExtender1" runat="server" MinimumPrefixLength=&quo

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

Access denied on complete content database?


Hello, I have a strange problem. After some upgrading and windows updates, my sites can't be accessed any more. I get an access denied, no matter what account I use. All system / farm admin / site collection administrator accounts are locked out. I can however, access Central Administration. When I create a new site with the same account as site admin, I can access that site perfectly. It seems only the sites in that single content database are affected, and I have no idea why.

As far as I can see, there are no ULS or event log messages to go on. The site collection also isn't locked.

What could be the culprit?

MCTS in Web Application Development in .NET 2.0

Complete Database Export/Import


I have a question I am hoping someone can answer in a nutshell.  I work at a small company that has a 75GB SQL Server 2000 DB.  Data is written to and deleted from many of the 500 tables it contains.  Some of the largest tables contain over 21 million rows.  It is believed that this databse has never had any maintanence performed, such as index defrags or compaction of any sort.  I will admit I am more of a systems engineer and not a DBA, but my questions are as follows:

My supervisor believes that if we export/import the entire database (or copy and re-write?), then the data, and indexes will be re-written in a contiguous format and this will speed up read/write access.

I believe, that even if there is a way to export/import the data, or to recreate the DB schema and then basically re-create the DB, that this will not impove the performance or re-organize the indexes.  I suggested that we use DBCC DBREINDEX as a starting point since it has never had best practices implemented.

Any suggestions?



I need complete tutorial about MSSQL 2008 Database mirroring for load balancing


I need complete tutorial about MSSQL 2008 Database mirroring for load balancing

i want to load balance my SQL workload

all database insert update and delete queries will be mirrored

all select queries will be load balanced

sql servers will run at windows server 2008r2 at different computers (VPS)

Browser based Pokemon MMORPG Pokemon Craft Developer Used asp.net 4.0 routing at it's Pokedex

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
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