THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Andrew Kelly

sp_spaceused Alternative

 

      I don’t know why but for some reason I have never liked using sp_spaceused. It probably started a long time ago when the results were often inaccurate due to the meta data being out of date. I am pretty sure that was fixed somewhere along the line but the system stored procedure had some other limitations that usually prevented me from seeing the data that I really wanted in most cases. So at some point I created my own stored procedure to view the data I was most interested in almost on a daily occurrence. Now to be totally accurate I started with code that one of the SSMS reports uses to show data and index space usage.  The code was not the prettiest so I did clean it up a little and modified it to better suite my needs but the core worked so I used it. I then added some additional queries to get other data that the original query didn’t have such as the file size and growth info. I also slightly modified one of the many variations of functions available out there that format numbers with commas for better readability. In this case it is simply called dbo.commas. Normally I have a database on every instance in which I keep custom procedures / functions etc. in which I can count on certain objects to exist so I can reference them in other objects. These examples are shown with no fully qualified path to the dbo.commas UDF so the UDF and the procedure would have to exist in each database or the one that you care to use it in. While that works you should consider doing one of two alternatives. Either add a utility database like I mentioned and put the two objects there or rename the procedure to have sp_xxx and put the objects in the master database and then mark them as system objects.  That way you can call the procedure from the context of any database.

OK so enough about that lets see what the procedure does and how to use it. The procedure has two parameters @DBName and @ShowFileInfo. The @DBName parameter is pretty self explanatory and will dictate which database the results are for. The second parameter is really optional as it defaults to a value = 0.  If the @ShowFileInfo parameter = 0 then only one result set will be returned which contains the relevant data for each table in the database. More on that in a bit. If a value of 1 is passed in then a 2nd result set is returned that contains information related to the size of the database, it’s files and growth. See further down for more details on both result sets.  These are all acceptable ways to call the stored procedure:

EXEC dbo.ListDBSpaceInfo @DBName = 'Adventureworks'
EXEC dbo.ListDBSpaceInfo @DBName = 'Adventureworks', @ShowFileInfo = 0
EXEC dbo.ListDBSpaceInfo @DBName = 'Adventureworks', @ShowFileInfo = 1
EXEC dbo.ListDBSpaceInfo 'Adventureworks'
EXEC dbo.ListDBSpaceInfo 'Adventureworks', 1

Here is what the results would look like if you ran it against the Adventureworks database.

ADWorks

The first result set lists each table showing the size in MB’s for the reserved, data and index space. The row counts and the Created and Modified dates. This is the information I find most useful the majority of the time. It orders the tables by Reserved MB but you may prefer by table name or something else. The code is easy enough to modify to sort however you like and you can even add an additional parameter to chose the sort by column.  Again I use it this way most often so it is what it is.

The 2nd result set if you choose to return it will list certain information about the database and it’s files. It will show the current total size of all the data files which is pretty definitive by nature. However the next few columns are dependent on the max size and growth properties of each file. For instance if any of the data files are set to –1 which means unlimited growth the resultant column for Max Size will say Unlimited as will the Potential Free Space. If the files have a fixed size the total max size if then calculated and displayed along with that amount minus the current data usage to give you the potential free space. Basically that is how much free space the database can theoretically accommodate assuming there is enough space on the disks. The same holds true for the log file. These numbers are most useful if you have limits set but the information can give you a good idea of the overall condition of the data and log file usage.

Now I understand that none of this is information that wasn’t available to users before but I usually had to go to several different sources to get a comprehensive view like this. The purpose of this blog post was not to wow everyone with some rocket science code it was simply to share with others who may find it useful. I use it all the time so I figure there must be others who can take advantage of it as well and hopefully customize it to suite their needs like I have. Have fun everyone, here is the code:

Andy

Published Friday, May 17, 2013 5:35 PM by Andrew Kelly

Attachment(s): TSQLCODE.ZIP

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Alexandre Jobin said:

thank you for the code. I will take a look for sure!

May 21, 2013 9:24 AM
 

MP said:

In order to that stored procedure to run the function dbo.Commas whether should be placed to all databases or that stored procedure should be modified something like

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[ListDBSpaceInfo]

@DBName NVARCHAR(128) = NULL

,@ShowFileInfo TINYINT = 0

AS

SET NOCOUNT ON ;

declare @thatdb NVARCHAR(128)

set @thatdb = db_name()

--select @thatdb

DECLARE @SQL NVARCHAR(MAX) = '';

IF @DBName IS NULL

SET @DBName = DB_NAME() ;

SET @SQL = N'USE [' + @DBName + '] ;' ;

SET @SQL = @SQL + N'

DECLARE @Tables TABLE ([DB Name] VARCHAR(128),[Object ID] INT, [Schema Name] VARCHAR(128), [Table Name] VARCHAR(128), [Row Count] BIGINT,

                       [Reserved MB] BIGINT, [Data MB] BIGINT, [Index MB] BIGINT, [Unused MB] BIGINT ) ;

DECLARE @DBCC TABLE ([Database Name] NVARCHAR(128),[Log Size(MB)] Decimal(10,4),[Log Space Used(%)] Decimal(10,4),[Status] INT) ;

DECLARE @DataMaxSize BIGINT, @DataCurrentSize BIGINT, @UsedSpace BIGINT, @LogSize DECIMAL(10,2), @LogSpaceUsed DECIMAL(10,2) ;

