Knowing the size of a SQL Server database is one of the many DBA responsibilities that you can accomplish easily with the stored procedure sp_SDS. Not only will sp_SDS determine “SQL Database Space,” but it can also be used to monitor database growth, alert a DBA on data or log file growth, execute a transaction log backup and even provide a detailed breakdown at the file level so a DBA can then shrink files with the databases with the emptiest space. This tip shows you the complete sp_SDS and its computing algorithm.
Click here to download the complete Data Definition Language (DDL) for sp_SDS. Run the DDL query against the master database to create the sp_SDS stored procedure.
Listing 1: T-SQL definition for sp_SDS.
Now, I’ll explain how this SQL Server stored procedure finds the size of a database and how to use it.
Most of the input variables for sp_SDS are pretty self-explanatory. The database we want to size is @TargetDatabase. @Level is how detailed the report should be, either at the database level or at the individual database file level. The default is database level. It shows one summary per database. A bit value is @UpdateUsage. The default value of 0 means we do not wish to run “DBCC UPDATEUSAGE” in SQL Server 2005 and SQL Server 2008. In SQL 2000, values in a sysindexes table sometimes are not updated promptly. Therefore, to get an accurate reading, we need to run this DBCC command. The @Unit parameter indicates what measurement the report should be in, namely KB, MB or GB. If it’s not specified, the measurement used is megabytes.
In Figure 1, you see a screenshot executing the code in Listing 2. The report is — by default — a summary at the database level with one row for each database.
USE master; EXEC dbo.sp_SDS; |
Listing 2: Sp_SDS in its simplest form of execution without parameters (all input variables are nulls). This is how the basic report will look running it out the of box:
Figure 1: Result of running sp_SDS with no input parameters on a testing SQL Server 2005. It generates a database level summary report in megabytes
The first column “Weight (%)” calculates the percentage of total database size that a given database takes. For example, AdventureWorks is 469.94 MB in total, divided by the grand total of 20,404.51 MB, which is 0.023 (i.e., 2.3 %).
From the report, it’s easy to tell that the majority of database space is taken by DBAReports (approximately 77 %). Data in this column gives DBAs a rough database picture at a glance. The remaining value-related columns are organized in a formula-like structure.
I like to use arithmetic formulas to denote seemingly convoluted relationships. In this case, the “TOTAL” column is the result of additions on both the left and right sides. These two equations are centered on different angles. One shows the space taken and the remaining free space. The other displays the data and log compositions of the database. Here is the math that shows how the numbers are derived. (Still use AdventureWorks as an example.):
TOTAL (469.94 MB) = USED (168.41 MB) + FREE (301.53 MB)
TOTAL (469.94 MB) = DATA (243.94 MB) + LOG (226.00 MB)
USED (%) (35.84 %) = USED (168.41 MB) / TOTAL (469.94 MB)
FREE (%) (64.16 %) = FREE (301.53 MB) / TOTAL (469.94 MB)
DATA (used %) (66.02 %) = used (161.06 MB) / DATA (243.94 MB)
LOG (used %) (3.25 %) = used (7.35 MB) / LOG (226.00 MB)
It’s worth noting that some values for the database Test_snapshot are null in the result. Test_snapshot is a snapshot database whose log files are not allowed by design. In addition, there’s a summary line at the end of the report to show the subtotal for each columns.
Usually, one would run the system stored procedure sp_spaceused or pull out a disk usage report from SQL Server Management Studio when checking database space. It’s interesting to compare sp_SDS with each method. The diagram in Figure 2 shows a couple of screenshots put together for comparison. It contains four sections. Section 1 is the result for sp_SDS at the database level. Section 2 shows the query result for sp_spaceused. Section 3 is similar to Section 1, but at the file level. Section 4 represents a pie chart plotted from SSMS. The relevant values are color highlighted and linked across all sections.
Figure 2: Diagram shows a comparison of sp_SDS at database level, sp_SDS at file level, sp_spaceused and a pie chart of disk usage graphic report. (Click on image for enlarged view.)
In SQL Server 2000, the Taskpad view in Enterprise Manager draws a different picture than in SQL Server 2005. In Figure 3, one of my managed databases, called “LANEPMSI,” contains 54 data files and one log file. For now, let’s not worry about why a four and a half GB database would need so many data files — that’s the vendor’s choice. What I want to emphasize is that, given such a large number of files, it’s difficult for a DBA to calculate their sizes. With sp_SDS, it is as easy to obtain this data as it is to F5 the T-SQL code in Listing 3. Figure 4 reflects the results for executing the T-SQL statements in Listing 3.
USE master; EXEC dbo.sp_SDS 'LANEPMSI', 'DATABASE', 1, 'MB'; EXEC dbo.sp_SDS 'LANEPMSI', 'file', 1, 'MB'; |
Listing 3: Executing sp_SDS at the database and file levels in a SQL Server 2000 database.
Figure 3: A SQL Server 2000 database contains a large number of data files, which makes it difficult to display the summary of total, used and free space of the database.
Figure 4: Sp_SDS lets you look at the total used and free, data and log spaces. It also breaks down the database into individual data and log files and shows the corresponding values for each file.
Sp_SDS is compatible with SQL Server 2000, 2005 and 2008. Figure 5 shows the execution of sp_SDS in a SQL Server 2008 database in parallel to executing the system stored procedure sp_spaceused. You can compare the data between Figure 5 and Figure 6 that shows a Disk Usage Report.
Figure 5: Sp_SDS is compatible with SQL Server 2008. Running sp_SDS and sp_spaceused in AdventureWorks2008 sample database shows comparable results.
Figure 6: A pie chart view of the Disk Usage report for AdventureWorks2008 sample database on SQL Server 2008.
Sp_SDS and its computing algorithm is very useful for various DBA tasks. Have you ever been asked to prepare a database space usage report for a management meeting? You can use sp_SDS to quickly generate a neat tabular format. One DBA responsibility is to monitor database growth. You can schedule a daily job to run sp_SDS and save the result in a table. As time goes by, you will establish a data warehouse, which you can use for database growth trend analysis.
Creating an alert on data or log growth is another practical use for the stored procedure sp_SDS. If the size exceeds a threshold, you can fire the alert or do something else, like executing a transaction log backup job. Occasionally a DBA does not need to shrink the whole database. He just has to shrink one or a few data files that hold the biggest empty space. In this scenario, sp_SDS with @Level = ‘FILE’ can help a DBA quickly decide on which file(s) to shrink. The detailed file level breakdown of SQL Server database space is also valuable in assisting a DBA to move things around when restoring databases in an environment with disk space shortage.
ABOUT”THE”AUTHOR OF SP_SDS
Richard Ding, database administrator at Northeastern University in Boston, has worked with SQL Server since the late 1990s. His interests cover database administration, T-SQL development, disaster recovery, replication and performance tuning. Ding writes for several magazines for the SQL Server product, including SQL Server Magazine and SQL Server Standard. Contact Richard Ding at [email protected].