Have your customers or managers ever asked you how much their databases grew during the past year? Have you needed to plan how much disk capacity you'll need for the next year based on your database's average growth rate during the past 12 months? How long will your existing unallocated disk space last based on your current growth rate? To answer these kinds of database-growth questions or similar disk-space questions, you need some historical space-usage information about your databases. I've developed a process that you can use to automatically collect space-usage statistics for each of your databases. You can then use the collected space information to perform a simple growth-rate calculation.
Several months ago, I decided to build a process to capture space-usage information for each database on a system so that I could track disk-space consumption over time. I wanted to find the amount of space allocated and used for both the data and the log files. I was looking for the same information that you see in Enterprise Manager's Database Details pane when you're viewing Space Allocated information, but I needed the information to be available to T-SQL code. Using SQL Server Profiler, I discovered that Enterprise Manager obtains space-allocated information by using two DBCC statements. One of the statements, SQLPERF, is documented; the other DBCC statement, SHOWFILESTATS, isn't. By manually running DBCC SHOWFILESTATS on each database and comparing the output with what Enterprise Manager displayed, I determined that this command would provide me used disk space information by database.
Both SQL Server 2000 and SQL Server 7.0 use these DBCC statements to populate Enterprise Manager's Space Allocated display. The DBCC SQLPERF(LOGSPACE) statement returns transaction log space information—the allocated log size for each database in megabytes and the percentage of log space used for each database—for all databases. With some simple math, you can easily convert the percentage of log space used into megabytes. This DBCC statement helped me obtain the log file space information I wanted to track.
I used the undocumented DBCC SHOWFILESTATS statement, which returns space-usage information for one database's data, to obtain the rest of the disk-space statistics I wanted. This statement returns one record per physical data file. Each statistics record returned appears in six columns: Fileid, FileGroup, TotalExtents, UsedExtents, Name, and FileName. You can use the TotalExtents column to determine the total space allocated to data and the UsedExtents column to determine the total space used for data. By summing the TotalExtents and UsedExtents values of all files within a database, then converting the number of extents into megabytes, I calculated the total space allocated and total space used for data. These calculations gave me the data space usage information I wanted to track over time. Figure 1 shows sample output of the DBCC SHOWFILESTATS command after you run it against the master database.
I've built these two DBCC statements into a process that automatically collects space information by database. This process runs periodically and saves space-usage statistics in a database table. The process consists of a SQL Server Agent job that contains two steps. The first step executes a stored procedure named usp_get_dbstats, which generates a T-SQL script. The resulting script consists of a DBCC SQLPERF(LOGSPACE) statement to gather the log information for all databases, a DBCC SHOWFILESTATS statement for each database, and some code to manipulate the DBCC data into the right format for saving the space-usage information in. The second step executes the T-SQL script that the first step generates. After extracting the space-usage information from SQL Server and formatting the data, this script populates a permanent database table with the current data and log space-usage information. You can then use this permanent table to answer a wealth of disk space allocation questions.
This process of gathering space-usage statistics is an example of using T-SQL code to generate T-SQL code. I used this two-step process to minimize the complexity of writing a stored procedure that would need to issue a USE statement to let me run the DBCC SHOWFILESTATS command against each database. Now, let's look at my homegrown disk-space collection process in a little more detail.
The Stored Procedure
The usp_get_dbstats stored procedure, which Listing 1 shows, is the main body of the space-usage statistics-gathering process. The stored procedure queries the system tables and programmatically generates and executes PRINT statements to produce a T-SQL script that, when executed, uses two DBCC statements to extract current space-usage information. Let's walk through this stored procedure one section at a time.
The code at callout A in Listing 1 gathers the log-space usage information. This block of code, like the others in Listing 1, dynamically generates and executes a series of PRINT statements that become the T-SQL script that gathers the space-usage statistics. The code at callout A produces a set of T-SQL statements that create a temporary table called #tmplg, then populates the table with the output from DBCC SQLPERF(LOGSPACE). The INSERT INTO statement that has the EXECUTE option puts the DBCC statement's output into the #tmplg table, which will eventually contain one record for each database on the server. Each record will contain information that goes into columns labeled DBName, LogSize, LogSpaceUsed, and Status. You can find the definitions of each of these columns in SQL Server Books Online (BOL) under the heading "DBCC SQLPERF."
Callout B shows the code that creates the #tmp_stats temporary table. Each record in this table will hold both the data and log space-usage information for a database. Later code blocks will populate and use this temporary table. This section of the code executes a series of PRINT statements to append to the T-SQL script that the code at callout A started.
Prev. page
[1]
2
3
next page 
