Post reply

Shrink the database only upto its initial size which is set

  • September 3, 2013 at 12:21 am

    #279214

    I have some database which are already created which are already created with default initial size. But i want to reset the initial size of these. I did it using SSMS.

    But now if i shrink the database it will shrink beyond the initial size i have set.It will shrink till the initial size it had during creation of database. But i don't want it. I was expecting to shrink till the initial size which i have set.So please suggest me how it can be done?

  • September 3, 2013 at 1:28 am

    #1646889

    Use DBCC SHRINKFILE http://technet.microsoft.com/en-us/library/ms189493.aspx or in SMSS choose to shrink a file not a DB.

  • September 3, 2013 at 6:20 am

    #1646996

    Shrinking dbs are never a nice task for many reasons - if you absolutely have to do it remember to run your reindexing/update stats afterwards....

    [font="Times New Roman"]There's no kill switch on awesome![/font]

  • September 3, 2013 at 6:30 am

    #1647002

    You cannot shrink the files to smaller than when they were initially created. Shrinking the files will only shrink down to the inital creation sizes if there is space available.

    MCITP SQL 2005, MCSA SQL 2012

  • September 3, 2013 at 7:04 am

    #1647020

    RTaylor2208 (9/3/2013)

    You cannot shrink the files to smaller than when they were initially created. Shrinking the files will only shrink down to the inital creation sizes if there is space available.

    Yes, you can.

    http://technet.microsoft.com/en-us/library/ms189493.aspx "You can shrink a file to a size that is less than the size specified when it was created. This resets the minimum file size to the new value. "

    http://technet.microsoft.com/en-us/library/ms190488.aspx "The database cannot be made smaller than the minimum size of the database. The minimum size is the size specified when the database is originally created, or the last size explicitly set by using a file size changing operation such as DBCC SHRINKFILE or ALTER DATABASE."

  • September 3, 2013 at 8:19 am

    #1647047

    I want to shrink the database to initial size which i have set for mdf and ldf file. I have set initial size for the database after creating the database.It was 3 mb for mdf by deafult. I changed it to 100 mb. So how it can be done?

    Now if i use shrink file option then it shrink back to 3 mb which i don't need.I want it to shrink to 100 mb.

  • September 3, 2013 at 8:38 am

    #1647055

    You can shrink it by specifying a size. Note that this might fragment your indexes quite a bit, and you'll need to rebuild them. If you don't have enough space to do that without growing the file, you'll grow again.

    Is there some reason that you want to get back to 100MB? Where is it now?

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • September 25, 2013 at 11:46 pm

    #1653415

    To avoid lot of auto-growth i have set the initial size to 100 mb based on the size and content of database.

Viewing 8 posts - 1 through 7 (of 7 total)

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