Post reply

When can we use "Do not Compress"?

  • December 27, 2011 at 11:59 pm

    #254110

    "Default Server Setting","Do not Compress" and "Compress Backup" features are given in sql server 2008.

    When can we use "Do not Compress"?

    Thanks

  • December 28, 2011 at 12:09 am

    #1425948

    Backup compress is an inbuilt tool in sql server 2008.

    If the database size is high (ex: 700 gb) we can use this option compress and when the database size is less (ex: 100 gb) we can use do not compress option.

  • December 28, 2011 at 12:19 am

    #1425953

    This is a great question, mostly because it got me searching and learning on the net... 🙂

    This article is quite helpful: http://technet.microsoft.com/en-us/library/bb964719.aspx

    I guess that if you have a SQL instance that needs to be available 24/7 and has high CPU usage then you wouldn't want to compress your backups, as doing so would rob you of your CPU power.

    Anyone else think of a few scenarios?

  • December 28, 2011 at 1:35 am

    #1425980

    If you have an encrypted database it can compress very poorly, so it is often worth ensuring no compression is selected for a database that is encrypted.

  • December 28, 2011 at 2:12 am

    #1425985

    I think "Default server setting" may keep Compress feature???

    Therefore "Do Not Compress" has given

    Its a guess only.

    Thanks

  • December 28, 2011 at 8:16 am

    #1426103

    forsqlserver (12/28/2011)

    I think "Default server setting" may keep Compress feature???

    Therefore "Do Not Compress" has given

    Its a guess only.

    Not necessarily, you can change the default behaviour as well.

    How to: View or Change the backup compression default Option (SQL Server Management Studio)

    http://msdn.microsoft.com/en-us/library/bb933863.aspx

    Caution: compression significantly increases CPU usage, and the additional CPU consumed by the compression process might adversely impact concurrent operations.

  • December 29, 2011 at 12:55 am

    #1426340

    Yes Dev Thanx...

    Thanks

  • December 29, 2011 at 2:49 pm

    #1426730

    By Default Backup compression is turned Off.In order to use it we have to turn it on.

    We can use the backup compression if our application is not resource intensive.As compressing the backup utilizes the cpu.

    Thanks

  • December 29, 2011 at 7:23 pm

    #1426756

    I leave backup compression turned on by default, and turn it off only for backups of specific databases that have very low compressibility.

    Even though compression uses more CPU, the reduced IO results in less CPU, so the net impact is not that high in most cases. Unless the CPU usage is very high at the time you run backups, you can probably gain backup speed and use less space for backups. If you are backing up over the network to a file share it will probably run much faster.

    Databases with transparent data encryption do not compress, and Book Online specifically says to not use backup compression for TDE databases.

    Backups of Sharepoint databases with a high content of compressed image files, like .PDF files, do not compress well.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply