SQL Server Tips by Robin Schumacher

Avoiding Free Space Deficit Bottlenecks

An SQL Server administrator will want to prevent all database free space deficits from occurring and will also want to know how to quickly fix a space deficit if one does indeed crop up. What sort of strategy and tactics should be used?

In terms of proactive actions, it is a good idea to have autogrowth turned on for dynamically growing databases. This puts the burden on the SQL Server’s back in terms of ensuring free space deficits do not occur for the database. If any of the databases do not have autogrowth set, this can easily be changed by altering the database and setting the filegrowth and maxsize properties for one or more files.

If the phone is ringing with reports of a space outage, there are a couple of things that can be done quickly to rectify the situation. The autogrowth for the problem database can be enabled, allowing the SQL Server to take control. Or, one or more database/log files can be permanently enlarged by altering the modify file clause, which allows the file size to be dynamically changed providing there is enough free space on the disk. If a space deficit at the server level prevents this action, a new file on a different drive can be added to the database and the database enlarged in that fashion.

Autogrowth should not be used as a replacement for properly sizing a database. Each time the SQL Server enlarges a database file, a small performance penalty will be encountered, so it is wise to allocate the proper amount of space in the beginning.

While a simple query is not available to determine if data files are expanding, the following query can be used to check on any dynamic log file extensions:

log_expand.sql

-- Script is available in the Online Code Depot

In addition to applying all the principles discussed for preventing database free space deficits, there are a couple of other options available for database transaction logs.

If point-in-time recovery is not necessary for a database, the truncate log on the checkpoint option or simple recovery mode can be enabled. This tells SQL Server to flush the log of any committed transactions when a checkpoint is performed. Enabling this option is not a perfect safety net because a single long running transaction can still cause a log to fill completely up, since the truncate only flushes committed work.

If point-in-time recovery is needed for the databases, the smartest thing is to put a log backup maintenance plan in place that periodically backs up and truncates the log. This course of action provides good recovery capability for the database and depending on the backup frequency can help stop log free space deficits.

The above book excerpt is from:

High-Performance SQL Server DBA
Tuning & Optimization Secrets

ISBN: 0-9761573-6-5
Robin Schumacher

 http://www.rampant-books.com/book_2005_2_sql_server_dba.htm