June 14, 2011 at 10:32 am
My suspicion is like Grant's. You are in the undo phase of recovery. I can't test right now, but I thought there were periodic messages, but can't find a reference to explain what is logged during recovery.
June 14, 2011 at 10:42 am
Does this give you anything? Not sure it will help, but curious
June 15, 2011 at 6:30 am
Guys.. fro mthe past 2 days the database is still in the recovery mode wihtout any message in the log, of course the db size is more than 2TB and just the log is around 900gb which is in simple recovery mode.
June 15, 2011 at 6:33 am
is there any other option i can do with the existing mdf file and want to get rid if the log file which is too big, i am not worried about losing any transactions that are in progress.
June 15, 2011 at 6:44 am
GilaMonster (6/14/2011)
Post the latest error log (you can disguise the names of the DBs, they're not important)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 15, 2011 at 6:45 am
Tara-1044200 (6/15/2011)
is there any other option i can do with the existing mdf file and want to get rid if the log file which is too big, i am not worried about losing any transactions that are in progress.
You should be worried about the transactions in progress. If you do anything to that log (like delete it), the database will NOT be able to come back online at all.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 15, 2011 at 7:18 am
Do what Gail said about posting the error log. And you still haven't answered Grant's question, that I can tell. Why was the server shut down?
You said you don't have enough disk space to recover that one DB (if I read the post correctly). How many DBs are on that disk? If just the one, you may be stuck (I don't think you can add more database files on other disks).
If you have other DBs, do you have another disk you can plug in and move those DB files off so this DB can use the space to finish recovering?
June 17, 2011 at 8:38 am
OK guys.. now i see the log saying database is receovring 11% not sure how long it would take, it is 2.5TB database.
Also i have observed that SQL server Agent is also not working which is giving an error "cannot perform this operation while sqlserveragent is starting" and when i looked in the SQLAGENT.OUT file it shows "? [393] Waiting for SQL Server to recover databases...". This does not makes senseto me, does the sql agent not work when there is problem with any of the database on the server?
June 17, 2011 at 8:43 am
There will be a time estimation after the %. It's usually on the low side.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 17, 2011 at 8:57 am
Gila, at this point i am more worried about sql agent as there are many jobs that has to kick off over the week end, its ok for me even the database takes 3-4 days to recover. I am trying to understand why sql agent is depending on the recovery of the database.
June 17, 2011 at 9:40 am
do you use DTC anywhere in the applications?
we had an issue before where there was a never terminating DTC transaction ongoing and it wouldn't terminate by killing the spid and it went into a -2 spid so trying to kill the UOW didnt work either.
we rebooted the server and the databases was stuck in recovery mode even though it hit 99% recovered, it just wouldn't do that last check.
we eventually terminated the DTC transaction by taking down SQL again and terminating the transaction via component servicers, rebooted SQL all was good.
June 17, 2011 at 11:15 am
Check the logs, see if there are any messages for MSDB. Check the state of MSDB. SQLAgent shouldn't depend on a user DB.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply