After shrinking file it regrow again.

Question

After shrinking file it regrow again.

balbirsinghsodhi

SSCrazy Eights

Points: 9210
More actions
March 29, 2010 at 11:47 am

#142213

I shrunk the file using following command but it regrow again.
DBCC shrinkfile(file_capx1,1).
It was 149 GB before and I checked the Used % and it was 50% used. I shrunk the file and it reduces to 47GB but when I checked other day it increased to 80 GB. I know it should not grow to 80 GB because it is a old partion group and the value ranges are very old and I am pretty sure that value never inserted into that range. In other word, The filegroups should not be updated..if this filegroup never inserted and update then it should not grow.
My question is if this filegroup is not updated then how come it grow again to 80GB.
The primary keys are on this file groups but secoundary indexes are on separate index file group.
I am trying to shrink old filegroups to get more space because these filegroups are taking huge space but half of the percentage is being used.

Viewing 7 posts - 1 through 6 (of 6 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

If they are growing, it is because it needs to grow. You may not be inserting any data, but by shrinking the file you fragment the indexes. I know you said the indexes are on another file group, but if there is a clustered index, it is on this file group. If, after shrinking the file, you run an index rebuild on the clustered index, this could easily cause the file to grow.

balbirsinghsodhi SSCrazy Eights Points: 9210 More actions · Answer 2

Thanks for your reply.

I know it should grow by little because of the PK index. This table is a partition table and PK indexes are distributed in so many file groups but how come only this filegroup grow from 47 GB to 97 GB.

also I changed the filegrowth option to 10 MB because I know that data will not grow in this file group, yes PK index will grow but not that much.

Do you know how PK indexes distributed to different filegroups. we know that data is distributed based on the date range but how PK index gets distributed to different filegroup.?

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

Did you partition your indexes as well (specifically the clustered index)?

http://msdn.microsoft.com/en-us/library/ms345146(SQL.90).aspx

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

balbirsinghsodhi (3/29/2010)
Thanks for your reply.
I know it should grow by little because of the PK index. This table is a partition table and PK indexes are distributed in so many file groups but how come only this filegroup grow from 47 GB to 97 GB.
also I changed the filegrowth option to 10 MB because I know that data will not grow in this file group, yes PK index will grow but not that much.
Do you know how PK indexes distributed to different filegroups. we know that data is distributed based on the date range but how PK index gets distributed to different filegroup.?

Can't really answer this question without seeing how you created the table partitions, partition schemes, partition function, and the indexes.

balbirsinghsodhi SSCrazy Eights Points: 9210 More actions · Answer 5

I assume it is partition because PK indexes are on the schema. here is the example. I think if it is on the schema then it means it is partitioned.

ALTER TABLE [dbo].[BTCTXN] ADD CONSTRAINT [pk_BTCTXN] PRIMARY KEY CLUSTERED

(

[GROUP_ZZ] ASC,

[BATCH] ASC,

[BIDIV10] ASC,

[BINUM] ASC,

[POST_PD] ASC

) ON [PFBTCScheme]([POST_PD])

balbirsinghsodhi SSCrazy Eights Points: 9210 More actions · Answer 6

and also the schema is on so many filegroups. Here is the example.

CREATE PARTITION SCHEME [PFBTCScheme] AS PARTITION [PFBTCFunction] TO ([FILEGROUP_BTC1], [FILEGROUP_BTC5], [FILEGROUP_BTC9], [FILEGROUP_BTC13], [FILEGROUP_BTC15], [FILEGROUP_BTC16], [FILEGROUP_BTC17], [FILEGROUP_BTC18], [FILEGROUP_BTC19], [FILEGROUP_BTC14])