x

How to Store sp_spaceused Results

Building on this question how would one store the results of the following:

 EXEC sp_msforeachdb @command1='use [?] exec sp_spaceused'

It spits out multiple rows and doesn't insert so nicely.

more ▼

asked Oct 01, 2010 at 11:54 AM in Default

avatar image

Blackhawk-17
12k 30 35 42

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

Try this:

With a slight mod, you could have this insert into your table.

 /*similar to sp_spaceused */
 
 
 declare @dbsize decimal(19,2)
         ,@logsize decimal(19,2)
 
 
 set nocount on
 
 /*
 **  Summary data.
 */
 begin
     select @dbsize = sum(convert(decimal(19,2),case when type = 0 then size else 0 end)) * 8/1024
         , @logsize = sum(convert(decimal(19,2),case when type = 1 then size else 0 end)) * 8/1024
         from sys.database_files
 
 end
 /*
 **  We want all objects.
 */
 Begin
     With FirstPass as (
         SELECT OBJECT_ID,
             ReservedPage = convert(decimal(19,2),SUM(reserved_page_count)) * 8/1024,
             UsedPage = convert(decimal(19,2),SUM(used_page_count)) *8/1024,
             PageCnt = SUM(
             convert(decimal(19,2),CASE
                 WHEN (index_id < 2) 
                     THEN (used_page_count)
                 ELSE lob_used_page_count + row_overflow_used_page_count
                 END
             )) * 8/1024,
             RowCnt = SUM(
             CASE
                 WHEN (index_id < 2) 
                     THEN row_count
                 ELSE 0
             END
             )
         FROM sys.dm_db_partition_stats 
         --Where OBJECTPROPERTY(OBJECT_ID,'IsMSShipped') = 0
         Group By OBJECT_ID
     )
     ,InternalTables as (
         Select ps.OBJECT_ID,
             ReservedPage = convert(decimal(19,2),SUM(reserved_page_count)) * 8/1024,
             UsedPage = convert(decimal(19,2),SUM(used_page_count)) *8/1024
         From sys.dm_db_partition_stats  ps
             Inner Join sys.internal_tables it
                 On it.OBJECT_ID = ps.OBJECT_ID
                 And it.internal_type IN (202,204,211,212,213,214,215,216)
         Where it.parent_id = ps.OBJECT_ID
             --And OBJECTPROPERTY(ps.OBJECT_ID,'IsMSShipped') = 0
         Group By ps.OBJECT_ID
     )
     ,Summary as (
         SELECT 
             ObjName = OBJECT_NAME (f.OBJECT_ID),
             NumRows = MAX(f.rowcnt),
             ReservedPageMB = SUM(IsNull(f.reservedpage,0) + IsNull(i.ReservedPage,0)),
             DataSizeMB = Sum(f.PageCnt),
             IndexSizeMB = Sum(CASE WHEN (f.UsedPage + IsNull(i.UsedPage,0)) > f.PageCnt 
                             THEN ((f.UsedPage + IsNull(i.UsedPage,0)) - f.PageCnt) ELSE 0 END) ,-- Equivalent of max_record_size from sys.dm_db_index_physical_stats
             UnusedSpace = Sum(CASE WHEN (f.ReservedPage + IsNull(i.ReservedPage,0)) > (f.UsedPage + IsNull(i.UsedPage,0)) 
                 THEN ((f.ReservedPage + IsNull(i.ReservedPage,0)) - (f.UsedPage + IsNull(i.UsedPage,0))) ELSE 0 END),
             DBSizeMB = @Dbsize,
             LogSizeMB = @logsize
         From FirstPass F
             Left Outer Join InternalTables i
             On i.OBJECT_ID = f.OBJECT_ID
         Group By f.OBJECT_ID
     )
     Select ObjName,NumRows, ReservedPageMB, DataSizeMB, IndexSizeMB, UnusedSpace, DBSizeMB, LogSizeMB,
             PercentofDB = ((IndexSizeMb + DataSizeMB) / @DBsize) * 100
 
     From Summary
     Order By PercentofDB desc
 End

You can also find that on my blog at http://jasonbrimhall.info/2010/05/25/space-used/

more ▼

answered Oct 01, 2010 at 01:23 PM

avatar image

CirqueDeSQLeil
5.5k 11 13 20

WOW - tons 'o info from that! Still playing with it, probably Monday now before I get a chance to dig deeper.

Oct 01, 2010 at 01:48 PM Blackhawk-17

Thanks When I tinkered with the scripts (if you follow the series there are two similar scripts based on the MS procs) I was considering expanding it to include more info. I still have to think about it for a bit.

Oct 01, 2010 at 02:00 PM CirqueDeSQLeil
  • awesome

Oct 02, 2010 at 12:57 AM Matt Whitfield ♦♦

thanks matt

Oct 02, 2010 at 07:00 AM CirqueDeSQLeil
(comments are locked)
10|1200 characters needed characters left

If you are just looking to capture the size of your database and store this for historical reasons and for monitoring try using sysfiles instead of sp_spaceused.

more ▼

answered Oct 01, 2010 at 12:48 PM

avatar image

Tim
40.4k 39 84 166

One of the things I want from sp_spaceused is space available to plan for growth.

Oct 01, 2010 at 01:03 PM Blackhawk-17

Depending on your set-up, xp_fixeddrives, might be helpful

Oct 01, 2010 at 03:43 PM Scot Hauder
(comments are locked)
10|1200 characters needed characters left

I am not aware of a way to do this within T-SQL, and some quick research did not turn up a way. I suspect that it may not be possible in pure T-SQL, but I would be glad to be proven wrong.

On the other hand, if you step out of pure T-SQL most client languages make it relatively easy to deal with multiple returned data sets. If you are using C# for instance, you should just be able to use the NextResult method, as described here http://csharp.net-informations.com/data-providers/csharp-multiple-resultsets.htm and here: http://www.mindfiresolutions.com/NextResult-in-C-or-ADONET-data-reader-705.php

If you are using python with pyodbc, then you can use the nextset function of the cursor to advance to the next dataset. See http://code.google.com/p/pyodbc/wiki/Cursor#nextset

more ▼

answered Oct 01, 2010 at 12:44 PM

avatar image

TimothyAWiseman
15.6k 22 51 38

(comments are locked)
10|1200 characters needed characters left

Hello

Try the below script

 create table #test (    --drop table #test                                                             
   database_name varchar(255)                                                                           
   ,database_size varchar(255) not null                                                                 
   ,unallocated_space varchar(255) not null                                                             
   ,reserved varchar(255) not null                                                                      
   ,data varchar(255) not null                                                                          
   ,index_size varchar(255) not null                                                                    
   ,unused varchar(255) not null                                                                        
   )                                                                                                    
 go                                                                                                     
 exec dbo.sp_msforeachdb                                                                                
 '                                                                                                      
    if db_id(''?'') > 4                                                                                 
              begin                                                                                         
       use [?];                                                                                         
       declare @cmd varchar(max);                                                                       
       declare c cursor local for                                                                       
                       select cmd = ''use [?]; exec sp_spaceused ''''''                           
                     + schema_name(schema_id) + ''.''                                  
                     + name + '''''', true'' from sys.objects                          
         where objectproperty(object_id, ''IsUserTable'') = 1                       
       open c;                                                                                          
       fetch next from c into @cmd                                                                     
       while @@fetch_status = 0                                                                         
                  begin                                                                               
                   insert #test (database_name, database_size, unallocated_space,reserved,   data, index_size, unused) exec (@cmd);                                                                
          fetch next from c into @cmd                                                                  
                       end;                                                                                
       close c;                                                                                         
       deallocate c;                                                                                    
    end                                                                                                 
 '                                                                                                      
 select * from #test                                                                                    
                                                                                                        
more ▼

answered Oct 01, 2010 at 12:48 PM

avatar image

V Padmala
83 2 3 6

I get a "Msg 213, Level 16, State 7, Procedure sp_spaceused, Line 180 Insert Error: Column name or number of supplied values does not match table definition." when I try it, which is roughly what I would have expected from reading over it. Am I perhaps missing something that needs to be tweaked or misunderstanding some portion of it?

Oct 01, 2010 at 01:28 PM TimothyAWiseman
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1014
x7
x1

asked: Oct 01, 2010 at 11:54 AM

Seen: 3102 times

Last Updated: Oct 01, 2010 at 11:54 AM

Copyright 2016 Redgate Software. Privacy Policy