Post reply

Shrink mdf file

  • November 18, 2009 at 1:24 am

    #1080726

    Yes - I was able to shrink the file by using the dbcc shrinkfile command with [emptyfile] paramater. This shrinks the file by moving the data to the .NDF file. You can also do this via SQL Management Studio by right clicking the database then going to Tasks then shrink file. This will bring up the shrink file dialogue box. You would then check the option [Empty file by migrating the data to other files in the same filegroup]

    Before doing this you would need to create a file (.NDF) in the same filegroup

  • November 18, 2009 at 12:19 pm

    #1081119

    Thank you. I did it on a test db it's working.

  • November 18, 2009 at 12:44 pm

    #1081147

    Thanks for the update on what finally worked for you in this scenario.

    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

  • November 18, 2009 at 1:35 pm

    #1081184

    Created .ndf, empty'd .mdf [dbcc shrinkfile (1, emptyfile)], shrunk .mdf, empty'd .ndf so it moved back data to .mdf, then remove .ndf.

  • May 28, 2010 at 11:59 am

    #1174084

    Hi,

    I have run into the same issue where I cant shrink the file past the InitialSize mentioned. I have the mdf file of 96 gb which has free space of 45Gb. I have tried creating a empty file in the test environment and tried to empty the mdf file using shrinkfile, but get this error "Cannot move all contents of file "DB_Data" to other places to complete the emptyfile operation". Could you please tell me how to go about shrinking the file?

    Thanks,

    Deepti

  • June 1, 2010 at 8:04 am

    #1174775

    To move .mdf data to ndf, you must have atleast 120% (for safer side) of .mdf 's size on .ndf

Viewing 6 posts - 16 through 20 (of 20 total)

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