SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Restore takes long time - how do I clear committed transactions from log ?


SQL Restore takes long time - how do I clear committed transactions from log ?

Author
Message
Gamleur84
Gamleur84
Old Hand
Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)

Group: General Forum Members
Points: 317 Visits: 124
Hi everyone,

There is so many blog posts and articles out there about this case and I have been reading, but I am still confused. I restored a database and it took more than 12+ hours to complete. I noticed that the transaction log was more than 500GB, so I am guessing the redo/undo phase was very long. Database is in full recovery mode.

If the transaction log is 500GB, but its has 490GB of free space. Does it mean that I have only 10GB of committed/uncommitted transactions?
If the transaction log is 500GB and it has no free space, does it mean that I have a lot of committed/uncommitted transactions in the log file? If yes, should I just take a backup of the transaction log to clear the committed/uncommitted transactions ? And then take a full backup.

Thank you
anthony.green
anthony.green
SSC Guru
SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)

Group: General Forum Members
Points: 56911 Visits: 8463
Gamleur84 - Wednesday, December 6, 2017 12:32 PM
Hi everyone,

There is so many blog posts and articles out there about this case and I have been reading, but I am still confused. I restored a database and it took more than 12+ hours to complete. I noticed that the transaction log was more than 500GB, so I am guessing the redo/undo phase was very long. Database is in full recovery mode.

If the transaction log is 500GB, but its has 490GB of free space. Does it mean that I have only 10GB of committed/uncommitted transactions?
If the transaction log is 500GB and it has no free space, does it mean that I have a lot of committed/uncommitted transactions in the log file? If yes, should I just take a backup of the transaction log to clear the committed/uncommitted transactions ? And then take a full backup.

Thank you

In essence yes that is correct, regular log backups should be made to ensure you can meet your companies RTO and RPO as well as keep sizes manageable.

If you have a 500GB log and its full back up the log first, then do a full backup.

If you have restored this to a new server the restore command has to create the database structure as is in the full backup, so it will still have to create a 500GB log file even thought if 490GB is empty it still creates the file at 500GB not 10GB. Again another reason to do regular log backups to stop the file size getting to much out of control.

That being said, you may want to profile the wait stats when restoring, your probably getting a lot of IO waits, I would be inclined to see if you have "Instant File Initialization" enabled and try the restore again with a completely new database see if that helps with reducing the 12 hour restore time.




How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


jasona.work
jasona.work
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21811 Visits: 13935
anthony.green - Thursday, December 7, 2017 1:13 AM
Gamleur84 - Wednesday, December 6, 2017 12:32 PM
Hi everyone,

There is so many blog posts and articles out there about this case and I have been reading, but I am still confused. I restored a database and it took more than 12+ hours to complete. I noticed that the transaction log was more than 500GB, so I am guessing the redo/undo phase was very long. Database is in full recovery mode.

If the transaction log is 500GB, but its has 490GB of free space. Does it mean that I have only 10GB of committed/uncommitted transactions?
If the transaction log is 500GB and it has no free space, does it mean that I have a lot of committed/uncommitted transactions in the log file? If yes, should I just take a backup of the transaction log to clear the committed/uncommitted transactions ? And then take a full backup.

Thank you

In essence yes that is correct, regular log backups should be made to ensure you can meet your companies RTO and RPO as well as keep sizes manageable.

If you have a 500GB log and its full back up the log first, then do a full backup.

If you have restored this to a new server the restore command has to create the database structure as is in the full backup, so it will still have to create a 500GB log file even thought if 490GB is empty it still creates the file at 500GB not 10GB. Again another reason to do regular log backups to stop the file size getting to much out of control.

That being said, you may want to profile the wait stats when restoring, your probably getting a lot of IO waits, I would be inclined to see if you have "Instant File Initialization" enabled and try the restore again with a completely new database see if that helps with reducing the 12 hour restore time.

If I recall correctly, IFI doesn't help with the transaction log, SQL is still going to create the file, then zero out every sector of that file (whereas IFI does help with the data files, which SQL does *NOT* zero out.)
So, a 1/2 terabyte file is going to take a long time to zero out, even on fast storage...

So it might be worthwhile to find out *why* the log is 500GB, then look into what can be done to get it, and keep it, smaller (if you have to do restores on a regular basis.) That might mean increasing the frequency of log backups, finding transactions (data loads, etc) that bloat the log and tuning those, or other changes.

anthony.green
anthony.green
SSC Guru
SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)

Group: General Forum Members
Points: 56911 Visits: 8463
jasona.work - Thursday, December 7, 2017 6:04 AM
anthony.green - Thursday, December 7, 2017 1:13 AM
Gamleur84 - Wednesday, December 6, 2017 12:32 PM
Hi everyone,

There is so many blog posts and articles out there about this case and I have been reading, but I am still confused. I restored a database and it took more than 12+ hours to complete. I noticed that the transaction log was more than 500GB, so I am guessing the redo/undo phase was very long. Database is in full recovery mode.

