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 ««12345»»»

Database stuck "in recovery" Expand / Collapse
Author
Message
Posted Tuesday, June 22, 2010 12:20 PM


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 @ 5:50 AM
Points: 40,209, Visits: 36,618
yessen (6/22/2010)
Is there a good way to maintain .ldf size at certain size? I tried to set the MAXSIZE = 10gb but when it reached that size my database could no longer make transactions and I had to get rid of that limit by setting it back to -1. All I am trying to do to set it to certain size and still continue writing the most recent transactions.

Please read through the article I referenced.

To keep the log from growing, you need regular log backups. Don't limit the size of the ldf, you'll cause problems if you underestimate how big it needs to be.

When I do "full database backup or differential database backup", does that shrinks the .ldf size?

No.



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 #941273
Posted Wednesday, June 23, 2010 7:24 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, November 7, 2010 3:18 PM
Points: 19, Visits: 60
GilaMonster (6/22/2010)
yessen (6/22/2010)
Is there a good way to maintain .ldf size at certain size? I tried to set the MAXSIZE = 10gb but when it reached that size my database could no longer make transactions and I had to get rid of that limit by setting it back to -1. All I am trying to do to set it to certain size and still continue writing the most recent transactions.

Please read through the article I referenced.

To keep the log from growing, you need regular log backups. Don't limit the size of the ldf, you'll cause problems if you underestimate how big it needs to be.

When I do "full database backup or differential database backup", does that shrinks the .ldf size?

No.


I ran the full backup on two servers that were in the "recovery mode" for 30 minutes each everytime I restarted my SQL Server and then switched to simple logging. Then I tried restarting the SQL server and they still were stuck in "recovery mode" for 30 minutes each.

I thought making the "full backup" should have restarted the "log chain" :/ please correct me if I am wrong.
Post #941727
Posted Wednesday, June 23, 2010 7:39 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 @ 5:50 AM
Points: 40,209, Visits: 36,618
You're confusing concepts. Log chain has to do with whether you can do point in time restores (restore a database to any time) using RESTORE DATABASE and RESTORE LOG.

Recovery (restart recovery) is a process that all databases have to go through on startup, regardless of recovery model or backups. It's to bring the database to a consistent state.

As I mentioned earlier
It's called restart recovery and it's required to ensure that the databases are all transactionally consistent. It shouldn't take long. If it does then either your log is badly configured (too many VLFs) or there's IO bottleneck problems on the log and/or data drive, or there's something else hindering checkpoints or recovery.

Have you messed with the recovery interval setting? (sp_configure)



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 #941746
Posted Thursday, June 24, 2010 12:27 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, November 7, 2010 3:18 PM
Points: 19, Visits: 60
GilaMonster (6/23/2010)
You're confusing concepts. Log chain has to do with whether you can do point in time restores (restore a database to any time) using RESTORE DATABASE and RESTORE LOG.

Recovery (restart recovery) is a process that all databases have to go through on startup, regardless of recovery model or backups. It's to bring the database to a consistent state.

As I mentioned earlier
It's called restart recovery and it's required to ensure that the databases are all transactionally consistent. It shouldn't take long. If it does then either your log is badly configured (too many VLFs) or there's IO bottleneck problems on the log and/or data drive, or there's something else hindering checkpoints or recovery.

Have you messed with the recovery interval setting? (sp_configure)


so in order to to make my database not stuck "in recovery mode" every time I restart my server, I need to back up my database and restore it from the same .bak file. This will restart my log chain and "recovery mode" should be quick after that. Am I right?
Post #942632
Posted Thursday, June 24, 2010 5:06 PM


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 @ 5:50 AM
Points: 40,209, Visits: 36,618
No. Log chain and restart recovery are UNRELATED.

An intact log chain gives you the ability to restore a database to a particular point in time, in the case of a disaster (eg a disk failure)
Restart recovery (databases 'in recovery') will run every time a database starts up and it cannot be prevented. It's essential for SQL to run it always.

They are totally different concepts, totally different reasons, totally different operations.

There are several reasons why you may have a slow restart recovery. None are linked to backups.



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 #942759
Posted Thursday, June 24, 2010 5:28 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 8:18 PM
Points: 17,824, Visits: 15,756
Gail gave several reasons that could cause a slow restart recovery. One of those is the presence of a lot of VLFs. Go check out the various things that Gail listed in that post concerning restart recovery. That should help you understand what is happening. It is an imperative process that happens on every database. Some may be slower than others - and that is sometimes due to the list of causes provided by Gail. A lot of the restart recovery slowness comes down to proper setup of your database. Check for VLFs (if you have a lot of them, this could be your problem) - read the articles by Kimberly Tripp on the subject (one is referenced in my sig). Check your checkpoint settings.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #942767
Posted Friday, June 25, 2010 5:49 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 6:53 AM
Points: 13,890, Visits: 28,285
yessen (6/24/2010)
GilaMonster (6/23/2010)
You're confusing concepts. Log chain has to do with whether you can do point in time restores (restore a database to any time) using RESTORE DATABASE and RESTORE LOG.

Recovery (restart recovery) is a process that all databases have to go through on startup, regardless of recovery model or backups. It's to bring the database to a consistent state.

As I mentioned earlier
It's called restart recovery and it's required to ensure that the databases are all transactionally consistent. It shouldn't take long. If it does then either your log is badly configured (too many VLFs) or there's IO bottleneck problems on the log and/or data drive, or there's something else hindering checkpoints or recovery.

