Shrink SQL Transaction Log
I have a SQL 2008 R2 database, whose .mdf file was 2.2 GB, but the transaction log file (.ldf) was 350 GB! We had a problem, where the log file was set to autogrow by 10%. When that would happen, our disk latency would bring the server to a stand still for 20 minutes. I know we haven't been taking very good care of our database, and I would like to change that moving forward.
Last night, I did a backup of the transaction log. The backup file is 318 GB. Next, I ran a shrink > files on the "log" file type, which only ran for a couple of seconds at most. Now my transaction log file is still 318 GB. On the shrink screen, it says my available free space is 308567.05 MB (99%). So it seems like I have tons of room in the log file, but I can't seem to shrink the file size.
I was looking at this question: https://www.experts-exchange.com/questions/24466045/SQL-Server-Huge-Transaction-Log.html, and it looks like I may need to Trucate the log file, by using this command:
BACKUP LOG <database_name> WITH TRUNCATE_ONLY;
Then, I can shrink the file to a specified size, using something like:
DBCC SHRINKFILE (ur_db_log_filename, 3000);
Is that the best thing to do? Do I need to truncate first? If I run a DBCC SQLPERF(logspace), it shows that my log is only using 0.75% (less than 1%). I'm just not sure what size I should shrink to.
Once I get the log file down, what is the best practice to keep the file small?
Thanks in advance for your help. Unfortunately, I'm not a DBA, it just one of the many hats I have to wear, and I certainly don't know everything there is to know about SQL.
Last night, I did a backup of the transaction log. The backup file is 318 GB. Next, I ran a shrink > files on the "log" file type, which only ran for a couple of seconds at most. Now my transaction log file is still 318 GB. On the shrink screen, it says my available free space is 308567.05 MB (99%). So it seems like I have tons of room in the log file, but I can't seem to shrink the file size.
I was looking at this question: https://www.experts-exchange.com/questions/24466045/SQL-Server-Huge-Transaction-Log.html, and it looks like I may need to Trucate the log file, by using this command:
BACKUP LOG <database_name> WITH TRUNCATE_ONLY;
Then, I can shrink the file to a specified size, using something like:
DBCC SHRINKFILE (ur_db_log_filename, 3000);
Is that the best thing to do? Do I need to truncate first? If I run a DBCC SQLPERF(logspace), it shows that my log is only using 0.75% (less than 1%). I'm just not sure what size I should shrink to.
Once I get the log file down, what is the best practice to keep the file small?
Thanks in advance for your help. Unfortunately, I'm not a DBA, it just one of the many hats I have to wear, and I certainly don't know everything there is to know about SQL.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You could use a different approach: put the db into simple recovery model, do a CHECKPOINT, then try to shrink the log. Once the log shrinks, set the db back to full recovery, then do a full database backup.
That way you don't have to back up that huge log -- let's face it, you're never going to try to apply a 300G+ log backup to the db anyway are you?
This new approach is required in SQL 2008 since SQL no longer allows:
BACKUP LOG ... WITH TRUNCATE_ONLY
That way you don't have to back up that huge log -- let's face it, you're never going to try to apply a 300G+ log backup to the db anyway are you?
This new approach is required in SQL 2008 since SQL no longer allows:
BACKUP LOG ... WITH TRUNCATE_ONLY
ASKER
If you've backed up your log once and tried the shrink you're halfway there. You may have had transactions in flight - backup your log again and shrink again, you should be good to go.You were correct. I ran another transaction log backup (only took a few seconds this time), and shrunk it again. It dropped the size down to about 127 MB. I've been reading all those articles you sent me.
Now that I have the transaction logs on that DB, and some of my other DB's, I created a maintenance plan to back them all up regularly. This is the basic outline of my plan:
Run full backups on Sunday at 1:00 AM
Run differential backups Every day at 2:00 AM
Run Transaction logs every hour on the half hour
Retention policy keeps .bak files (DB backups) for 4 weeks
Keeps .trn files (Transaction log backups) for 2 months
So, I would assume those regular transaction log backups will keep the log truncated, and keep it under control, without needing to shrink it. Is that correct?
I would also assume that I should be able to do a "down to the hour" restore of all my data, any time within the previous 2 months. I am also backing up that server to a NAS, with a retention policy that goes way back pas that. Hopefully that would mean I could pull my previous transaction log backups from there, and restore them if needed. But even if I delete my old backups beyond 2 months, my most recent DB and Transaction log backups should contain my entire database, correct?
Thanks again.
>>So, I would assume those regular transaction log backups will keep the log truncated, and keep it under control, without needing to shrink it. Is that correct?
Correct. Assuming that it is sized correctly to cater for maximum transaction load in the hour between backups.
>>I would also assume that I should be able to do a "down to the hour" restore of all my data, any time within the previous 2 months.
Incorrect. You're only keeping the bak files for 4 weeks. You have to start a restore with a full backup. If you're only keeping them for 4 weeks, transactions log backups kept before your first available full are worthless.
Change your question to "any time within the previous 4 weeks" and I'd say correct.
If you have older full backups on the NAS, then I guess you could use those ....
See below for a slight modification to that though - because under certain circumstances you could actually restore right up to the point of failure.
>>But even if I delete my old backups beyond 2 months, my most recent DB and Transaction log backups should contain my entire database, correct?
If I understand what you are saying correctly, then yes. In order to restore your database you need: Most recent Full, most recent Diff (if you're taking Diffs), all Transaction logs from most recent diff to point of failure.
As long as you don't lose your transaction log in a failure (say you have a disk failure for your data file but the log is still available), you can perform a tail log backup as the first step in your recovery to restore a database right up to the point of failure.
Have a watch of this Video Micro Tutorial to see an example of that in action: https://www.experts-exchange.com/Database/MS-SQL-Server/VP_470.html
Correct. Assuming that it is sized correctly to cater for maximum transaction load in the hour between backups.
>>I would also assume that I should be able to do a "down to the hour" restore of all my data, any time within the previous 2 months.
Incorrect. You're only keeping the bak files for 4 weeks. You have to start a restore with a full backup. If you're only keeping them for 4 weeks, transactions log backups kept before your first available full are worthless.
Change your question to "any time within the previous 4 weeks" and I'd say correct.
If you have older full backups on the NAS, then I guess you could use those ....
See below for a slight modification to that though - because under certain circumstances you could actually restore right up to the point of failure.
>>But even if I delete my old backups beyond 2 months, my most recent DB and Transaction log backups should contain my entire database, correct?
If I understand what you are saying correctly, then yes. In order to restore your database you need: Most recent Full, most recent Diff (if you're taking Diffs), all Transaction logs from most recent diff to point of failure.
As long as you don't lose your transaction log in a failure (say you have a disk failure for your data file but the log is still available), you can perform a tail log backup as the first step in your recovery to restore a database right up to the point of failure.
Have a watch of this Video Micro Tutorial to see an example of that in action: https://www.experts-exchange.com/Database/MS-SQL-Server/VP_470.html
ASKER
Thanks again for the reply. Just one follow up on the retention period, to make sure I understand this correctly. I am taking full backups weekly, differential backups weekly, and hourly transaction log backups. Then, let's say may retention period is 4 weeks.
I just did a backup of my transaction log while it was huge, so I have a 300+ GB backup file of the log. Let's fast forward to 2 months from now. That huge backup file has been deleted, because it's outside of my retention period.
Let's say I have failure on a Friday in March. I don't have that huge backup anymore, but I have a Full DB backup from the previous Sunday, plus differential backups from every day since then, and all my hourly transaction log backups for the entire week as well (and the previous 4 weeks). With that information, I should be able to restore my entire DB? I have the full backup, the diff backups, and the trn's for the last week, so that should be everything, right? Even though I deleted my huge 300+GB backup from 1/26?
I just want to make sure I don't have to keep all backups I have ever made from the beginning of time in order to restore my entire DB in the event of failure.
Thanks again for your time!
I just did a backup of my transaction log while it was huge, so I have a 300+ GB backup file of the log. Let's fast forward to 2 months from now. That huge backup file has been deleted, because it's outside of my retention period.
Let's say I have failure on a Friday in March. I don't have that huge backup anymore, but I have a Full DB backup from the previous Sunday, plus differential backups from every day since then, and all my hourly transaction log backups for the entire week as well (and the previous 4 weeks). With that information, I should be able to restore my entire DB? I have the full backup, the diff backups, and the trn's for the last week, so that should be everything, right? Even though I deleted my huge 300+GB backup from 1/26?
I just want to make sure I don't have to keep all backups I have ever made from the beginning of time in order to restore my entire DB in the event of failure.
Thanks again for your time!
You are correct.
In your example, you would restore your full backup from the previous Sunday with NORECOVERY.
Then you would restore your most recent differential from let's say last night with NORECOVERY.
Then you apply all your TLogs in order (starting with the first one after the Diff) with NORECOVERY, except the last one which is done with RECOVERY.
The "last one" can be the last hourly one, or the tail log backup (as described in the video linked above), depending upon what your real "last" TLog backup file is.
The minute you took your next full backup, that 300GB TLog back is irrelevant.
Just make sure you have enough history there in case one of the backup files is bad for whatever reason.
Also - test your backups regularly. Your backups are only as good as the last time you tested a restore. If you've never tested a restore to test of your prod backup ... and don't do so regularly ... my only question to you is this.
"Is your resume up to date?" :)
In your example, you would restore your full backup from the previous Sunday with NORECOVERY.
Then you would restore your most recent differential from let's say last night with NORECOVERY.
Then you apply all your TLogs in order (starting with the first one after the Diff) with NORECOVERY, except the last one which is done with RECOVERY.
The "last one" can be the last hourly one, or the tail log backup (as described in the video linked above), depending upon what your real "last" TLog backup file is.
The minute you took your next full backup, that 300GB TLog back is irrelevant.
Just make sure you have enough history there in case one of the backup files is bad for whatever reason.
Also - test your backups regularly. Your backups are only as good as the last time you tested a restore. If you've never tested a restore to test of your prod backup ... and don't do so regularly ... my only question to you is this.
"Is your resume up to date?" :)
ASKER
Thanks again for your help, Steve. The documentation you provided is excellent. I have a regular backup routine in place now, and my log file is WAY smaller (350,000 MB down to about 400 MB). I will continue to monitor this over time, and make sure it is staying small with the regular backups. I feel much better about my setup than I did before.
ASKER