Call now: (800) 766-1884  



 Home


 SQL Server Tips
 SQL Server Training

 SQL Server Consulting
 SQL Server Support
 SQL Server Remote DBA



 Articles
 Services
 SQL Server Scripts
 Scripts Menu



 

 

 

   
  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  

 

Burleson Consulting Remote DB Administration


 

 


 

 

 

 

 
Burleson is the America's Team

Note: The pages on this site were created as a support and training reference for use by our staff of DBA consultants.  If you find it confusing, please exit this page.

Errata?  SQL Server technology is changing and we strive to update our SQL Server support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:and include the URL for the page.
 


Burleson Consulting
SQL Server database support

 

Copyright © 1996 -  2013 by Vaaltech Web Services. All rights reserved.

Hit Counter