Have you messed with the recovery interval setting? (sp_configure)


so in order to to make my database not stuck "in recovery mode" every time I restart my server, I need to back up my database and restore it from the same .bak file. This will restart my log chain and "recovery mode" should be quick after that. Am I right?


You have two issues going on here. First, you need to get your databases and your backups configured correctly. Second, why are you restarting SQL Server over and over again? The majority of our servers are restarted maybe two-three times a year. It really sounds like something is going a bit off with your systems if you are restarting them so often.

As to the databases and the backups, it sounds like you're almost there. You have the concept of point in time recovery down. Now you need to implement the log backups, which are completely seperate from, though dependent on, the full backups. You'll need to schedule the log backups so that they run multiple times a day. Not knowing your systems or the business needs, I can't suggest a realistic number, so, as a SWAG, start at every 30 minutes. You may need to adjust that down to run the backups more frequently, but it's really dependent on your needs and your systems. Once you have the log backups running, I think you'll see that your log files don't need to be 300gb or more in size, but, it really depends on the number and size of the transactions in your system. Again, I'm not there, so I can't be sure, 300gb could be the amount of transactions you get within 30 minutes. Regardless, once you get the transaction backup in place, you should be able to determine roughly how much space you need to maintain the log and you can shrink the file down to something near that number, but larger to allow for surges & growing volume.



----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #942968
Posted Friday, June 25, 2010 7:40 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, November 7, 2010 3:18 PM
Points: 19, Visits: 60
Grant Fritchey (6/25/2010)
yessen (6/24/2010)
GilaMonster (6/23/2010)
You're confusing concepts. Log chain has to do with whether you can do point in time restores (restore a database to any time) using RESTORE DATABASE and RESTORE LOG.

Recovery (restart recovery) is a process that all databases have to go through on startup, regardless of recovery model or backups. It's to bring the database to a consistent state.

As I mentioned earlier
It's called restart recovery and it's required to ensure that the databases are all transactionally consistent. It shouldn't take long. If it does then either your log is badly configured (too many VLFs) or there's IO bottleneck problems on the log and/or data drive, or there's something else hindering checkpoints or recovery.

Have you messed with the recovery interval setting? (sp_configure)


so in order to to make my database not stuck "in recovery mode" every time I restart my server, I need to back up my database and restore it from the same .bak file. This will restart my log chain and "recovery mode" should be quick after that. Am I right?


You have two issues going on here. First, you need to get your databases and your backups configured correctly. Second, why are you restarting SQL Server over and over again? The majority of our servers are restarted maybe two-three times a year. It really sounds like something is going a bit off with your systems if you are restarting them so often.

As to the databases and the backups, it sounds like you're almost there. You have the concept of point in time recovery down. Now you need to implement the log backups, which are completely seperate from, though dependent on, the full backups. You'll need to schedule the log backups so that they run multiple times a day. Not knowing your systems or the business needs, I can't suggest a realistic number, so, as a SWAG, start at every 30 minutes. You may need to adjust that down to run the backups more frequently, but it's really dependent on your needs and your systems. Once you have the log backups running, I think you'll see that your log files don't need to be 300gb or more in size, but, it really depends on the number and size of the transactions in your system. Again, I'm not there, so I can't be sure, 300gb could be the amount of transactions you get within 30 minutes. Regardless, once you get the transaction backup in place, you should be able to determine roughly how much space you need to maintain the log and you can shrink the file down to something near that number, but larger to allow for surges & growing volume.



I don't restart the SQL Server at all, our system engineer's backup exec software failed and we had to restart it because it was tied to SQL server somehow. When we restarted it, we noticed that two of our databases were stuck in "recovery mode" for 30 minutes each. However, it was not the case before. So, my boss got pissed and told me to fix the situation, so that doesn't happen for such an extensive amount of time.

Grant, I understand that I have to fix everything. I inherited this server and databases very recently. Trying to figure out what is going on.
I will definitely start backing up log files, which will keep them from growing to very large. However, my concern is to bring everything back to normal (not taking too much time to leave "recovery mode"). How do I check that too many VLFs problem? Then, how do I fix it?
Post #943038
Posted Friday, June 25, 2010 8:08 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 @ 5:50 AM
Points: 40,209, Visits: 36,618
Jason gave you the link, last one in his signature

http://www.sqlskills.com/blogs/kimberly/post/8-Steps-to-better-Transaction-Log-throughput.aspx



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 #943063
Posted Friday, June 25, 2010 8:15 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, November 7, 2010 3:18 PM
Points: 19, Visits: 60
GilaMonster (6/25/2010)
Jason gave you the link, last one in his signature

http://www.sqlskills.com/blogs/kimberly/post/8-Steps-to-better-Transaction-Log-throughput.aspx


I have read it. It makes sense I have more than 30 000 VLFs because prior DBA set autogrowth size to 1MB and it created one for every 1MB.

I am going to run DBCC SHRINKFILE(transactionloglogicalfilename, TRUNCATEONLY) when no one is using the database i guess.
and if I do:
ALTER DATABASE databasename
MODIFY FILE
(
NAME = transactionloglogicalfilename
, SIZE = newtotalsize
)

what will happen if my current size is 300gb and I set it to 5gb? will that break my log file again?
Post #943071
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse