locked
shrink db RRS feed

  • Question

  • is there any overhead in using shrink "file to option"  in the shrink Database. what difference does this do from the regular shrink(i e compressing pages)

    Thnks in advance.
    Tuesday, May 19, 2009 8:10 PM

Answers

  • Hi,

    The "Shrink file to" option is equivalent to executing DBCC SHRINKFILE with the target_size option. However, this option is different with the row and page compression. Enabling compression changes the physical storage format of the data to the reduce storage cost. The "Shrink file to" option attempts to shrink the specified file to the specified size in megabyte, and any free space beyond that is returned to the operation system. In addition, the specified size cannot be less than the current allocated space or more than the total extents allocated to the file. If you enter a valued beyond the min or the max, the value will revert to the min or the max.

    If you have any more questions, please let me know.   

    Thanks.


    ***Xiao Min Tan***Microsoft Online Community***
    Thursday, May 21, 2009 7:00 AM

All replies

  • Hi

    Shrink file - Shrinks the size of the specified data file or log file for the related database.

    Shrink DB - Shrinks the size of the data files in the specified database.

    The database being shrunk does not have to be in single-user mode; other users can be working in the database when the file is shrunk. You do not have to run the instance of SQL Server 2005 in single-user mode to shrink the system databases.

    Note: DBCC SHRINKDATABASE and DBCC SHRINKFILE operations can be stopped at any point in the process, and any completed work is retained.


    Karumajji
    Tuesday, May 19, 2009 8:18 PM
  • on the gui , there is an option called shrink file to, where we can specify the size limit to which it can be shrunk, any difference in this options operation

    thnks for the reply
    Tuesday, May 19, 2009 8:22 PM
  • You  can specifiy which file(MDF or LDF) you want to shrink and how much % you want to shrink


    Karumajji
    Tuesday, May 19, 2009 8:25 PM
  • Hi,

    The "Shrink file to" option is equivalent to executing DBCC SHRINKFILE with the target_size option. However, this option is different with the row and page compression. Enabling compression changes the physical storage format of the data to the reduce storage cost. The "Shrink file to" option attempts to shrink the specified file to the specified size in megabyte, and any free space beyond that is returned to the operation system. In addition, the specified size cannot be less than the current allocated space or more than the total extents allocated to the file. If you enter a valued beyond the min or the max, the value will revert to the min or the max.

    If you have any more questions, please let me know.   

    Thanks.


    ***Xiao Min Tan***Microsoft Online Community***
    Thursday, May 21, 2009 7:00 AM