Check SQL Server database and log file size with this stored procedure
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 most empty space.
This tip shows you the complete sp_SDS and its computing algorithm. It takes you a step further
than the
Premium Access
Register now for unlimited access to our premium content across our network of over 70 information Technology web sites.
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States.
Privacy
This was first published in May 2008
stored procedure
sp_SOS that finds the size of database objects, including SQL Server tables.
Click here to download the complete Data
Definition Language (DDL) for sp_SDS.
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).
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. (Click on image for enlarged
view.)
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.
As you might recall from my previous tip about sp_SOS, 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. (Click on image
for enlarged view.)
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.
(Click on image for enlarged view.)
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. (Click on image for enlarged
view.)
Figure
6: A pie chart view of the Disk Usage report for AdventureWorks2008 sample database on SQL
Server 2008. (Click on image for enlarged view.)
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
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].
Disclaimer:
Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.
Join the conversationComment
Share
Comments
Results
Contribute to the conversation