Recover the database during undo phase

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Recover the database during undo phase

Author

Topic

shan
Yak Posting Veteran

84 Posts

Posted - 2012-07-31 : 15:29:02

I am trying to restore a huge database in the lower environment from prod backup,

When I execute the below query It indicates the restore is complete

SELECT percent_complete,* FROM sys.dm_exec_requests WHERE session_id = 57

percent_complete =100

But the Database is still in "Restoring" - State, When started the restore command I did execute the restore command "with recovery" and when i run the below

sp_who2 active

I can see one active restore spid, I belive the database is in undo \ redo phase.

My first question is
How do i confirm if its in undo \ redo phase.

My second question is ,

Can i Recover the database manually by issuing

RESTORE DATABASE dbname WITH RECOVERY

As I am not concerned about open \ uncommited transactions, If I do that will the database will result in any corruption or in unusuable state.

Any advice is appreciated.

-Shan

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-07-31 : 15:50:02

You can't stop the undo/redo phase. While it wouldn't cause physical corruption, it could possibly cause metadata or transactional inconsistencies that prevent data from being accessed.

If you only need to read from the database you can try RESTORE WITH STANDBY. This is similar to WITH NORECOVERY but permits read-only access.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-07-31 : 15:51:39

See how many VLFs you have in production. Run DBCC LOGINFO for that database and let us know how many rows that returns. If it's more than say 1000, the culprit is those VLFs.

You can't skip the recovery process. And no running that command won't work.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

shan
Yak Posting Veteran

84 Posts

Posted - 2012-07-31 : 16:04:43

Ok thanks you both. Standby wont help and will lead to more confusion among many users who are using.

Tara - This is in norecovery, How will i issue DBCC against this db?
Pls advise.

-Shan

shan
Yak Posting Veteran

84 Posts

Posted - 2012-07-31 : 16:07:43

Also is there a way to determine the how far it needs to apply undo \ redo from logs?

By the way this is restore of full backup only but this db have huge \big transactions in ldf.

-Shan

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-07-31 : 16:13:42

quote:
Originally posted by shan

Ok thanks you both. Standby wont help and will lead to more confusion among many users who are using.

Tara - This is in norecovery, How will i issue DBCC against this db?
Pls advise.

No, run it in production.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-07-31 : 16:14:34

How big is the database? How big is the MDF and the LDF? Have you checked for I/O latency? Are you using backup compression?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

shan
Yak Posting Veteran

84 Posts

Posted - 2012-07-31 : 16:18:29

Yes DBCC LOGINFO returns in prod with 18421.

But the full backup is 5 days old not todays...

Can you pls help me understand what does it mean and how do we know where are we at since DBCC LOGINFO returned > 1000.

Thanks for your quick reply.

-Shan

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-07-31 : 16:23:05

You need to fix production as that many VLFs is contributing to this problem and will mean longer crash recovery time when SQL restarts: http://www.sqlskills.com/BLOGS/KIMBERLY/post/Transaction-Log-VLFs-too-many-or-too-few.aspx

To fix it, you would shrink down to say 1GB and then expand it back out to the size that you need. Do it in chunks though, maybe 5GB chunks.

I realize your current restore won't be able to take advantage of the fewer VLFs when you fix production, however that many VLFs is a huge problem for production as well as any environment where you need to restore it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-07-31 : 16:23:46

And fix your autoincrement size in production too! I use 512MB or 1024GB for the transaction log typically. I typically use a larger size for the data file(s), but it depends on projected growth/size and instant file initialization.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

shan
Yak Posting Veteran

84 Posts

Posted - 2012-07-31 : 16:28:26

Thank you Tara for your expert advise.

-Shan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources