June 24, 2010 at 5:28 pm
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[/url]
Learn Extended Events
June 25, 2010 at 5:49 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 25, 2010 at 7:40 am
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?
June 25, 2010 at 8:08 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 25, 2010 at 8:15 am
GilaMonster (6/25/2010)
Jason gave you the link, last one in his signaturehttp://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?
June 25, 2010 at 8:23 am
It doesn't sound like the log file has been broke, just improperly configured and managed.
Whether or not 5gb is the correct size really depends on the number and size of the transactions you have to deal with and the amount of time between log backups.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 25, 2010 at 8:24 am
Break? A simple shrink cannot break the log. If you mean the log chain, no, shrink does not break that either
Once you've shrunk it down, regrow it back to a reasonable size. If the log reaches 300GB from time to time, regrow it back to that size. Then fix the autogrow settings to something reasonable for the size the log is.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 25, 2010 at 8:24 am
Before you shrink your log file you need to make sure you have a couple of things in place
1. Perform a full backup of your database (safety measure).
2. Do it during an authorized outage window.
After you shrink your log file, you should grow it back out to an appropriate size. 5GB may not be enough. You want to make sure it is large enough to avoid auto file growths. I would grow it in 4GB or 8GB chunks.
Example: So if you need a 32 GB log file (assuming that may have plenty of room in case a process goes crazy) you would create an 8GB log file and then grow it by 8GB until you reach 32 GB (3 growths after the initial sizing to 8GB).
You will still have VLFs in your log file - this is normal and expected. However, they will not be severely fragmented and certainly would not be 30,000.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 25, 2010 at 8:31 am
GilaMonster (6/25/2010)
If the log reaches 300GB from time to time, regrow it back to that size. Then fix the autogrow settings to something reasonable for the size the log is.
I don't think 300GB would be necessary. If you are performing log backups (doesn't seem like you were) then the log will not likely reach that size. What size is your database? How many transactions do you do an hour? Part of proper sizing for your transaction log will depend on how frequently you backup your t-log and how much is backed up from the t-log each time.
Setting an appropriate autogrow for the log file is key to avoiding too many vlfs as well. As you saw, 1MB growths cause tremendous fragmentation and a slow restart recovery.
Before you take action on what size to make your log file, you will need to do some more homework and trend your database usage / t-log usage.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 25, 2010 at 8:39 am
Based on what I have read:
Currently it is 300gb log file.
If I backup log files it becomes empty ( less than 1gb something like that) and these 300gb will be stored in that backup of log file.
Then I set the autogrowth to reasonable size depending how many transactions I do.
Then make sure I do regular log back ups, so it doesn't grow above like 50gb.
If for some reason I don't do log back up and my file had to grow it will create extra VLF. If I have too many of them, just do DBCC SHRINKFILE.
Once I have a small log file and few VLFs. My databases should not take much time recovering (i.e checking for consistency) if I need to restart SQL server.
Am I correct?
June 25, 2010 at 8:42 am
Sounds basically accurate to me. The only addition I would make is to be careful about the shrink process. It can lead to other issues, including file fragmentation. If you find yourself running shrink more than twice, you may need to reasses the size of the log or the frequency of the backups. I wouldn't rely on shrink as a regular part of the process.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 25, 2010 at 8:46 am
yessen (6/25/2010)
Once I have a small log file and few VLFs. My databases should not take much time recovering (i.e checking for consistency) if I need to restart SQL server.
Providing the large number of VLFs was the reason for the slow recovery.
p.s. recovery is not checking for consistency. It's doing rollback/rollforward of transactions to ensure transactional integrity.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 25, 2010 at 8:48 am
GilaMonster (6/25/2010)
yessen (6/25/2010)
Once I have a small log file and few VLFs. My databases should not take much time recovering (i.e checking for consistency) if I need to restart SQL server.Providing the large number of VLFs was the reason for the slow recovery.
p.s. recovery is not checking for consistency. It's doing rollback/rollforward of transactions to ensure transactional integrity.
I see. It remembered the state. Went backwards using the log file and then forward. If states of the databases matched, it was happy 😀
June 25, 2010 at 9:02 am
yessen (6/25/2010)
I see. It remembered the state. Went backwards using the log file and then forward. If states of the databases matched, it was happy 😀
SQL went through the active portion of the transaction log undoing changes that had happened but not committed at the time of shutdown and redoing transactions that had committed but hadn't been written to the data file at the time of shutdown.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 25, 2010 at 9:16 am
I switched to simple logging like 5 days ago to prevent files growing too much because my boss was happy if we could not restore at particular point of time. He said it is ok because we have everyday backups.
But if I want to do log backup:
BACKUP LOG database
TO DISK = N'c:\backup.bak'
Some articles say, it is impossible when simple logging.
Is it ok if i switch back to full logging and backup log this way?
Since I know now what is going on and will be able to manage my log file at a manageable size, I can switch back to full logging and have benefit of restoring to point in time.
Viewing 15 posts - 16 through 30 (of 57 total)
You must be logged in to reply to this topic. Login to reply