<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2008
»
SQL Server 2008 Administration
»
Database inrecovery
12 posts, Page 1 of 2
1
2
»»
Database inrecovery
Rate Topic
Display Mode
Topic Options
Author
Message
Ratheesh.K.Nair
Ratheesh.K.Nair
Posted Monday, October 22, 2012 7:44 AM
SSCrazy
Group: General Forum Members
Last Login: Yesterday @ 9:03 AM
Points: 2,415,
Visits: 3,374
Hi Experts,
One of our VLDB(3TB) is inrecovery state after server restart and from sql log i got the message 2% completed of phase one and approximately 53268 seconds remaining. Database is in SIMPLE recovery mode,is that a reason for this high recovery time??If yes then how?? Can anyone suggest some tips on reducing recovery time??
Thanks in Advance.
Post #1375429
GilaMonster
GilaMonster
Posted Monday, October 22, 2012 8:19 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 11:21 AM
Points: 37,686,
Visits: 29,943
Nope. Simple recovery is not the reason for the high recovery time. You either had huge uncommitted transactions when the DB shut down or you have severe log fragmentation or both.
Only thing you can do at this point is wait.
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 #1375447
Mac1986
Mac1986
Posted Monday, October 22, 2012 6:22 PM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Wednesday, May 15, 2013 2:33 AM
Points: 180,
Visits: 512
Agree with Master Gail Or if you do not need the Logs Just drop the database and then attached the data files with no ldf file. That way a fresh ldf file is created and you will not have any log history.
USE [master]
GO
EXEC sp_attach_single_file_db @dbname='TestDb',
@physname=N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TestDb.mdf'
GO
OR
CREATE DATABASE TestDb ON
(FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TestDb.mdf')
FOR ATTACH_REBUILD_LOG
GO
Post #1375795
mahesh.dasoni
mahesh.dasoni
Posted Tuesday, October 23, 2012 12:58 AM
SSC Journeyman
Group: General Forum Members
Last Login: Yesterday @ 7:41 AM
Points: 81,
Visits: 859
As suggested by Gail When the Server Restarted there must be some Transaction that might be in rollback state.It might take lot of time to recover and also consume lot of log space.
If u have lastest backup u can resore it from the backup.
Post #1375860
GilaMonster
GilaMonster
Posted Tuesday, October 23, 2012 2:17 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 11:21 AM
Points: 37,686,
Visits: 29,943
Mac1986 (10/22/2012)
Agree with Master Gail Or if you do not need the Logs Just drop the database and then attached the data files with no ldf file. That way a fresh ldf file is created and you will not have any log history.
That is absolutely horrid advice.
The database is in recovery, that means it was not cleanly shut down. If he were to do as you suggest, SQL would not be able to create a new log file (if can only do that when the database was cleanly shut down prior to the deletion) and the attach script you recommended will fail.
Hence, following your advice will turn this from an attached database in recovery to a database file that will not attach at all. Hardly an improvement.
(yes, it's possible to hack the DB back in and attempt an emergency mode repair. I say attempt, because it does not always succeed. Even if it does succeed, it will very likely leave the DB transactionally inconsistent, referentially inconsistent, potentially even structurally inconsistent)
Deleting a database's log file is a very good way of destroying that DB beyond any ability to recover (except via restoring backup)
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 #1375890
Ratheesh.K.Nair
Ratheesh.K.Nair
Posted Tuesday, October 23, 2012 4:51 AM
SSCrazy
Group: General Forum Members
Last Login: Yesterday @ 9:03 AM
Points: 2,415,
Visits: 3,374
Thanks Gail,
The DB got recovered.
In a blog i read that SIMPLE recovery mode can cause high recovery interval since its not possible to take log backups and taking frequent log backup will reduce the recovery time is that true??
Will frequent log backup reduces VLF's created??
Post #1375958
GilaMonster
GilaMonster
Posted Tuesday, October 23, 2012 5:03 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 11:21 AM
Points: 37,686,
Visits: 29,943
Ratheesh.K.Nair (10/23/2012)
In a blog i read that SIMPLE recovery mode can cause high recovery interval since its not possible to take log backups and taking frequent log backup will reduce the recovery time is that true??
No, as I said earlier, Simple recovery model is not in any way the cause of high recovery time. Log backups have nothing to do with recovery time. Recovery models have nothing to do with recovery time. Recovery time is a factor of the amount of operations that need to roll back (not committed) and the number of operations that have to roll forward (committed but not hardened to the data file). Neither of those is affected by recovery model.
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 #1375962
Ratheesh.K.Nair
Ratheesh.K.Nair
Posted Tuesday, October 23, 2012 5:33 AM
SSCrazy
Group: General Forum Members
Last Login: Yesterday @ 9:03 AM
Points: 2,415,
Visits: 3,374
Thanks a lot Gail..You are awesome..
Post #1375972
joeroshan
joeroshan
Posted Tuesday, October 23, 2012 5:39 AM
Mr or Mrs. 500
Group: General Forum Members
Last Login: Today @ 4:12 AM
Points: 573,
Visits: 1,165
Just to add to Gails' points,
in simple recovery model the inactive log is cleared on check point. So VLFs are affted by the file growth setting/ the your transaction durations/ recovery interval setting at server level.
-- Roshan Joe
*******************************************
Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help
Custom cleanup script for backups
Post #1375977
Ratheesh.K.Nair
Ratheesh.K.Nair
Posted Tuesday, October 23, 2012 6:20 AM
SSCrazy
Group: General Forum Members
Last Login: Yesterday @ 9:03 AM
Points: 2,415,
Visits: 3,374
Thanks Roshan,
So you meant to say SIMPLE Recovery model can cause VLF to grow???
Post #1375996
« Prev Topic
|
Next Topic »
12 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
