Solved

compressing transaction log

Posted on 2009-05-06
15
468 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

777 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