x

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

Vic G gravatar image

Vic G
11 1 1 1

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 oldest

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]
GO
DBCC SHRINKFILE (N'test_size' , 40000)
GO

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

TimothyAWiseman gravatar image

TimothyAWiseman
15.6k 21 23 32

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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x38

asked: Nov 18, 2009 at 06:33 PM

Seen: 2259 times

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