Managing SQL Server Disk Usage

Some uses of SQL Server can cause databases and transaction log files to grow larger than intended. There are some tools available to system administrators and DBA’s to help manage file sizes for SQL Server. Here are some quick notes.

  • View or Configure the backup compression default Server Configuration Option – Enable backup compression by default to shrink the size of backup files created by SQL Server. NOTE that this will increase the load on your SQL Server system when a backup file is being created.
  • Shrinking the Transaction Log – Consider shrinking the transaction log files periodically for databases that log a large amount of transactions. This can be automated with a SQL Maintenance Plan or through Windows Task Scheduler.
  • If a database file has 25% (1/4) or more free space, it may be a good candidate to shrink down to 12.5% (1/8) free space. This should be run manually if needed – Microsoft warns against automated or recurring database shrink operations (normally databases are growing not shrinking).
  • Tune the Autogrowth settings for large database and transaction log files. The defaults for this setting may vary depending on which version of SQL Server you have installed. SQL 2008 R2 seems to default data file growth to 1MB unlimited, and transaction log file file growth to 10% unlimited. You may want to use a fixed MB size growth for both data and log files and perhaps set a reasonable maximum to prevent the files from filling up the entire filesystem where they are stored? Specific values to use should be determined based on your estimated data growth needs over time – maybe use your approximate weekly or monthly data need? You can set different system-default values for autogrow by modifying the options within the “model” system database, but it may be best to leave the manufacturer default alone here. NOTE that “model” options are used ONLY when creating NEW databases.
Advertisements

About notesbytom

Keeping technology notes on WordPress.com to free up my mind to solve new problems rather than figuring out the same ones repeatedly :-).
This entry was posted in Database and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s