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 Wednesday, January 22, 2014 9:13 AM


SSC-Dedicated

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

Group: Administrators
Last Login: Yesterday @ 3:05 PM
Points: 31,284, Visits: 15,750
Let me dig around.








Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1533709
Posted Wednesday, January 22, 2014 9:16 AM


SSC-Dedicated

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

Group: Administrators
Last Login: Yesterday @ 3:05 PM
Points: 31,284, Visits: 15,750
Aha: http://www.sqlservercentral.com/Forums/Topic1511837-391-1.aspx







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1533710
Posted Wednesday, January 22, 2014 9:31 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:21 AM
Points: 6,643, Visits: 14,230
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"
Post #1533724
Posted Thursday, January 23, 2014 8:39 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 2:10 PM
Points: 889, Visits: 2,460
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.
Post #1534140
Posted Thursday, February 27, 2014 8:18 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 7:33 AM
Points: 414, Visits: 2,087

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
FGDayRange01 1
FGDayRange02 1
FGDayRange03 1
FGDayRange04 1
FGDayRange05 0
FGDayRange06 0

Before Inserts:

total records Partitionnumber
450 3
550 6
50 1
450 4
500 5
450 2

After Inserts:

Total Records Partition 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?
Post #1545915
Posted Thursday, February 27, 2014 10:24 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 7:33 AM
Points: 414, Visits: 2,087
Any suggestions ???
Post #1545996
Posted Thursday, February 27, 2014 12:28 PM


SSC-Dedicated

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

Group: Administrators
Last Login: Yesterday @ 3:05 PM
Points: 31,284, Visits: 15,750
Are you saying you can add data to the partitions that are marked as read-only in the filegroups? It's possible there is a bug here wtih the partitions that doesn't respect the filegroup status.

However a trigger that rejects inserts into those ranges might help.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1546072
Posted Thursday, February 27, 2014 1:20 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 7:33 AM
Points: 414, Visits: 2,087
Steve Jones - SSC Editor (2/27/2014)
Are you saying you can add data to the partitions that are marked as read-only in the filegroups? It's possible there is a bug here wtih the partitions that doesn't respect the filegroup status.

However a trigger that rejects inserts into those ranges might help.


YES. I can insert data into read only files. Can you provide more information or any kb article details? Also I am not familiar on how to create triggers on ranges. Thanks...
Post #1546102
Posted Thursday, February 27, 2014 1:42 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 8:55 PM
Points: 35,618, Visits: 32,214
muthyala_51 (1/22/2014)
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.


My recommendation would be to make one of the file groups Read-Write, run REORGANIZE on all indexes, and then see how much free space (not data, not index) there is.

Also, when you set the individual file groups to Read-Only, are you seting the "SINGLE_USER" mode of the database?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1546112
Posted Thursday, February 27, 2014 2:00 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 7:33 AM
Points: 414, Visits: 2,087
i am not making the database into single user mode. I just leave the latest partition to read write and change the rest of the filegroups to read only. As paritions are based on date column, all new rows will be inserted into the latest file group.
Post #1546122
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse