?
Solved

compressing transaction log

Posted on 2009-05-06
15
Medium Priority
?
474 Views
Last Modified: 2012-05-06
is trasaction log compression before shipping in SQL Server 2008 compared to SLQ Server 2005? first , can it be done in 2005?
0
Comment
Question by:crishna1
  • 6
  • 3
  • 3
  • +1
15 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 24317465
not really...you will need to shrink it if it gets too big.  BUT, if you do frequent enough log backups, you shoudln't run into any problems.
0
 

Author Comment

by:crishna1
ID: 24317555
from your responce,  can i assume sql server 2005 or 2008 do not compress? or compression in 2008 is no superior than 2005?
say the database size would grow to 1 TB and i'm planning on backingup the log every 3-4 hours.

also, you mentioned to shrink the log, i guess this comes as an option in SSMS, is it?
 
0
 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 225 total points
ID: 24317597
you can right click on the db and go to tasks and shrink it from there.

I don't think log compressions is an option in 2008...data compression is, but log compression...it is ssequential data...really nothing to compress.

If you plan on your log file getting big, do more frequent log backups.

Also, read this article I wrote about it.
http://blogs.techrepublic.com.com/datacenter/?p=448
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!

 
LVL 22

Assisted Solution

by:dportas
dportas earned 225 total points
ID: 24317619
Log compression is supported in SQL Server 2008 Enterprise but not in 2005.
http://technet.microsoft.com/en-us/library/ms188168.aspx

0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24317642
just to be clear, David you're referring to compressing the log backups, and not the log files themselves, right?
0
 

Author Comment

by:crishna1
ID: 24317803
is it supported only on Enterprise Edition? what about Standard Edition?
0
 

Author Comment

by:crishna1
ID: 24317820
i am reffering to compressing log backup before shipping it, hope i am clear.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 300 total points
ID: 24317832
I suspect this was the link that was intended previously:
Backup Compression (SQL Server)
http://technet.microsoft.com/en-us/library/bb964719.aspx
0
 
LVL 22

Expert Comment

by:dportas
ID: 24317837
Yes. For log shipping it's the size of the backup that matters. You don't need to do anything with the files.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24317839
ah...sorry, thought you meant the log itself. Sure, you can compress both backup types, but with Enterprise Edition in 2008 only.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24317855
Yep..but unless I just missed it, I didn't see where the author has referred to using log shipping.
0
 
LVL 22

Expert Comment

by:dportas
ID: 24317920
I read "before shipping" and assumed that's what he meant.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24317927
Here is a good article on Backup Compression using SQL Server 2008
Backup compression in SQL Server 2008
http://blogs.techrepublic.com.com/datacenter/?p=433

:)
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24317960
david, you're probably right...I just missed it.

Thats a good looking article..  :)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24317971
I thought you would get a kick out of that. :)
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

830 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