SQL Server Performance

dbcc shrinkdatabase vs dbcc shrinkfile

Discussion in 'General DBA Questions' started by playafree, Feb 9, 2007.

  1. playafree New Member

    Hi,

    First off, i'm new to SQL but i'm kind of confused on the difference between these two commands.

    To all you gurus out there, please clarify this for me.. From what i'm reading from Microsoft, dbcc shrinkfile 'Shrinks the size of the specified data file or log file for the related database'. dbcc shrinkdatabase 'Shrinks the size of the data files in the specified database'.

    So would i use dbcc shrinkfile for TX log files only and dbcc shrinkdatabase for db files? The dbcc shrinkdatase seems self explanatory but doesn't dbcc shrinkfile perform the same tasks?

    Thanks
  2. ramkumar.mu New Member

    quote: So would i use dbcc shrinkfile for TX log files only and dbcc shrinkdatabase for db files?

    Shrinkdatabase shrinks the entire database. Entire database refers both log and data files.
    Shrinkfile too shrinks data files and logfiles but individually. What if you want to just free up some space over a particular drive? you can use Shrinkfile over shrinkdatabase

    Thanks,
    Ram

    "It is easy to write code for a spec and walk in water, provided, both are freezed..."
  3. thomas New Member

    dbcc shrinkfile is preferable, because you have more control over it. You can specify a target size you want to shrink the file to (as opposed to a target percent with shrinkdatabase).

    Shrinking data files should be avoided if possible as it causes fragmentation as the data is moved around to free up contiguous free space to delete. you should only do it if you have a very big chunk of free space you need to get rid of.
  4. playafree New Member

    Understood. Thanks to you all for your response.


    quote:Originally posted by thomas

    dbcc shrinkfile is preferable, because you have more control over it. You can specify a target size you want to shrink the file to (as opposed to a target percent with shrinkdatabase).

    Shrinking data files should be avoided if possible as it causes fragmentation as the data is moved around to free up contiguous free space to delete. you should only do it if you have a very big chunk of free space you need to get rid of.

  5. ciaotz New Member

    How do i know if i have a very BIG chunk of free space? i mean, how do i know if the data file can free up big space.

Share This Page