DBAs must monitor their SQL Servers carefully to ensure that the system and user databases have sufficient disk space for the life time of the applications using these SQL Servers. Failure to do so can result in the following problems:
[TABLE]
[TABLE]
SQL Server's logs and database files grow according to the auto-growth settings which they inherit from the model database.
There are three options for auto growth:
Most DBAs accept these defaults, however, having your SQL Server database files auto growing is not a good idea as:
The best practice is to size your data and log files to sizes that minimize the number of auto growths. For example, if you expect your database to grow 10% each year you might want to size it 30% to allow plenty of room to begin with.
For data files, the best practice is not to use the Growth in Percent, but rather the Growth in Megabytes, and select a value that will work for your database. For example, small databases might set the Growth in Megabytes to 1 Gig, where very large databases (VLDBs) may set it to 10 Gigs or more. The reason for this is that the default percent growth (10%) for a 400 Gig database will be 40 Gigs – a large uncontrolled jump at any one time. Setting it to 1 Gig or 2 Gigs will create auto growths in a much more controlled manner.
To size your transaction log files you should:
This prevents too many virtual log files, which can lead to performance problems.
[TABLE]
There are two parts to proactive monitoring of SQL Server database sizes:
You want to know how full your disks are currently, how full the database and log files are, and which ones are experiencing auto growth.
Xp_fixeddrives will give the amount of free space available on local fixed drives. To get a more complete picture of your disk sizes, you need to use something like the below script.
Note:
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
For SQL 2005 you will need to enable Ole Automation Procedures:
sp_configure 'Ole Automation Procedures',1
reconfigure with override):
Then create and run this procedure:
Click here to view and/or download the procedure.
I find the query below to provide me with most of the information I require to collect on my servers' databases:
[IMAGE]
These results can be piped into a table with a structure like this one:
You can sum up by drive to determine the total size consumed by all of your database files. You will need to run the above script at a predefined interval to determine when you are getting dangerously low on space.
It is wise to monitor your database files to get an idea of which ones are filling up and approaching the level where they will auto grow. Sp_spaceused is ideal for this. Detecting auto growths themselves is more complex.
Detecting auto-growths
There are two tools for doing this:
Trace files
Performance monitor
[TABLE]
I find using trace files are the best, as they can detect real time data and log files auto-growths. The below script illustrates how to set up your trace:
Click here to view and/or download the script.
The following allows you to interactively read your trace.
Note: A number representing a handle to your trace will be displayed by the above script. Enter it below, in my case it was 1:
select * from fn_trace_getinfo ( 1)
select databaseid, spid, duration,starttime,servername,
events=case when eventclass=92 then
'database '+db_name(databaseid)+' data file increased' else
' database '+db_name(databaseid)+' log file increased' end from fn_trace_gettable (
'C:\Autogrow.trc', 1) where eventclass in (92,93)
You can run this query once every 1 minute and then handle the log and database file auto grow events as you see fit, i.e. email, pager, etc.
[TABLE]
You can only use performance monitor to monitor log auto growths – there is no counter for data file growths. Use Performance Monitor to monitor the performance objects SQL Server Databases: Log Growths, Percent log Used, and Data File Size.
Performance Monitor allows you to watch specific databases or all databases, and if necessary, raise alerts to send net messages or write events to the Windows NT application log, which monitoring software (like NetIQ) can then pick up and react to.
Summary
Monitoring your SQL Server database files is an essential operation of all DBAs. Failure to do so can result in downtime and data loss. DBAs must be proactive in monitoring their database file sizes. In this article we have looked at three ways of monitoring auto-growths of database files. Choose a method that works for you. The trace method does provide real time monitoring of auto-growths. It must be used in conjunction with a method which gathers information on available disk space – like the method discussed in the T-SQL method. Performance Monitor does allow you to monitor log file auto and growths and the database file size, and allows alerts to be raised when conditions get low.
[TABLE]