DECLARE @LogMaxSize BIGINT, @LogCurrentSize BIGINT ;

-- Populate the table variable with the table info

INSERT INTO @Tables ([DB Name], [Object ID], [Schema Name], [Table Name], [Row Count],

                   [Reserved MB], [Data MB], [Index MB], [Unused MB])

SELECT

   DB_NAME() AS [DB Name],

   a2.object_id,

   a3.name AS [schemaname],

   a2.name AS [tablename],

   a1.rows as row_count,

       ((a1.[reserved] + ISNULL(a4.[reserved],0))* 8) / 1024 AS [reserved],

   (a1.[data] * 8) / 1024 AS [data],

   ((CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.[data] THEN (a1.used + ISNULL(a4.used,0)) - a1.[data] ELSE 0 END) * 8) / 1024 AS [index_size],

   ((CASE WHEN (a1.[reserved] + ISNULL(a4.[reserved],0)) > a1.used THEN (a1.[reserved] + ISNULL(a4.[reserved],0)) - a1.used ELSE 0 END) * 8) / 1024 AS [unused]

FROM

   (SELECT ps.object_id, SUM (CASE WHEN (ps.index_id < 2) THEN row_count ELSE 0 END ) AS [rows],

SUM (ps.reserved_page_count) AS [reserved], SUM (CASE WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)

ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count) END ) AS [data],

SUM (ps.used_page_count) AS [used]

   FROM sys.dm_db_partition_stats AS [ps]

   GROUP BY ps.object_id) AS [a1]

   LEFT OUTER JOIN

(SELECT it.parent_id, SUM(ps.reserved_page_count) AS [reserved], SUM(ps.used_page_count) AS used

FROM sys.dm_db_partition_stats AS ps INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)

   WHERE it.internal_type IN (202,204)

       GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id)

   INNER JOIN sys.all_objects AS a2  ON ( a1.object_id = a2.object_id )

   INNER JOIN sys.schemas AS a3 ON (a2.schema_id = a3.schema_id)

WHERE a2.type <> N''S'' and a2.type <> N''IT'' ;

SELECT t.[DB Name] AS [Database Name]

     , t.[Schema Name]

     , t.[Table Name]

     , '+@thatdb+N'.'+'dbo.Commas(t.[Row Count]) AS [Row Count]

     , '+@thatdb+N'.'+'dbo.Commas(t.[Reserved MB]) AS [Reserved MB]

     , '+@thatdb+N'.'+'dbo.Commas(t.[Data MB]) AS [Data MB]

     , '+@thatdb+N'.'+'dbo.Commas(t.[Index MB]) AS [Index MB]

     , (SELECT o.create_date FROM sys.objects AS o WHERE o.object_id = t.[Object ID]) AS [Create Date]

     , (SELECT o.modify_date FROM sys.objects AS o WHERE o.object_id = t.[Object ID]) AS [Modified Date]

FROM @Tables AS t

   ORDER BY CAST([Reserved MB] AS BIGINT) DESC ;

IF @ShowFileInfo = 1

BEGIN

SELECT @DataCurrentSize = SUM(CAST([size] AS BIGINT)) * 8 /1024

,@DataMaxSize =  SUM(CAST([max_size] AS BIGINT)) * 8 /1024

FROM sys.database_files AS df

WHERE df.type = 0 ;

SELECT @LogCurrentSize = SUM(CAST([size] AS BIGINT)) * 8 /1024

,@LogMaxSize =  SUM(CAST([max_size] AS BIGINT)) * 8 /1024

FROM sys.database_files AS df

WHERE df.type = 1 ;

   SELECT @UsedSpace = SUM([Reserved MB]) FROM @Tables ;

   INSERT INTO @DBCC EXEC(''DBCC SQLPERF(Logspace)'') ;

   SELECT @LogSize = [Log Size(MB)], @LogSpaceUsed = [Log Space Used(%)]

       FROM @DBCC WHERE [Database Name] = DB_NAME(DB_ID()) ;

   SELECT DB_NAME() AS [Database Name]

       , '+@thatdb+N'.'+'dbo.Commas(@DataCurrentSize) AS [Current DB Size MB]

       , CASE WHEN @DataMaxSize < 1 THEN ''Unlimited'' ELSE '+@thatdb+N'.'+'dbo.Commas(@DataMaxSize) END AS [Max DB Size MB]

       , CASE WHEN @DataMaxSize < 1 THEN ''Unlimited'' ELSE '+@thatdb+N'.'+'dbo.Commas(@DataMaxSize - @UsedSpace) END AS [Potential DB Free Space MB]

       , '+@thatdb+N'.'+'dbo.Commas(@LogSize) AS [Tran Log Size MB]

       , '+@thatdb+N'.'+'dbo.Commas(@LogSpaceUsed) AS [% Log Used]

       , CASE WHEN @LogMaxSize < 1 THEN ''Unlimited'' ELSE '+@thatdb+N'.'+'dbo.Commas(@LogMaxSize) END AS [Max Log Size MB]

       , CASE WHEN @LogMaxSize < 1 THEN ''Unlimited'' ELSE '+@thatdb+N'.'+'dbo.Commas(@LogMaxSize - @LogSpaceUsed) END AS [Potential Log Free Space MB ]

    ;

END

'

EXEC sp_executeSQL @statement = @SQL, @params = N'@ShowFileInfo TINYINT', @ShowfileInfo = @ShowfileInfo ;

May 26, 2013 5:17 AM

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement