Howtos / Articles

Automatically shrink database files in Microsoft SQL 2008

Microsoft SQL server 2008 has the ability to automatically shrink database files. By default this option is disabled. With automatic shrinking of database files turned on, the SQL server will periodically check the available free space within the database files, and will automatically shrink the files if needed.

If your database has heavy use, it might be best to keep this option disabled to prevent performace degration.

To check what the current auto shrink setting:

SELECT DATABASEPROPERTYEX('<database_name>', 'IsAutoShrink')

Replace <database_name> with the name of your database.

This will output a 1 if auto shrink is enabled, or a 0 if it is disabled.

To enable automatic file shrinking:

ALTER DATABASE <database_name>
SET AUTO_SHRINK ON

Replace <database_name> with the name of your database.

To disable automatic file shrinking:

ALTER DATABASE <database_name>
SET AUTO_SHRINK OFF

Replace <database_name> with the name of your database.



Share this howto article!
  • Facebook
  • Twitter
  • Reddit
  • email

Leave a Reply

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>