Gathering Space Usage Statistics
September 30, 2004
As a DBA, you will need to perform a number of different tasks to keep your databases and servers up and running. One of those DBA tasks you will need to perform is disk space capacity planning. You need to make sure there is enough disk space, so when one of your databases needs to grow it can find the disk space it needs. Depending on the growth patterns of your databases, you will more than likely need to purchase additional disk space periodically. One way to estimate your future disk space needs accurately, is to track the growth rate of your databases over time. This article will discuss how disk space usage information can be a valuable resource in helping with capacity planning.
SQL Server does not provide a simple solution to gathering database space usage statistics. In order to gather disk space usage statistics you will need to use both documented and undocumented methods available within SQL Server. Prior to discussing how to gather space usage statistics, let's first discuss how statistics can be used to help you with your disk space capacity planning.
Suppose your manager comes to you and says he has some extra money to buy you some additional disk space for your server, although, he does not want to buy any more than you might need to handle the growth of all your databases for the next 18 months. He needs you to come up with an estimate on how much disk space you will need for the next 18 months. Your manager expects you to base your estimate on some hard, actual disk space usage statistics. If you have been gathering disk space usage space statistics for your databases overtime this would be a very easy task. However, if you do not have any hard disk space usage information then you would not be able to comply with your manager's request.
Let's see how disk space statistics can help you provide your manager with how much your databases will grow in the next 18 months. To calculate disk space growth you need to have some actual disk space usage information for two different points in time. Suppose you wrote down how much each database was using during the first week of June 2004. Then during the first week of August, you once again recorded the amount of disk space each of your databases was using. Now to calculate your disk space usage for the next 18 months you could use the following formula:
Disk Usage Estimate = ((SUM(ADB1+ADB2+ADB3+...+ADBn) - SUM(JDB1+JDB2+JDB3+...+JDBn))/2) * 18
Where Disk Usage Estimate is your calculated estimate of the amount of disk space needed for the next 18 months, ADBx is the amount of space used in August for DBx where x is a value between 1 and n, where n represent a different space usage amount for each database on your server. JDBx is the amount of space used in June for DBx where x is a value between 1 and n, where n represents a different space usage amount for each database on your server. The "2" in the above formula represents the number of months between the two different dates where disk space usage information was collected. By subtracting the amount of space used in August by the amount of space used in June then dividing that number by "2" gives you the average space used in a single month. By taking the calculated monthly space usage number and multiplying it by 18 gives you the disk space usage estimate for 18 months. Now that you know how to calculate the amount of disk space used between two dates, let's discuss how to capture disk space usage information by database for SQL Server.
Most people are familiar with how to use Enterprise Manager to determine how much disk space is used by a database. To use Enterprise Manager, click on the "View" menu item and then click on the "Taskpad" item from the view pull down menu. Doing this will display the "Space Allocated" information in the right pane of the Enterprise Manager display. On the "Space Allocated" display, you can see the amount of disk space used and allocated for both "Data" and the "Transaction log."
So how does Enterprise Manager come up with the data for this space usage display? If you turn on Profiler, then have Enterprise Manager display the "Taskpad" information, then the method that Enterprise Manager uses should be revealed. Profiler will show you that Enterprise manager uses two commands to get the hard disk space information for the "Space Allocated" display. These two commands are "DBCC sqlperf(logspace)" and "DBCC showfilestats". The "DBCC sqlperf(logspace)" command is documented in Books Online, but the "DBCC showfilestats" is an undocumented DBCC command. If you run these commands in Query Analyzer, you will find that Enterprise Manager manipulates the data returned in order to build the "Space Allocated display. Let's review how Enterprise manager uses these two commands to calculate the amount of disk space used and allocated to the Data and Transaction log files.
First, let's look at the output of the "DBCC sqlperf(logspace)" command. When you run this command, it returns a record set. The record set contains one row per database, and each row in the record set contains the following information: Database Name, Log Size (MB), Log Space Used (%), and Status. Using the information returned from the "DBCC sqlperf(logspace)" command you can determine the allocated and used disk space amount for each database's transaction log. The "Log Size (MB)" column identifies the number of megabytes allocated to the transaction log. So to calculate the amount of space that the actual transaction log is using, you need to use the following formula:
Transaction Log Space Used = Log Size (MB) * Log Space Used (%).