x

How to Store sp_spaceused Results

Building on this [question][1] 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.

[1]: http://ask.sqlservercentral.com/questions/19837/how-to-store-the-result-in-temp-table-from-exec-command
more ▼

asked Oct 01 '10 at 11:54 AM in Default

Blackhawk-17 gravatar image

Blackhawk-17
11.8k 28 30 35

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

4 answers: sort newest

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/][1]

[1]: http://jasonbrimhall.info/2010/05/25/space-used/
more ▼

answered Oct 01 '10 at 01:23 PM

CirqueDeSQLeil gravatar image

CirqueDeSQLeil
4k 10 11 15

WOW - tons 'o info from that! Still playing with it, probably Monday now before I get a chance to dig deeper.
Oct 01 '10 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 '10 at 02:00 PM CirqueDeSQLeil
+1 - awesome
Oct 02 '10 at 12:57 AM Matt Whitfield ♦♦
thanks matt
Oct 02 '10 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 '10 at 12:48 PM

Tim gravatar image

Tim
35.5k 32 40 138

One of the things I want from sp_spaceused is space available to plan for growth.
Oct 01 '10 at 01:03 PM Blackhawk-17
Depending on your set-up, xp_fixeddrives, might be helpful
Oct 01 '10 at 03:43 PM Scot Hauder
(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 '10 at 12:48 PM

V Padmala gravatar image

V Padmala
83 2 3 4

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 '10 at 01:28 PM TimothyAWiseman
(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][3]

[3]: http://code.google.com/p/pyodbc/wiki/Cursor#nextset
more ▼

answered Oct 01 '10 at 12:44 PM

TimothyAWiseman gravatar image

TimothyAWiseman
15.5k 19 22 32

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x672
x5
x1

asked: Oct 01 '10 at 11:54 AM

Seen: 2222 times

Last Updated: Oct 01 '10 at 11:54 AM