Solved

compressing transaction log

Posted on 2009-05-06
15
470 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 75 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 22

Assisted Solution

by:dportas
dportas earned 75 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 100 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

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

733 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