SQL Server Performance

Auto Shrink

Discussion in 'General DBA Questions' started by TerryCrosby, Dec 13, 2002.

  1. TerryCrosby New Member

    While this is turned on for several of my databases, it doesn't seem to do anything. Am I missing something? To get my databases to shrink, it seems I must issue DBCC ShrinkFile. All replies would be appreciated. TIA.

    Terry Crosby
  2. bradmcgehee New Member

    Shrinking databases has always been a weakness of SQL Server (all version). And in many cases, your only choice may be to use DBCC ShrinkFile. This, unfortunately, is fairly normal behavior you are seeing.

    On the other hand, from a performance perspective, you should not turn on auto shrink for any database. This is because this feature uses unnecessary overhead and can hurt the overall performance of your server.

    If you need to shrink files, do it manually as needed.


    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com
  3. satya Moderator

    For log files, SQL Server uses target_size to calculate the target size for the entire log; therefore, target_size is the amount of free space in the log after the shrink operation. Target size for the entire log is then translated to target size for each log file. Shrinking a log is dependent on first truncating the log.

    BOL has got better explanation in this regard, refer to it.

    HTH

    Satya SKJ

Share This Page