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.