Unable to shrink primary data file after splitting to multiple secondary data files

Question

Post reply

Unable to shrink primary data file after splitting to multiple secondary data files

Alex Webber

SSCommitted

Points: 1543
More actions
May 19, 2010 at 3:37 am

#222709

We recently split our 400gb+ primary data file out into a bunch of logical secondary file groups to take advantage of a new disk pack. After achieving this we managed to shrink the primary data file down to 132gb, but there is only actually about 25gb of data in there (1 x 18gb table and some minor tables). If we run the following we get (file names obscured!):
SELECT
name AS [File],
CAST(size/(128.0 * 1024) AS INT) AS [Size/GB],
CAST(size/(128.0 * 1024) - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/(128.0 * 1024) AS INT) AS [Free/GB]
FROM sys.database_files;
File....................Size/GB......Free/GB
PrimaryFile..........132...........108
LogFile...............0..............0
SecondaryFile1....200...........64
SecondaryFile2....19............12
SecondaryFile3....222...........96
SecondaryFile4....158...........69
This all ties up when we query the primary file group to see what's on it with the 18gb table and little else. That 18gb table has very low internal fragmentation, although there is a fair amount of LOB_DATA. We need to be able to free up some/most of the 108gb of free space to move one of the secondary files onto the same disk. We have a replica dev environment and have tried pretty much everything. All I can think of is that there is some sort of lock being held somewhere that prevents this file from shrinking? Although it's not a massive problem like the primary file, some of these secondary files appear to have massive amounts of free space in them as well and can't be shrunk further. All tables on them also have very low levels of internal fragmentation.
All thoughts are welcome!
Cheers, Alex.

Viewing 11 posts - 1 through 10 (of 10 total)

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

Alex Webber SSCommitted Points: 1543 More actions · Answer 1

Just to add running DBCC CHECKALLOC ends up with:

The total number of extents = 6244877, used pages = 49737236, and reserved pages = 49956438 in this database.

(number of mixed extents = 1006, mixed pages = 5470) in this database.

So translating to size:

Total allocation:....381.157gb

Total reserved:.....381.137gb

Total used:..........379.465gb

Total unused:..........1.672gb

Which tallies with the used space leaving that whopping 349gb space that we can't appear to free up 🙁

Stefan_G SSCertifiable Points: 6609 More actions · Answer 2

Exactly how are you trying to shrink the files ?

Are you perhaps using the GUI ?

Try giving the command directly:

DBCC SHRINKFILE (filename,1)

Did that work ?

If not, what was the result set of the DBCC SHRINKFILE command ?

Alex Webber SSCommitted Points: 1543 More actions · Answer 3

Hey Stefan. Yes DBCC SHRINKFILE'ing. It just won't drop that space.

I was thinking of creating a new secondary file, adding it to the same filegroup as the primary data file and then running a DBCC SHRINKFILE with EMPTYFILE on the primary file and then if the space is reclaimed going back the other way. Any thoughts? Cheers, Alex.

DbIdFileIdCurrentSizeMinimumSizeUsedPagesEstimatedPages

8......1.......2881968....384....................2863952.....2863952

EDIT: rofl! Just as I posted this I realised it has now shrunk! Although this is in dev environment and I don't know how I managed to so I can't replicate it on live! arrgghh!!

Alex Webber SSCommitted Points: 1543 More actions · Answer 4

Just thinking about it. I did offline then online the database. Maybe this caused some lock preventing sql server from shrinking the file? I recall reading something about that somewhere and that an offline->online was the only way to remove it?

Stefan_G SSCertifiable Points: 6609 More actions · Answer 5

Stefan_G

SSCertifiable

Points: 6609

May 19, 2010 at 10:19 am

#1169580

Weird. Glad you found a solution. 🙂

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

Another possible thing to try - increase the file size first then try to shrink.

Also, try an index defrag. Sometimes that will make a difference.

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

Alex Webber SSCommitted Points: 1543 More actions · Answer 7

Me too Stefan, although I'm still not 100% sure what it was! Guess I'll restore a local backup tomorrow and replay the SET OFFLINE -> SET ONLINE.

Thanks CirquedeSQLeil, I already tried both the things you suggested. Rebuilding every index was my first port of call 🙂

If the SET OFFLINE -> SET ONLINE works I'll post that as a possible solution to the problem scenario. Also I'd imagine it's probably worth submitting it as a potential bug to the SQL Server team.

Alex Webber SSCommitted Points: 1543 More actions · Answer 8

Trying to replay the SET OFFLINE -> SET ONLINE didn't work second time round! aarrggh!

Back to getting the following from SHRINKFILE

DbIdFileIdCurrentSizeMinimumSize UsedPages EstimatedPages

8.......1......17363872....384............2888816....2885160

132gb of current size vs 22gb of actual usage!

Stefan_G SSCertifiable Points: 6609 More actions · Answer 9

Sorry, I have no more ideas.

Maybe time to let Microsoft look at this?

Nagesh S-432384 SSCrazy Points: 2231 More actions · Answer 10

Take a backup and then shrink file, hope this might work for you.

Nag

Nag
------------------------------------------------
How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537