Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
GhantaBro
Posting Yak Master
215 Posts |
 Posted - 2010-02-19 : 14:42:53
|
| Hi Guys,We have a database that is 2tb in size with 2 tables that are partitioned (monthly partition) in sql 2008. I want to make some space available from the server for other projects... please advice me what best I should do in order to compress the data? Tables have 2 billions records...Please advice me the approaches in order to free up some space. Thanks a lot in advance for the suggestions. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2010-02-19 : 15:52:55
|
Are you using Enterprise Edition? If so, turn on table compression.
N 56°04'39.26"E 12°55'05.63" |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-20 : 01:57:38
|
| Sparse columns too perhaps? (Assuming you have some, but at 2TB I expect that has already been handled by some other route) |
|
|
|
GhantaBro
Posting Yak Master
215 Posts |
Posted - 2010-02-20 : 22:14:39
|
| Yeah table compression is something I should use and we are using sql 2008 Enterprise Edition... I guess Compression on the tables can be done in partitions separately... table has partitions by month..I have not used Sparse Columns Properties... should I recreate new tables with these features and then populate the tables? Or I can make changes in the existing table itself? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-21 : 03:54:52
|
You can use ALTER TABLE - its just a flag on a column definition:SomeColumnName varchar(20) SPARSE NULL If you think Sparse Columns might be useful then, ipso facto, you must have some columns that have a high proportion of NULL values. If these columns are also indexed then you can use Filtered Indexes to reduce the index size (e.g. set up a filter on the Index to only store values where TheIndexColumn IS NOT NULL). That might save you a shed-load of space on your indexes  |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2010-02-21 : 09:08:54
|
http://msdn.microsoft.com/en-us/library/cc280604.aspx
N 56°04'39.26"E 12°55'05.63" |
|
|
|
GhantaBro
Posting Yak Master
215 Posts |
Posted - 2010-02-22 : 13:50:04
|
| Thanks for the response... I am sure I can find few columns that has mostly nulls values and for those Sparse Properties is a good idea. Also, how about downtime for tables of 2 billion records if I want to alter using sparse and also do a page level compression? They are partitioned by month. Wanted to learn from others experience on this... thanks for the help. |
|
|
|
|
|
|
Data Compression