SQLServerCentral.com / Disaster Recovery / Database Design / Database in Recovery mode / Latest PostsInstantForum.NET v99.99.99SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 09 Dec 2016 18:07:23 GMT20RE: Database in Recovery modehttp://www.sqlservercentral.com/Forums/Topic1532531-384-1.aspx[quote][b]Michael L John (1/22/2014)[/b][hr][quote][b]Grant Fritchey (1/22/2014)[/b][hr][quote][b]SQLRNNR (1/22/2014)[/b][hr][quote][b]Grant Fritchey (1/22/2014)[/b][hr][quote][b]praneethydba (1/22/2014)[/b][hr]HI,I am happy to tell you, my database is up now.....Online now.....:-)Thanks a lot for your support...ThanksPraneeth[/quote]Congrats! That's great!Now, run a backup.[/quote]No, schedule regular backups and make sure they complete successfully.[/quote]Absolutely. Right after you take one manually out of shear paranoia.[/quote]Do all of the above, test the restore, and then get the developers out of the production databases![/quote]All very sound advice:-DWed, 22 Jan 2014 09:23:02 GMTSQLRNNRRE: Database in Recovery modehttp://www.sqlservercentral.com/Forums/Topic1532531-384-1.aspx[quote][b]Grant Fritchey (1/22/2014)[/b][hr][quote][b]SQLRNNR (1/22/2014)[/b][hr][quote][b]Grant Fritchey (1/22/2014)[/b][hr][quote][b]praneethydba (1/22/2014)[/b][hr]HI,I am happy to tell you, my database is up now.....Online now.....:-)Thanks a lot for your support...ThanksPraneeth[/quote]Congrats! That's great!Now, run a backup.[/quote]No, schedule regular backups and make sure they complete successfully.[/quote]Absolutely. Right after you take one manually out of shear paranoia.[/quote]Do all of the above, test the restore, and then get the developers out of the production databases!Wed, 22 Jan 2014 09:20:52 GMTMichael L JohnRE: Database in Recovery modehttp://www.sqlservercentral.com/Forums/Topic1532531-384-1.aspx[quote][b]SQLRNNR (1/22/2014)[/b][hr][quote][b]Grant Fritchey (1/22/2014)[/b][hr][quote][b]praneethydba (1/22/2014)[/b][hr]HI,I am happy to tell you, my database is up now.....Online now.....:-)Thanks a lot for your support...ThanksPraneeth[/quote]Congrats! That's great!Now, run a backup.[/quote]No, schedule regular backups and make sure they complete successfully.[/quote]Absolutely. Right after you take one manually out of shear paranoia.Wed, 22 Jan 2014 09:01:37 GMTGrant FritcheyRE: Database in Recovery modehttp://www.sqlservercentral.com/Forums/Topic1532531-384-1.aspxOk Sure..ThanksWed, 22 Jan 2014 08:45:36 GMTpraneethydbaRE: Database in Recovery modehttp://www.sqlservercentral.com/Forums/Topic1532531-384-1.aspx[quote][b]SQLRNNR (1/22/2014)[/b][hr][quote][b]Grant Fritchey (1/22/2014)[/b][hr][quote][b]praneethydba (1/22/2014)[/b][hr]HI,I am happy to tell you, my database is up now.....Online now.....:-)Thanks a lot for your support...ThanksPraneeth[/quote]Congrats! That's great!Now, run a backup.[/quote]No, schedule regular backups and make sure they complete successfully.[/quote]Also get training resources for yourself and the developer that wrote the code to start a 24 hour transaction that never completed :-DWed, 22 Jan 2014 08:27:49 GMTKeith TateRE: Database in Recovery modehttp://www.sqlservercentral.com/Forums/Topic1532531-384-1.aspxGood news!I would document the incident, including timelines. The time that the database was offline has an associated cost. That is what you need to use when someone says that they can not afford hardware resources for backups.Backups are insurance - you have a risk (losing data). To reduce that risk, you pay a premium (the costs of setting up the backups). And you hope that you never make a claim (database failure) but you sleep better at night knowing that you have managed the risk.Good luck!Wed, 22 Jan 2014 08:25:52 GMTtim_harkinRE: Database in Recovery modehttp://www.sqlservercentral.com/Forums/Topic1532531-384-1.aspx[quote][b]Grant Fritchey (1/22/2014)[/b][hr][quote][b]praneethydba (1/22/2014)[/b][hr]HI,I am happy to tell you, my database is up now.....Online now.....:-)Thanks a lot for your support...ThanksPraneeth[/quote]Congrats! That's great!Now, run a backup.[/quote]No, schedule regular backups and make sure they complete successfully.Wed, 22 Jan 2014 08:21:28 GMTSQLRNNRRE: Database in Recovery modehttp://www.sqlservercentral.com/Forums/Topic1532531-384-1.aspx[quote][b]praneethydba (1/22/2014)[/b][hr]HI,I am happy to tell you, my database is up now.....Online now.....:-)Thanks a lot for your support...ThanksPraneeth[/quote]Congrats! That's great!Now, run a backup.Wed, 22 Jan 2014 08:19:00 GMTGrant FritcheyRE: Database in Recovery modehttp://www.sqlservercentral.com/Forums/Topic1532531-384-1.aspx:-) i am really tensed of my Job...I got my database and job back :-)Wed, 22 Jan 2014 07:33:29 GMTpraneethydbaRE: Database in Recovery modehttp://www.sqlservercentral.com/Forums/Topic1532531-384-1.aspxThat's good news. Glad it is working for you now.Wed, 22 Jan 2014 07:27:17 GMTSQLRNNRRE: Database in Recovery modehttp://www.sqlservercentral.com/Forums/Topic1532531-384-1.aspxHI,I am happy to tell you, my database is up now.....Online now.....:-)Thanks a lot for your support...ThanksPraneethWed, 22 Jan 2014 07:25:16 GMTpraneethydbaRE: Database in Recovery modehttp://www.sqlservercentral.com/Forums/Topic1532531-384-1.aspxWhat is the output of this code (change the database name):[code="sql"]SELECT state_descFROM sys.databaseswhere name = 'DatabaseName'[/code]Tue, 21 Jan 2014 09:44:35 GMTKeith TateRE: Database in Recovery modehttp://www.sqlservercentral.com/Forums/Topic1532531-384-1.aspxThe status is RECOVERING? Not anything else?Post the error log?Tue, 21 Jan 2014 09:44:33 GMTGilaMonsterRE: Database in Recovery modehttp://www.sqlservercentral.com/Forums/Topic1532531-384-1.aspxHI,Unfortunately, no records are shown as output...ThanksPraneethTue, 21 Jan 2014 09:39:50 GMTpraneethydbaRE: Database in Recovery modehttp://www.sqlservercentral.com/Forums/Topic1532531-384-1.aspx[quote][b]I will let you the status of my database once it is up.[/quote] Has your database finished recovery? If not, what does the estimate (see Jason's post) say? and has it been changing (hopefully going down)?Tue, 21 Jan 2014 09:21:05 GMTKeith TateRE: Database in Recovery modehttp://www.sqlservercentral.com/Forums/Topic1532531-384-1.aspx[quote][b]GilaMonster (1/21/2014)[/b][hr][quote][b]SQLRNNR (1/21/2014)[/b][hr][quote][b]GilaMonster (1/21/2014)[/b][hr][quote][b]praneethydba (1/21/2014)[/b][hr]Still in Recovery. I am Panic.[/quote]Be patient.The progress gets logged to the error log, check there for estimated time remaining, but note it's often on the low side[/quote]Here is an article with a query to check the recovery process.[url]http://jasonbrimhall.info/2012/06/04/database-in-recovery/[/url]This query can likely help ease some of the anxiety.[/quote]I've seen the error log figure count down to about 10 minutes, then jump up to a few hours again. It's not reliable, it's an estimate.[/quote]Yeah, but getting an estimate is better than not knowing anything, and knowing is half the battle :-DTue, 21 Jan 2014 08:41:38 GMTSQLRNNRRE: Database in Recovery modehttp://www.sqlservercentral.com/Forums/Topic1532531-384-1.aspx[quote][b]SQLRNNR (1/21/2014)[/b][hr][quote][b]GilaMonster (1/21/2014)[/b][hr][quote][b]praneethydba (1/21/2014)[/b][hr]Still in Recovery. I am Panic.[/quote]Be patient.The progress gets logged to the error log, check there for estimated time remaining, but note it's often on the low side[/quote]Here is an article with a query to check the recovery process.[url]http://jasonbrimhall.info/2012/06/04/database-in-recovery/[/url]This query can likely help ease some of the anxiety.[/quote]I've seen the error log figure count down to about 10 minutes, then jump up to a few hours again. It's not reliable, it's an estimate.Tue, 21 Jan 2014 08:39:08 GMTGilaMonsterRE: Database in Recovery modehttp://www.sqlservercentral.com/Forums/Topic1532531-384-1.aspx[quote][b]GilaMonster (1/21/2014)[/b][hr][quote][b]praneethydba (1/21/2014)[/b][hr]Still in Recovery. I am Panic.[/quote]Be patient.The progress gets logged to the error log, check there for estimated time remaining, but note it's often on the low side[/quote]Here is an article with a query to check the recovery process.[url]http://jasonbrimhall.info/2012/06/04/database-in-recovery/[/url]This query can likely help ease some of the anxiety.Tue, 21 Jan 2014 08:31:17 GMTSQLRNNRRE: Database in Recovery modehttp://www.sqlservercentral.com/Forums/Topic1532531-384-1.aspxOk thanks a lotTue, 21 Jan 2014 07:53:28 GMTpraneethydbaRE: Database in Recovery modehttp://www.sqlservercentral.com/Forums/Topic1532531-384-1.aspxGood to see your book is in O'Reiley, Grant! I will have to give it a read. Hard to find good DB books here in Norway in English and it costs a fortune to ship it. http://my.safaribooksonline.com/book/-/9781430242031?bookview=overviewTue, 21 Jan 2014 07:45:14 GMTdavid.john.gilliganRE: Database in Recovery modehttp://www.sqlservercentral.com/Forums/Topic1532531-384-1.aspxPluralsight offers (or did when I joined) a free 30 days for video trial courses....they just bought another company (forget the name) and there are now more for the "Pro" membership. I have found both worth the money (I pay it myself) as I do some IT stuff for fun on the side. They have a 8 hour course on "SQL Server: Logging, Recovery, and the Transaction Log" for example....good stuff....Tue, 21 Jan 2014 07:41:47 GMTdavid.john.gilliganRE: Database in Recovery modehttp://www.sqlservercentral.com/Forums/Topic1532531-384-1.aspxSince you are completely on your own on this, again, I suggest getting a copy of my book. Links are down in my signature.After you get backups in place... that has to be priority #1.Tue, 21 Jan 2014 07:40:48 GMTGrant FritcheyRE: Database in Recovery modehttp://www.sqlservercentral.com/Forums/Topic1532531-384-1.aspxThanks a lot Gail Shaw..I am very grateful to you.. By the way I am a junior DBA and my Senior DBA resigned. So I am the one has to handle everything.I will let you the status of my database once it is up. In the mean while i will see the SQL training sites..ThanksPraneethTue, 21 Jan 2014 07:34:58 GMTpraneethydbaRE: Database in Recovery modehttp://www.sqlservercentral.com/Forums/Topic1532531-384-1.aspx[b][/b]I would recommend, among other free sites like here, that you pay for good training ones like pluralsight and O'Reiley books on-line. I saw, for example, they have over 55 training sessions on different aspects (and versions) of SQL Server. And no, I don't own stock in any of them :crying:Tue, 21 Jan 2014 07:29:31 GMTdavid.john.gilliganRE: Database in Recovery modehttp://www.sqlservercentral.com/Forums/Topic1532531-384-1.aspxOk Sure... After this Incident my boss asked me to configure the backup startegy and DR setup aswell...ThanksPraneethTue, 21 Jan 2014 07:29:01 GMTpraneethydbaRE: Database in Recovery modehttp://www.sqlservercentral.com/Forums/Topic1532531-384-1.aspxDon't even think about partitioning. If you do not have backup, partitioning is way over your head (not trying to know you, but if you have no backups, indexes, etc., partitioning is the least of your problems).Once the dust settles, get a valid backup strategy in place. DBAs get fired for not having valid backups. If your company says they can not afford the space\resources to backup the data, then fight them. Get it in writing all the way up to the CIO level. Make sure that they know, without ANY doubt, that one bad disk sector can take out the entire database. And disks fail, or sometimes hiccup to the point where a database can be corrupted.Take a look here: http://www.brentozar.com/needs/Tue, 21 Jan 2014 07:24:12 GMTtim_harkinRE: Database in Recovery modehttp://www.sqlservercentral.com/Forums/Topic1532531-384-1.aspx[quote][b]praneethydba (1/21/2014)[/b][hr]They hired me to do this job. As I am in the beginning of my career. I am not sure, from where to start and what to find.[/quote]Do they know that, or do they think you're an experienced DBA?If the former, tell them you don't know where to start or what to look for and that they should get an experienced consultant in to do an analysis and recommendations. If the latter, well....Tue, 21 Jan 2014 07:15:45 GMTGilaMonsterRE: Database in Recovery modehttp://www.sqlservercentral.com/Forums/Topic1532531-384-1.aspxHI Gail ShawThey hired me to do this job. As I am in the beginning of my career. I am not sure, from where to start and what to find. But I dont want to give up. I am seeking help from you guys, where I will get some tips on performance tuning and where I can build myself on this project..Can you please share me the information on partitions, tuning etc...ThanksPraneethTue, 21 Jan 2014 07:13:07 GMTpraneethydbaRE: Database in Recovery modehttp://www.sqlservercentral.com/Forums/Topic1532531-384-1.aspx[quote][b]praneethydba (1/21/2014)[/b][hr]HI,Still in Recovery. I am Panic.My company is running this database from past 8 years and no indexing, no Primary/clustered index, No jobs etc.So I want to streamline all this database. I studied on net to improve performance we can do table partition for fast retrieval of data.Can you please let me know the link or site Any additional tips are highly appreciated!!!ThanksPraneeth[/quote]Partitioning is not really a performance tuning mechanism. It's primarily a data management mechanism that can have performance improvements. I agree with Gail, get a professional in to do an assessment. Barring that, get a copy of my book. At least you'll have a functional starting point for figuring this stuff out.Tue, 21 Jan 2014 07:09:27 GMTGrant FritcheyRE: Database in Recovery modehttp://www.sqlservercentral.com/Forums/Topic1532531-384-1.aspx[quote][b]praneethydba (1/21/2014)[/b][hr]Still in Recovery. I am Panic.[/quote]Be patient.The progress gets logged to the error log, check there for estimated time remaining, but note it's often on the low side[quote]My company is running this database from past 8 years and no indexing, no Primary/clustered index, No jobs etc.[/quote]Sounds like you need to get someone in to evaluate and make recommendations. If it's that much of a mess, it's going to be a huge job just figuring out where to start.[quote]I studied on net to improve performance we can do table partition for fast retrieval of data.[/quote]Um, no.[url]https://www.simple-talk.com/sql/database-administration/gail-shaws-sql-server-howlers/[/url]Tue, 21 Jan 2014 06:04:50 GMTGilaMonsterRE: Database in Recovery modehttp://www.sqlservercentral.com/Forums/Topic1532531-384-1.aspxHI,Still in Recovery. I am Panic.My company is running this database from past 8 years and no indexing, no Primary/clustered index, No jobs etc.So I want to streamline all this database. I studied on net to improve performance we can do table partition for fast retrieval of data.Can you please let me know the link or site Any additional tips are highly appreciated!!!ThanksPraneethTue, 21 Jan 2014 05:57:50 GMTpraneethydbaRE: Database in Recovery modehttp://www.sqlservercentral.com/Forums/Topic1532531-384-1.aspx[quote][b]praneethydba (1/20/2014)[/b][hr]HI,As the database is very big and no place on drive. we stopped taking backup's.Can you please provide me the solution..ThanksPraneeth[/quote]The best backups are not stored on the same drive, IMHO. And with the cost of space these days........Tue, 21 Jan 2014 05:18:03 GMTdavid.john.gilliganRE: Database in Recovery modehttp://www.sqlservercentral.com/Forums/Topic1532531-384-1.aspxWhile waiting for it to come online, have you checked the disk system? Have you checked general server health? Have you checked permissions (make sure they haven't changed)?Your recovery process could take a very long time based on the info provided. While that is happening, you might as well do everything possible to ensure there is nothing else causing delays or problems.Mon, 20 Jan 2014 08:53:35 GMTSQLRNNRRE: Database in Recovery modehttp://www.sqlservercentral.com/Forums/Topic1532531-384-1.aspxHopefully your boss will see the urgency of taking backups now!Mon, 20 Jan 2014 08:51:37 GMTBeatrix KiddoRE: Database in Recovery modehttp://www.sqlservercentral.com/Forums/Topic1532531-384-1.aspxNo... Not yet.Its still recovery. I told the scenario to my boss. Just we are all waiting for the database to be online.ThanksPraneethMon, 20 Jan 2014 08:49:17 GMTpraneethydbaRE: Database in Recovery modehttp://www.sqlservercentral.com/Forums/Topic1532531-384-1.aspxIs your database back online?Mon, 20 Jan 2014 08:33:23 GMTSQLRNNRRE: Database in Recovery modehttp://www.sqlservercentral.com/Forums/Topic1532531-384-1.aspxWait until recovery finishes!There is no alternate solution. There is no magic 'instantly finish recovery' button. Be patient and wait for the recovery to complete, then your DB will come online. If you restart SQL again all you're going to do is force SQL to start recovery over from the beginning, hence wasting the last 8 or so hours.Wait. And while you're waiting, sort out your backup strategy.Mon, 20 Jan 2014 02:54:39 GMTGilaMonsterRE: Database in Recovery modehttp://www.sqlservercentral.com/Forums/Topic1532531-384-1.aspxHI ,Thanks for the reply..I totally agree with you. We have to take db backup. there is an Impact on the business as week starts from today. My concern is will it be back online or will be go to suspect/damage of my database.I will wait for 3 more hours, but if i come online well and good, if not is there any other alternative solution inorder to bring back my database usable i.e. online.Its confirmed that my database will go turn to suspect mode or damaged.ThanksPraneethMon, 20 Jan 2014 02:49:15 GMTpraneethydbaRE: Database in Recovery modehttp://www.sqlservercentral.com/Forums/Topic1532531-384-1.aspx[quote][b]praneethydba (1/20/2014)[/b][hr]As the database is very big and no place on drive. we stopped taking backup's.[/quote]So what do you expect to do when something goes seriously wrong and the DB is lost or damaged? Or is the entire of that very big database easily replaceable without any business impact?Mon, 20 Jan 2014 02:44:10 GMTGilaMonsterRE: Database in Recovery modehttp://www.sqlservercentral.com/Forums/Topic1532531-384-1.aspxWait.There is nothing else you can do, particularly since you have no backup. You have to wait until recovery is complete.When you restarted the server all you did was force SQL to do the rollback of the operation after the restart, this time with the DB unavailable to anyone. It usually takes longer to roll back than the initial operation took, so be patient and don't restart it again, you'll just make it start over.Mon, 20 Jan 2014 02:40:29 GMTGilaMonster