If the transaction log is 500GB, but its has 490GB of free space. Does it mean that I have only 10GB of committed/uncommitted transactions?
If the transaction log is 500GB and it has no free space, does it mean that I have a lot of committed/uncommitted transactions in the log file? If yes, should I just take a backup of the transaction log to clear the committed/uncommitted transactions ? And then take a full backup.

Thank you

In essence yes that is correct, regular log backups should be made to ensure you can meet your companies RTO and RPO as well as keep sizes manageable.

If you have a 500GB log and its full back up the log first, then do a full backup.

If you have restored this to a new server the restore command has to create the database structure as is in the full backup, so it will still have to create a 500GB log file even thought if 490GB is empty it still creates the file at 500GB not 10GB. Again another reason to do regular log backups to stop the file size getting to much out of control.

That being said, you may want to profile the wait stats when restoring, your probably getting a lot of IO waits, I would be inclined to see if you have "Instant File Initialization" enabled and try the restore again with a completely new database see if that helps with reducing the 12 hour restore time.

If I recall correctly, IFI doesn't help with the transaction log, SQL is still going to create the file, then zero out every sector of that file (whereas IFI does help with the data files, which SQL does *NOT* zero out.)
So, a 1/2 terabyte file is going to take a long time to zero out, even on fast storage...

So it might be worthwhile to find out *why* the log is 500GB, then look into what can be done to get it, and keep it, smaller (if you have to do restores on a regular basis.) That might mean increasing the frequency of log backups, finding transactions (data loads, etc) that bloat the log and tuning those, or other changes.

Ah yes I always forget that part and the documentation is a little hazy around the log file, as the top of the doc says it works for logs but then you read further and it says it doesn't as logs are always zeroed out when created or grown, how I love the consistency of Microsoft Docs.




How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


Sue_H
Sue_H
SSC-Dedicated
SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)

Group: General Forum Members
Points: 30324 Visits: 8803


In addition to taking a log backup and looking into why the log is that large, I would also want to look at the number of VLFs as that can impact the recovery time.
If you want to be certain on what phase of the restore is taking the most time, you can set a trace flags for verbose logging of the restore prior to doing the restore:
DBCC TRACEON(3004, 3605, -1)

Sue



Gamleur84
Gamleur84
Old Hand
Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)

Group: General Forum Members
Points: 317 Visits: 124
Hi guys, thank you for the answers.

I am not concerned about the 500GB empty file. I am concerned about the amount of committed/uncommited transaction in the log file.Let me give more advanced details on this case because I think it is very interesting.

The log file is 500GB, but it is 99% empty, but here is the interesting part: I have 600 VLF files in the transaction log file and all of them have a status 2 (active). I thought I was crazy, how can my log file be empty and have all the VLF files marked as active? I found the reason here: https://blogs.msdn.microsoft.com/sql_pfe_blog/2013/06/27/lazy-log-truncation-clearing-of-sql-transaction-log-vlf-status-deferred/.

In short, my transaction log file is empty (truncated), but all VLF files are active.

How does it effect the restore ? Will the REDO/UNDO phases go through all VLF files because they are marked as active?
Sue_H
Sue_H
SSC-Dedicated
SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)

Group: General Forum Members
Points: 30324 Visits: 8803

Having too many VLFs primarily affects the discovery portion of the recovery process as it will read every VLF one at a time whether or not there are active transactions to be processed. So it reads every VLF one by one in a single threaded fashion no matter what the status.

Sue




Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (124K reputation)SSC Guru (124K reputation)SSC Guru (124K reputation)SSC Guru (124K reputation)SSC Guru (124K reputation)SSC Guru (124K reputation)SSC Guru (124K reputation)SSC Guru (124K reputation)

Group: General Forum Members
Points: 124219 Visits: 18070
keep the log large enough to support transactions in between log backups ( when in full recovery ) but small enough to facilitate a sensible restore time.
IFI will initialise data files quickly but the log is always zeroed

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (461K reputation)SSC Guru (461K reputation)SSC Guru (461K reputation)SSC Guru (461K reputation)SSC Guru (461K reputation)SSC Guru (461K reputation)SSC Guru (461K reputation)SSC Guru (461K reputation)

Group: General Forum Members
Points: 461279 Visits: 43772
Gamleur84 - Thursday, December 7, 2017 11:50 AM
I am not concerned about the 500GB empty file.

You should be. Even with a log file in good shape insofar as VLFs, it takes a long time to format even with "Instant File Initialization" enabled. If it turns out that you need a 500GB log file, then you may have a serious problem with some code and a couple of other things.


--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Thomas Rushton
Thomas Rushton
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: Moderators
Points: 10936 Visits: 5834
Was this database part of an availability group? There's a thing about lazy recycling of transaction log VLFs that might be what's causing problems / concerns. https://blogs.msdn.microsoft.com/sql_pfe_blog/2013/06/27/lazy-log-truncation-clearing-of-sql-transaction-log-vlf-status-deferred/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search