Shrink data file even though it is a bad practice....

Question

Post reply

Shrink data file even though it is a bad practice....

Chitown

Hall of Fame

Points: 3390
More actions
March 11, 2016 at 11:52 am

#311985

I am wondering if I can get some help with the script. I have a DB with more than 550+ data files (Of course they are all different sizes). I am trying to shrink all the files down to 80%. I have the script to generate the DBCC shrink command but I am still struggling with shrinking it to certain %. Can I please get some help?
Drop table #TEST
CREATE TABLE #TEST
(SQLQUERY VARCHAR (1000))
INSERT INTO #TEST SELECT 'USE [' + d.name + N']' + CHAR(13) + CHAR(10)
+ 'DBCC SHRINKFILE (N''' + mf.name + N''' 0)'+CHAR(13)+'GO'
FROM sys.master_files mf
JOIN sys.databases d
ON mf.database_id=d.database_id
WHERE d.database_id>4 and mf.type_desc = 'ROWS'
Select * from #TEST

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

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

Jon.Morisi SSChampion Points: 12846 More actions · Answer 1

...I am trying to shrink all the data files down to 80%...

Warning. Incoming thrashing. Prepare for impact!

Alexander Suprun SSCertifiable Points: 6253 More actions · Answer 2

I'm not sure what are you struggling with exactly. Do you know how to calculate percentage value?

Alex Suprun

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

Chitown (3/11/2016)
I am wondering if I can get some help with the script. I have a DB with more than 550+ data files (Of course they are all different sizes). I am trying to shrink all the files down to 80%. I have the script to generate the DBCC shrink command but I am still struggling with shrinking it to certain %. Can I please get some help?
Drop table #TEST
CREATE TABLE #TEST
(SQLQUERY VARCHAR (1000))
INSERT INTO #TEST SELECT 'USE [' + d.name + N']' + CHAR(13) + CHAR(10)
+ 'DBCC SHRINKFILE (N''' + mf.name + N''' 0)'+CHAR(13)+'GO'
FROM sys.master_files mf
JOIN sys.databases d
ON mf.database_id=d.database_id
WHERE d.database_id>4 and mf.type_desc = 'ROWS'
Select * from #TEST

Is there actually any free space available in those files? I know you probably know but have to say it out loud... shrink doesn't do compression.

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

Jon.Morisi SSChampion Points: 12846 More actions · Answer 4

Have you considered limiting what you're going to do to truncate only?

DBCC SHRINKFILE (file_name , TRUNCATEONLY)

Ed Wagner SSC Guru Points: 287026 More actions · Answer 5

I hope you're planning on doing some serious index maintenance after all this shrinking. Shrinking data files tends to fragment indexes to 99%, so it will be necessary.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

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

Ed Wagner (3/11/2016)
I hope you're planning on doing some serious index maintenance after all this shrinking. Shrinking data files tends to fragment indexes to 99%, so it will be necessary.

And, as a reminder, when you do that, the free space in the file group will expand to at least the size of the largest index you have, which is usually the clustered index of the largest table you have. I say "at least" because there's usually also a 20% or so "extra charge" depending on where the sort is done and whether or not it's done in an ONLINE or OFFLINE fashion.

There is a "trick" that can be done for full reindexing of even the clustered indexes to remove virtually all unnecessary free space from file groups before making them READ_ONLY (particularly useful for partitioned temporal WORM tables) and could possibly done in this case but, although not difficult, it IS complicated.

Are such extraordinary measures warranted in this case? If the file groups aren't going to be set to READ_ONLY, then probably not as any index rebuild maintenance (not reorg) will cause the file group to grow for any index over 128 extents (8 mb) as will any index page splits.

Of course, the OP still hasn't responded as to whether or not there is actually any free space available in the file groups nor what "80%" actually means here.

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

Ed Wagner SSC Guru Points: 287026 More actions · Answer 7

Jeff Moden (3/12/2016)
Ed Wagner (3/11/2016)
I hope you're planning on doing some serious index maintenance after all this shrinking. Shrinking data files tends to fragment indexes to 99%, so it will be necessary.
And, as a reminder, when you do that, the free space in the file group will expand to at least the size of the largest index you have, which is usually the clustered index of the largest table you have. I say "at least" because there's usually also a 20% or so "extra charge" depending on where the sort is done and whether or not it's done in an ONLINE or OFFLINE fashion..

Heh - I had something similar originally, but I removed it before posting. The surcharge of rebuilds is data file space and the surcharge or reorganizations is transaction log space. If space is what the OP is trying to save, they're going to end up paying for it in the end.

Now, if that surcharge of space is levied during the index maintenance that occurs immediately after the shrink or after the users complain about horrible performance and index maintenance is done later is up the OP. Either way, the surcharge must be paid or the users will have to live with performance problems.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions