Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

compressing read only file groups Expand / Collapse
Author
Message
Posted Tuesday, January 21, 2014 8:40 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 8:51 AM
Points: 394, Visits: 1,977
We have a table partitioned to different file groups based on date. We changed all the old filegroups to read only and left just one file group to load data on to it. Now we are running short of space. We are planning to compress the old file groups and release some space based on the following article-
http://technet.microsoft.com/en-us/library/ms190257(v=sql.105).aspx.

But when tested on the test server ofcourse with very less data I don't see any change in the file size. Any suggestions??
Post #1533144
Posted Tuesday, January 21, 2014 2:11 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:50 AM
Points: 6,356, Visits: 13,686
What steps have you taken so far?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1533352
Posted Wednesday, January 22, 2014 3:24 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 8:51 AM
Points: 394, Visits: 1,977
Perry Whittle (1/21/2014)
What steps have you taken so far?


I used the command COMPACT /C /S source destination to compress all the read file groups. It worked fine. But I need to know how safe is this. Also the statement in the article says The execution time of these statements will be slower than equivalent statements on noncompressed filegroups.
Post #1533527
Posted Wednesday, January 22, 2014 4:58 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:50 AM
Points: 6,356, Visits: 13,686
muthyala_51 (1/22/2014)
Perry Whittle (1/21/2014)
What steps have you taken so far?


I used the command COMPACT /C /S source destination to compress all the read file groups. It worked fine. But I need to know how safe is this.

It's standard NTFS folder\file compression and has been available in Windows server since Windows 2000.



muthyala_51 (1/22/2014)
[quote][b]Also the statement in the article says The execution time of these statements will be slower than equivalent statements on noncompressed filegroups.

That is correct. You have the overhead in the OS to uncompress the data before it is read.


You also stated that you didnt see any difference in file size on your test, can you supply more details on the volume and the file itself?
What was the exact output for the command

COMPACT /C /S



-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1533564
Posted Wednesday, January 22, 2014 8:21 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 8:51 AM
Points: 394, Visits: 1,977
Perry,

I was wrong. I can see the difference in the file property window. Do you suggest to go for such process? What's your opinion on compressing files ?
Post #1533670
Posted Wednesday, January 22, 2014 8:26 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 12:38 AM
Points: 33,267, Visits: 15,436
Do you have Enterprise Edition? I'd data compress first.

However, compressing the disk can work. Can you give more information on your setup and sizes? How much data growth is there? Has the CPU usage dramatically increased?

Adding disks might be a better solution.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1533679
Posted Wednesday, January 22, 2014 8:29 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 8:51 AM
Points: 394, Visits: 1,977
current size of the database is 2.36 TB. Monthly around 100 GB of data will be added to this partitioned table. We can increase the drive size by adding more space. But I am looking for options to reduce the data size by file compression or data compression.
Post #1533687
Posted Wednesday, January 22, 2014 8:34 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:50 AM
Points: 6,356, Visits: 13,686
SQL Server data compression would be my preference over NTFS compression

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1533690
Posted Wednesday, January 22, 2014 8:46 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 12:38 AM
Points: 33,267, Visits: 15,436
Perry Whittle (1/22/2014)
SQL Server data compression would be my preference over NTFS compression


+ 1

You can also make sure that your read only files are mostly full. I saw a technique for creating a new filegroup, rebuilding the clustered index to that filegroup and shrinking to just the size needed. If that's the only operation in that filegroup, you shouldn't get fragmentation.

Other than that, you need to decide if the CPU tradeoff is worth the space saved.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1533698
Posted Wednesday, January 22, 2014 8:51 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 8:51 AM
Points: 394, Visits: 1,977
Steve,

Can you help me to find that article or elaborate on that technique which you are referrring to?
Post #1533700
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse