Solved

Shrink SQL Transaction Log

Posted on 2015-01-27
8
240 Views
Last Modified: 2015-01-29
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
Comment
Question by:Jake Pratt
  • 4
  • 3
8 Comments
 
LVL 22

Accepted Solution

by:
Steve Wales earned 500 total points
ID: 40573443
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
 

Author Comment

by:Jake Pratt
ID: 40573660
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40573809
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
 

Author Comment

by:Jake Pratt
ID: 40576250
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 22

Expert Comment

by:Steve Wales
ID: 40576266
>>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
 

Author Comment

by:Jake Pratt
ID: 40576327
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
 
LVL 22

Expert Comment

by:Steve Wales
ID: 40576336
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
 

Author Closing Comment

by:Jake Pratt
ID: 40578574
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Suggested Solutions

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now