Database in Recovery

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server Newbies » Database in Recovery

Database in Recovery

Rate Topic

Display Mode

Topic Options

Author

Message

SQLSACT

Posted Friday, January 11, 2013 3:59 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 6:21 AM
Points: 1,191, Visits: 2,116

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

GilaMonster

Posted Friday, January 11, 2013 4:05 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:46 AM
Points: 37,712, Visits: 29,966

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

SQLSACT

Posted Friday, January 11, 2013 4:12 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 6:21 AM
Points: 1,191, Visits: 2,116

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

GilaMonster

Posted Friday, January 11, 2013 4:18 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:46 AM
Points: 37,712, Visits: 29,966

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

SQLSACT

Posted Friday, January 11, 2013 4:21 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 6:21 AM
Points: 1,191, Visits: 2,116

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

Andrew Pruski

Posted Tuesday, January 29, 2013 6:40 AM

SSC Eights!

Group: General Forum Members
Last Login: Today @ 4:32 AM
Points: 841, Visits: 499

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 »

Permissions