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

SQL 2005 Transaction logs won't shrink

My SQL 2005 transaction logs are not shrinking with the maintenance plans I currently have in place.  I run a FULL DB backup Tues-Sat at 1 am.  TL's are backed up every 2 hours between 5:00 am and 6:00 pm Mon-Friday.  I run DB optimization every Saturday at 11:00 pm.  Can someone tell me what I'm missing?  When I run a full DB backup shouldn't the TL's shrink?  Recovery model is set to FULL.  Its my undestanding that I could change this to Simple Mode and avoid the issue, but I'd like to keep them in FULL recovery mode...at least for now.  I'm attaching a snapshot of my maintenance plans.  Any thoughts?
main-plans.jpg
0
ammexit
Asked:
ammexit
  • 7
  • 5
1 Solution
 
chapmandewCommented:
>>When I run a full DB backup shouldn't the TL's shrink?
Absolutely not.  The TL files will never shrink by themselves...the space in them will be reused when you run the log backups, but they'll never shrink.  You must explicitly shrink them if you want them smaller.
0
 
ammexitAuthor Commented:
Then wouldn't the "Shrink Database" that I run on Saturday's take care of it then?  I'm going to read your aricle now.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
chapmandewCommented:
I guess it really depends on if it is shrinking the log files too....
0
 
ammexitAuthor Commented:
But the maintenance plan defines that task as "The Shrink Database task reduces the disk psace consumed by the database and log files by removing empty data and log pages."  I don't see any option to specify the log files.  Only to select the DB's to run the task on.  Wouldn't that mean TL's associated with the selected DB's would shrink?
0
 
chapmandewCommented:
how small are you wanting them to be? It might be shrinking them, but they get large again later..espeically if you're only doing it 1x per week.
why are you concerned w/ shrinking them?  are you running out of space?
0
 
ammexitAuthor Commented:
I don't necessarily want them down to a certain size, just want to make sure my SQL Server is configured correctly. I'm new to SQL and just build this box about three months ago.  I didn't have a maintenance plan setup initially and the TL's for my 19 GB database grew to 120 GB.  It was on its way to maxing out the drive.  I setup the plans and didn't see any "shrinking" happening.  Called support and we ran a procedure to force a truncation of the log file.  Left it under the idea that the TL's would shrink properly going forward.  At that point they had shrunk to 3 GB.  Its been two weeks and the TL's are up to 18 GB.
0
 
chapmandewCommented:
18GB seems a bit high, but it is not outrageous so long as you have enough space.  I'd say if they get above 30 then you can start worrying.  So long as you're doing consistent trans log backups, you should be fine.  If they get above 30, do a full backup, a log truncation,and another full backup.  Then shrink the log files.  THEN, set your log backups to occur more frequently.
0
 
ammexitAuthor Commented:
More frequently then every 2 hours?  Would it be a good idea to set my "Shrink Database" task to occur nightly and see if that takes the size down?  This way I could see if the log files are actually shrinking properly.
0
 
chapmandewCommented:
sure, you can try it for a couple of nights.

A log backup is only so you can restore to a point in time...so, you could potentially lose 2 hrs of data if you have a problem.  Try every hr or so along with your nightly shrink to see if it helps...it should.
0
 
ammexitAuthor Commented:
Okay, I'll do that and repost.  Thank you Chapmandew!  I'll let you know what happens.
0
 
chapmandewCommented:
sounds good.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now