Data file not shrinking

• Casper101

  Hall of Fame

  Points: 3903

  More actions

  December 6, 2010 at 5:26 am

  #232151

  I brought a copy of our live DB down to our DEV server and have deleted a lot of data. This now leaves the data file with a size of 85 GB and 37 GB is unused space.

  I have tried DBCC SHRINKFILE on the mdf file, but i can not shrink a file to a size smaller then the initial size (which is 85 GB).

  How can I reduce the initial size?

  I tried:

  ALTER DATABASE DB_Name MODIFY FILE (Name = N'data_file', SIZE=50000MB)

  but that gives me an error: "MODIFY FILE failed. Specified size is less than or equal to current size".

  I have tried the GUI: Right click DB - Properties - Files - Reducing the Initial size, but no luck.

  What are my options here? I need to use that space on our DEV server so I need to free up that space....

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736478

  More actions

  December 6, 2010 at 8:06 am

  #1259043

  Create a new db

  copy the current one over there, moving all the schema and data.

• shump66@yahoo.com

  Right there with Babe

  Points: 791

  More actions

  December 6, 2010 at 8:48 am

  #1259072

  Give this a try

  DBCC SHRINKFILE (N'Test001' , 1000)

  the 1000 is the size MB can not be smaller then used space

  Scott
  I am a Senior Data Analyst/DBA
  I work with MS SQL, Oracle & several ETL Tools

  @thewolf66[/url]