compressing read only file groups

Question

Post reply

compressing read only file groups

Viewing 8 posts - 16 through 22 (of 22 total)

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

Dhruva_51 SSChampion Points: 10521 More actions · Answer 1

Dhruva_51

SSChampion

Points: 10521

February 27, 2014 at 10:24 am

#1692776

Any suggestions ???

Steve Jones - SSC Editor SSC Guru Points: 736455 More actions · Answer 2

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.

Dhruva_51 SSChampion Points: 10521 More actions · Answer 3

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...

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 4

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Dhruva_51 SSChampion Points: 10521 More actions · Answer 5

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.

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 6

muthyala_51 (2/27/2014)
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.

That part of what I'm talking about. Last I heard, you had to set a database to "Single User" in order to be able to set a related file group to Read Only unless, of course, you're the only one in the database to begin with. Yeah... you can change it back Multi-User but I was just wondering how you set file groups to read only only without going through a pass through "SINGLE_USER".

Shifting gears back to your partitioning problem... it sounds like your table is actually a type of audit table table because you're setting partitions to Read Only based on a date. Is the date column, by any chance, a part of your PK?

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 736455 More actions · Answer 7

You don't write a trigger based on a range or partition. You write it based on the table.

If the read only groups are 3 months old, then your trigger just has something like

if datePKcolumn < dateadd( mm, -3, getdate()) then

rollback

Dhruva_51 SSChampion Points: 10521 More actions · Answer 8

Jeff Moden (2/27/2014)
muthyala_51 (2/27/2014)
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.
That part of what I'm talking about. Last I heard, you had to set a database to "Single User" in order to be able to set a related file group to Read Only unless, of course, you're the only one in the database to begin with. Yeah... you can change it back Multi-User but I was just wondering how you set file groups to read only only without going through a pass through "SINGLE_USER".
Shifting gears back to your partitioning problem... it sounds like your table is actually a type of audit table table because you're setting partitions to Read Only based on a date. Is the date column, by any chance, a part of your PK?

Yes, that's kind of an audit table with datetime part of PK. I was only the one who was accessing the database at the time of converting filegroups to read only mode, so it was easy for me to convert them without any errors.