Post reply

Shrink database to only up to its current initial size

  • January 16, 2014 at 3:26 am

    #284456

    In SQL server every database has a property Initial Size (MB) which can be seen in properties of database in SSMS. By default it will be 3 MB for mdf and 1 MB for ldf file.

    So now if create a new database then it will be set to default size(i.e 3 MB for mdf and 1 mb for ldf file). But after creating the database I change the initial size to some other value say For mdf 10 MB and ldf 5 MB.(In real-time some database administrator may want to change initial after creating database)

    But now if shrink the database it will shrink beyond the initial size I have set after creation(consider that no data is there in database otherwise it will shrink to its actual content size). It will shrink till the initial size it had during creation of database(i.e 3mb for mdf and 1 mb for ldf). I was expecting to shrink till the initial size which I have set(i.e 10 mb for mdf and 5mb for ldf). Can it be possible to do? If possible then how?

    In some article i saw like dbcc SHRINKFILE can be used to shrink beyond initial size and dbcc SHRINKDATABASE cannot shrink beyond initial size. But I want to shrink only till the initial size to which I have set.

    Note: I know that shrink is bad and should not be done.But I want to know how it can be done?

  • January 16, 2014 at 5:01 am

    #1680597

    Initial size in SSMS is somewhat of a misleading term. for a user database it changes in SSMS when the file size changes, for tempdb it does not.

    The DB only has one initial size in reality, the size it was first created with. This is the size relevant to the shrinkfile operation

    ---------------------------------------------------------------------

  • January 16, 2014 at 11:07 pm

    #1680961

    The DB only has one initial size in reality, the size it was first created with. This is the size relevant to the shrinkfile operation

    So can i change the size which is relevant to shrinkfile? (not by specifying size in shrinkfile command)

  • January 17, 2014 at 3:13 am

    #1680998

    if you use shrinkfile rather than shrinkdatabase you can specify whatever size you want. Limitation is amount of data in data file and whatever the size of your first 2 VLFs is equal to.

    ---------------------------------------------------------------------

  • January 20, 2014 at 11:41 pm

    #1681734

    Also a nice explanation here for my question.

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

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