Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Database in Recovery Expand / Collapse
Author
Message
Posted Friday, January 11, 2013 3:59 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:20 AM
Points: 1,375, Visits: 2,661
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
Post #1405871
Posted Friday, January 11, 2013 4:05 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:14 AM
Points: 42,460, Visits: 35,520
Yes, you could have. The DB was online and available.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1405874
Posted Friday, January 11, 2013 4:12 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:20 AM
Points: 1,375, Visits: 2,661
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
Post #1405880
Posted Friday, January 11, 2013 4:18 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:14 AM
Points: 42,460, Visits: 35,520
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1405885
Posted Friday, January 11, 2013 4:21 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:20 AM
Points: 1,375, Visits: 2,661
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
Post #1405889
Posted Tuesday, January 29, 2013 6:40 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 7:05 AM
Points: 1,138, Visits: 809
Hi,

If you're trying to avoid log growth you could pre-size the transaction log to avoid auto-growth operations.

Andrew
Post #1413011
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse