Shrinking primary data file below initial size

I have recently migrated tables to their own filegroups and now the primary file group is too large. The initial size of the primary data file (.mdf) is 150GB. I want to reduce this to say 40GB.

DBCC SHRINKFILE doesn't work because it can't go below 150GB. I've done it with an emptyfile and it won't empty it because its the primary file. ALTER DATABASE doesn't work because the file isn't empty. It looks like I need to create a new database and transfer the data??

There must be another way, any suggestions SSC?

more ▼

asked Nov 18, 2009 at 06:33 PM in Default

avatar image

Vic G
11 1 1 3

Could you post the command you used with SHRINKFILE and the results you got? Thanks...

Nov 18, 2009 at 07:23 PM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

If you try to shrink it with the size specified as 0 or without a specifified size, then it will target that databases default which is what it was created at. However, if you specify your target size, like:

USE [test_size]
DBCC SHRINKFILE (N'test_size' , 40000)

Then it will come as close to your specified target as it can and should happily go below the size it was created with, and that will reset that databases default size. Of course, it will not go below what is actually needed to store the current data.

If this is not working for you, can you please provide the exact commands you are using and any messages?

more ▼

answered Nov 18, 2009 at 08:44 PM

avatar image

15.6k 22 55 38

That worked. Thank you.

Nov 19, 2009 at 04:26 AM Vic G

Vic, make sure you mark it by clicking on the tick!

Nov 19, 2009 at 07:10 AM Melvyn Harbour 1 ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Nov 18, 2009 at 06:33 PM

Seen: 2776 times

Last Updated: Nov 18, 2009 at 06:33 PM

Copyright 2018 Redgate Software. Privacy Policy