||SQL Server Tips by Robin Schumacher
Avoiding Free Space Deficit
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
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:
-- Script is available in the Online Code
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