• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 278
  • Last Modified:

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: http://www.experts-exchange.com/Database/MS-SQL-Server/Q_24466045.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.
0
Jake Pratt
Asked:
Jake Pratt
  • 4
  • 3
1 Solution
 
Steve WalesSenior Database AdministratorCommented:
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.

Have a read of this article http://www.experts-exchange.com/Database/MS-SQL-Server/A_11077-How-to-shrink-a-bloated-log-file.html - it covers just your experience.

From here on out, regular TLog backups are your friend and will stop this from happening again.

The article also links to articles discussing strategies for regrowing your TLog sensibly to a size that meets your transaction throughput needs, but with only a 2.2GB DB, I don't imagine you have a whole lot of traffic to worry about
0
 
Jake PrattAuthor Commented:
Steve.  Thank you for your reply.  I will try another backup and shrink tonight.  Very nicely written article.  I will also take a look at the additional links for more information on backup strategies, and autogrowth recommendations.
0
 
Scott PletcherSenior DBACommented:
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
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Jake PrattAuthor Commented:
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.
0
 
Steve WalesSenior Database AdministratorCommented:
>>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: http://www.experts-exchange.com/Database/MS-SQL-Server/VP_470.html
0
 
Jake PrattAuthor Commented:
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!
0
 
Steve WalesSenior Database AdministratorCommented:
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?" :)
0
 
Jake PrattAuthor Commented:
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.
0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now