January 21, 2014 at 8:40 am
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??
January 21, 2014 at 2:11 pm
What steps have you taken so far?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 22, 2014 at 3:24 am
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.
January 22, 2014 at 4:58 am
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)
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" 😉
January 22, 2014 at 8:21 am
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 ?
January 22, 2014 at 8:26 am
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.
January 22, 2014 at 8:29 am
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.
January 22, 2014 at 8:34 am
SQL Server data compression would be my preference over NTFS compression
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 22, 2014 at 8:46 am
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.
January 22, 2014 at 8:51 am
Steve,
Can you help me to find that article or elaborate on that technique which you are referrring to?
January 22, 2014 at 9:13 am
Let me dig around.
January 22, 2014 at 9:16 am
January 22, 2014 at 9:31 am
muthyala_51 (1/22/2014)
Steve,Can you help me to find that article or elaborate on that technique which you are referrring to?
As follows
ALTER DATABASE olddata ADD FILEGROUP newFG
ALTER DATABASE olddata ADD FILE(NAME=somename, FILENAME = 'drive:\path\somefile.NDF', SIZE = 10GB, MAXSIZE = 20GB, FILEGROWTH = 1GB)
CREATE CLUSTERED INDEX [someindex] on schema.table (somecolumn)
WITH (DROP_EXISTING=ON) ON [newFG]
ALTER DATABASE olddata MODIFY FILEGROUP newFG READ_ONLY
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 23, 2014 at 8:39 am
I agree with everyone above - if at all possible, use SQL Server Enterprise edition data compression - you just need to be sure every (large) table has a clustered index, then ALTER INDEX ALL with DATA_COMPRESSION = PAGE.
If you do use NTFS compression, use something like http://www.piriform.com/defraggler so you can see not only how many fragments each file in your filegroup is in, but how scattered they are across your disk (assuming contiguous, dedicated, unshared spindles at a SCSI/SAS/SATA/Virtual host/SAN level). NTFS compression is a fiend for fragmenting database files. Once they're read only, they shouldn't change anymore, but you may still want to defragment them, and again if you ever add data to them.
The last NTFS compressed database files I saw were in more than a hundred thousand fragments on the disk for only a few tens of GB of data. Performance was absolutely terrible, unsurprisingly.
February 27, 2014 at 8:18 am
I tested the process quoted by you. It worked fine but the problem is now I can also insert data into read only files.
select name,is_read_only from sys.filegroups
PRIMARY 0
FGDayRange011
FGDayRange021
FGDayRange031
FGDayRange041
FGDayRange050
FGDayRange060
Before Inserts:
total records Partitionnumber
450 3
550 6
50 1
450 4
500 5
450 2
After Inserts:
Total RecordsPartition Number
500 3
600 6
50 1
500 4
550 5
500 2
Currently the clustered primary index is created on file group FGDayRange06. I am using the below query to get the total count---
SELECT COUNT(*) AS [Total Records], $Partition.pfFiveDayRange(businessDate) AS [Partition Number]
FROM MSSQLTIPS_Partitions
GROUP BY $Partition.pfFiveDayRange(businessDate);
I am completely bowled by the above scenario. What I am missing? Any suggestions?
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply