Post reply

Sql server 2005 Mdf File Shrinking on Windows environment

  • March 25, 2011 at 5:40 am

    #145324

    Hi All,

    I am newbie in MSSql 2005 and trying To shrink my mdf file. Currently Total mdf size is 43GB out of this 37 GB is free. i.e. 6GB data exist on mdf/database. So I want to ask some question based on my previous working on the same topic.

    1. Is there any fastest way to shrink mdf file other then dbcc shrinkdatabase, or graphically from shrink option using sql server managment studio.

    2. Other way I think that I will take backup of database and restore using this backup file. But again it will again create the destination file of same size as in source database. So I want to ask that is there any way to take backup of only data exist on mdf file. i.e. 6GB only. and restore only 6 gb which will create the 6 GB (approx) mdf file in result of that.

    Can some one solve my problem related to this.

    I have already tried to shrink database using above mention command and it is taking lots of time and consuming more space by logfile increament.

    Please tell me the easiest and effective way to complete this task.

    Thanks

    Pradeep Sharma

  • March 25, 2011 at 5:49 am

    #1303499

    DBCC ShrinkDatabase or DBCC shrinkFile are the only ways to shrink a database (other than autoshrink which should never, never, never be enabled)

    A restore always recreates the database exactly as it was at time of backup, file size included.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 25, 2011 at 5:58 am

    #1303504

    Hi,

    As per my situation can you please tell the exact command to shrink mdf file from 43GB to 8GB.

    Thanks

    Pradeep Sharma

  • March 25, 2011 at 6:02 am

    #1303506

    Did you look through Books Online? The syntax is clearly documented.

    DBCC SHRINKFILE (1, 8192)

    p.s. I don't memorise infrequently used commands and parameters, I had to check Books Online for that.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 25, 2011 at 6:08 am

    #1303510

    thanks

Viewing 5 posts - 1 through 4 (of 4 total)

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