SQL Server Performance

Change initial datafile size

Discussion in 'Getting Started' started by reethu, Aug 14, 2008.

  1. reethu New Member

    Initially we have created all databases with initial size of 1 gb and now we are rethinking to take the size back to 250 mb
    is that possible to take the initial size back ,if so what is best and easy procedure or script to do it .
    if any one knows help out.
  2. preethi Member

    If you have not resized the Model database, you can simply shrink the user databases.
    Otherwise, you need to shrink Model database first.
    If you want to specify the size for data files only, go through the option of Shrink file.
  3. reethu New Member

    i understand, it can be done database by database but i',m thingking to do it by script or procedure
    because i have databases like more than 100,
    so it takes too much long time and pain, if you have other ideas doing by script
    please provide.
  4. preethi Member

    [quote user="reethu"]
    because i have databases like more than 100,
    Can you try something like this?

    sp_MsForEachDB 'USE ?; IF DB_ID() >4 DBCC shrinkfile (1, 250)'
    Note: You may have to add some more conditions to exclude any database if you want to.
  5. reethu New Member

    it works perfectly to me
    But two small clarification. when i apply this command
    got two quastions :: 1. what if data is more than 205 mb 2.what if size is already lessthan that specified .
    i think it happens when above conditions meet . DBCC Cannot shrink to behind data in it.
    2 ans:: it wont affect the less size it leaves as it is.
    are they correct if not give me the correction.
    Thanks again.
  6. preethi Member

    DBCC ShrinkFile will remove the free space only.
    This is what I get from books online:
    If target_size is specified, DBCC SHRINKFILE tries to shrink the file to the specified size. Used pages in the part of the file to be freed are relocated to available free space in the part of the file retained. For example, if there is a 10-MB data file, a DBCC SHRINKFILE operations with a target_size of 8 causes all used pages in the last 2 MB of the file to be reallocated into any unallocated pages in the first 8 MB of the file. DBCC SHRINKFILE does not shrink a file past the size needed to store the data in the file. For example, if 7 MB of a 10-MB data file is used, a DBCC SHRINKFILE statement with a target_size of 6 shrinks the file to only 7 MB, not 6 MB.
    Hope this helps
  7. satya Moderator

    HOw are you creating the databases, using a script?
    If so then simply change the value on that script , otherwise follow as Preethi referred.

Share This Page