January 11, 2013 at 3:59 am
Hi All
I had a situation where my SQL Server was stopped in the middle of a long running transaction, I knew that when SQL Server came up, there would be a substantial wait before the database became available again.
When SQL Server came up, the database was in a recovery state. I checked the logs and it showed "recovery of database.... Phase 2 of 3"
What confused me is that after phase 2 of 3, the database went into and Online State but the logs showed "recovery of database.... Phase 3 of 3"
I allowed phase 3 to complete before starting the application that uses the database
My question is, could I have started using the database during the 3rd phase already, was it unnecessary to have waited for phase 3 to complete?
Thanks
January 11, 2013 at 4:05 am
Yes, you could have. The DB was online and available.
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
January 11, 2013 at 4:12 am
GilaMonster (1/11/2013)
Yes, you could have. The DB was online and available.
Thanks, would you recommend it though?
Am I right in that the 3rd phase (Undo) is where the uncommitted transactions are being rolled back?
At the start of Phase 3, I noted that the log was 99% full, at about 70% of the 3rd phase, the usage went down to about 20% and after recovery, it was 1%.
I am assuming that if I had started the application, the Log would have grown?
Thanks
January 11, 2013 at 4:18 am
SQLSACT (1/11/2013)
GilaMonster (1/11/2013)
Yes, you could have. The DB was online and available.Thanks, would you recommend it though?
Yes, why not?
Am I right in that the 3rd phase (Undo) is where the uncommitted transactions are being rolled back?
Yes
I am assuming that if I had started the application, the Log would have grown?
Maybe. Maybe not. Does the log usually grow when you start the app?
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
January 11, 2013 at 4:21 am
At startup there a quite a few inserts that run.
I wasn't sure if that would get the log to it's tipping point and then subsequently grow. Was really trying to avoid log growth.
Thanks
January 29, 2013 at 6:40 am
Hi,
If you're trying to avoid log growth you could pre-size the transaction log to avoid auto-growth operations.
Andrew
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply