Not the usual shrinkfile question...

Question

Post reply

Not the usual shrinkfile question...

NJ-DBA

SSChampion

Points: 13834
More actions
September 2, 2010 at 7:14 pm

#141710

Before you think "dont shrink it, it's going to grow again!", hear me out...
Database contained tons of tables without clustered indexes. I dropped nonclustered and clustered indexes and recreated on new filegroups. Now primary file group is still huge, but with 50% free space (200GB file, 100GB free)...
I'd like to give that space back to the OS as the other files are likely to grow faster. I know that I can only shrink to the last allocated extent without creating fragmentation in the other tables still on that primary FG. How can I determine what objects are toward the end of the datafile and where the free space is to free that up...
do I just run shrinkfile and wait? seems to be taking ages....

Viewing 13 posts - 1 through 12 (of 12 total)

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

NJ-DBA SSChampion Points: 13834 More actions · Answer 1

NJ-DBA

SSChampion

Points: 13834

September 3, 2010 at 11:05 am

#1216303

still nothing huh?

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

Just shrinkfile and wait - but don't remove all free space.

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

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 3

Shrink the File with "Release Unused space" option which is the default option in the SSMS. Don't use Reorganize option which is the second one in SSMS.

Thank You,

Best Regards,

SQLBuddy

NJ-DBA SSChampion Points: 13834 More actions · Answer 4

never tried shrinking file through the gui.... I usually do "dbcc shrinkfile (filename, targetsize). Maybe I'm just not being patient enough.

NJ-DBA SSChampion Points: 13834 More actions · Answer 5

using GUI with "released unused space" finishes in seconds and makes no change. Maybe this is equivalent to "with truncateonly"... which also has no result. I think there is just some object at the end of the datafile.

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 6

You are absolutely right. In 95% of case it will not release space. What it does is just find the last used page in the file and chop the file at that point. If the used page is at the end of the file as you said it won't have much affect.

Thank You,

Best Regards,

SQLBuddy

NJ-DBA SSChampion Points: 13834 More actions · Answer 7

So my question then is this: lets say 90% of objects have been moved to another file group. and 1 object is the last in the datafile. what do i need to do to determine what object is there at the end of the datafile in order to move it to the front of the file (by reindexing) and allow a truncateonly to shrink the file down to something reasonable?

NJ-DBA SSChampion Points: 13834 More actions · Answer 8

I bet there is some query I can run against sys.allocation_units to tell me what's near the end of the file (?)

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 9

Try to use the second option in the SSMS i.e "Reorganize Pages before releasing Unused Space". This will de-fragment the file by moving all the used pages to the front of the file and then chop-off the file at the end of the last used page.

Here you can specify a % of free space required after moving the data to the beginning of the file. I.e if you specify 10% , then it will move all data to the front of the file and then leave a 10% of free space and then chops-off the remaining free space from the file and release it back to the OS.

NOTE: Do it only during the Off-Office hours i.e when there is no user activity.

Don't worry about finding the last page in the file.

Thank You,

Best Regards,

SQLBuddy

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 10

NJ-DBA (9/3/2010)
So my question then is this: lets say 90% of objects have been moved to another file group. and 1 object is the last in the datafile. what do i need to do to determine what object is there at the end of the datafile in order to move it to the front of the file (by reindexing) and allow a truncateonly to shrink the file down to something reasonable?

I searched for a long time to be able to do this specifically because of recovering BLOB extents via shrinks and never found an answer sufficient enough to actually be useful. Just let it re-org itself during a large maintenance window.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

magasvs SSCertifiable Points: 7660 More actions · Answer 11

Not sure if this is the best solution, but this is what I do when I have similar tasks:

1) add another datafile to the filegroup

2) shrink the first file with EMPTYFILE option. Data will be moved to the second file

3) remove data file # 1

NJ-DBA SSChampion Points: 13834 More actions · Answer 12

Yep- I've done the exact same thing on other situations- and that would be ideal- but I don't think there is enough room on the server for that.