Shrink the database only upto its initial size which is set

Question

Shrink the database only upto its initial size which is set

IT researcher

SSCertifiable

Points: 7600
More actions
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?

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

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

e4d4 SSCertifiable Points: 5777 More actions · Answer 1

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

Loundy SSCertifiable Points: 6080 More actions · Answer 2

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]

RTaylor2208 SSChampion Points: 13298 More actions · Answer 3

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

e4d4 SSCertifiable Points: 5777 More actions · Answer 4

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."

IT researcher SSCertifiable Points: 7600 More actions · Answer 5

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.

Steve Jones - SSC Editor SSC Guru Points: 736222 More actions · Answer 6

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?

IT researcher SSCertifiable Points: 7600 More actions · Answer 7

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