Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2002
    Posts
    1,245

    Unanswered: FWIW: Database Space Used (stored proc)

    For what it's worth, I hacked up MS' sp_spacedused and created a new stored procedure called sp_dbspaceused. I made the following modifications:

    1. It returns a single resultset (instead of multiple resultsets);
    2. I eliminated the options that were specfically geared towards sizing of individual objects (no object name parameter and no update statistics parameter);
    3. I eliminated the formatting from the result set (the numbers are expressed in KB)

    Place the code into an admin database or (more risky and less "best practice") directly into your master database.

    Usage:
    USE MyDatabase
    GO

    EXEC AdminDatabase.dbo.sp_dbspaceused
    GO


    Code:
    CREATE PROCEDURE sp_dbspaceused 
    
    as
    
    declare @id	int			-- The object id of @objname.
    declare	@pages	int			-- Working variable for size calc.
    declare @dbname sysname
    declare @dbsize dec(15,0)
    declare @logsize dec(15)
    declare @bytesperpage	dec(15,0)
    declare @pagesperMB		dec(15,0)
    
    /*Create temp tables before any DML to ensure dynamic
    **  We need to create a temp table to do the calculation.
    **  reserved: sum(reserved) where indid in (0, 1, 255)
    **  data: sum(dpages) where indid < 2 + sum(used) where indid = 255 (text)
    **  indexp: sum(used) where indid in (0, 1, 255) - data
    **  unused: sum(reserved) - sum(used) where indid in (0, 1, 255)
    */
    create table #spt_space
    (
    	rows		int null,
    	reserved	dec(15) null,
    	data		dec(15) null,
    	indexp		dec(15) null,
    	unused		dec(15) null
    )
    
    
    set nocount on
    
    /*
    **  If @id is null, then we want summary data.
    */
    /*	Space used calculated in the following way
    **	@dbsize = Pages used
    **	@bytesperpage = d.low (where d = master.dbo.spt_values) is
    **	the # of bytes per page when d.type = 'E' and
    **	d.number = 1.
    **	Size = @dbsize * d.low / (1048576 (OR 1 MB))
    */
    begin
    	select @dbsize = sum(convert(dec(15),size))
    		from dbo.sysfiles
    		where (status & 64 = 0)
    
    	select @logsize = sum(convert(dec(15),size))
    		from dbo.sysfiles
    		where (status & 64 <> 0)
    
    	select @bytesperpage = low
    		from master.dbo.spt_values
    		where number = 1
    			and type = 'E'
    	select @pagesperMB = 1048576 / @bytesperpage
    /*
    	select  database_name = db_name(),
    		database_size =
    			ltrim(str((@dbsize + @logsize) / @pagesperMB,15,2) + ' MB'),
    		'unallocated space' =
    			ltrim(str((@dbsize -
    				(select sum(convert(dec(15),reserved))
    					from sysindexes
    						where indid in (0, 1, 255)
    				)) / @pagesperMB,15,2)+ ' MB')
    */
    	print ' '
    	/*
    	**  Now calculate the summary data.
    	**  reserved: sum(reserved) where indid in (0, 1, 255)
    	*/
    	insert into #spt_space (reserved)
    		select sum(convert(dec(15),reserved))
    			from sysindexes
    				where indid in (0, 1, 255)
    
    	/*
    	** data: sum(dpages) where indid < 2
    	**	+ sum(used) where indid = 255 (text)
    	*/
    	select @pages = sum(convert(dec(15),dpages))
    			from sysindexes
    				where indid < 2
    	select @pages = @pages + isnull(sum(convert(dec(15),used)), 0)
    		from sysindexes
    			where indid = 255
    	update #spt_space
    		set data = @pages
    
    
    	/* index: sum(used) where indid in (0, 1, 255) - data */
    	update #spt_space
    		set indexp = (select sum(convert(dec(15),used))
    				from sysindexes
    					where indid in (0, 1, 255))
    			    - data
    
    	/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
    	update #spt_space
    		set unused = reserved
    				- (select sum(convert(dec(15),used))
    					from sysindexes
    						where indid in (0, 1, 255))
    
    	select reserved = cast((reserved * d.low / 1024.) as bigint) ,
    		data = cast((data * d.low / 1024.) as bigint) ,
    		index_size = cast((indexp * d.low / 1024.) as bigint) ,
    		unused = cast((unused * d.low / 1024.) as bigint) 
    		from #spt_space, master.dbo.spt_values d
    		where d.number = 1
    			and d.type = 'E'
    end
    
    return (0) -- sp_spaceused
    
    GO
    Have you hugged your backup today?

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I think this one is shorter :
    Code:
    select
       reserved=(
    	  select sum(convert(dec(15),reserved))
    		 from sysindexes
    	   where indid in (0, 1, 255))*8,
       index_size = ((
    	  select sum(convert(dec(15),used))
    		 from sysindexes
    	   where indid in (0, 1, 255))
    	   - (
    	  select (select sum(convert(dec(15),dpages))
    		 from sysindexes
    	   where indid < 2) + isnull(sum(convert(dec(15),used)), 0)
    	   from sysindexes
    	   where indid = 255))*8,
       data=(
    	  select (select sum(convert(dec(15),dpages))
    		 from sysindexes
    	   where indid < 2) + isnull(sum(convert(dec(15),used)), 0)
    	   from sysindexes
    	   where indid = 255)*8,
       unused=((
    	  select sum(convert(dec(15),reserved))
    		 from sysindexes
    	   where indid in (0, 1, 255))
    	   - (
    	  select sum(convert(dec(15),used))
    		 from sysindexes
    	   where indid in (0, 1, 255)))*8
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Dec 2002
    Posts
    1,245
    Yes it is. No one ever accused me of having an overabundance of imagination.

    Thanks for the nice re-write.

    Regards,

    hmscott
    Have you hugged your backup today?

  4. #4
    Join Date
    Dec 2002
    Posts
    1,245
    With compliments to rdjabarov and apologies to those who do this for a living , I offer up this version which will pull the results for each database...

    Regards,

    hmscott

    Code:
    ALTER PROC sp_dbSpaceUsed
    
    AS
    
    CREATE TABLE #TempSpace (
    	[Database] varchar(255),
    	Reserved dec(15),
    	Index_Size dec(15),
    	Data dec(15),
    	Unused dec(15)
    )
    
    DECLARE @sSQL varchar(1000)
    
    SELECT @sSQL = 'INSERT INTO #TempSpace ([Database], Reserved, Index_Size, Data, Unused)
    SELECT
       ''?'' as [Database],
       reserved=(
    	  select sum(convert(dec(15),reserved))
    		 from [?]..sysindexes
    	   where indid in (0, 1, 255))*8,
       index_size = ((
    	  select sum(convert(dec(15),used))
    		 from [?]..sysindexes
    	   where indid in (0, 1, 255))
    	   - (
    	  select (select sum(convert(dec(15),dpages))
    		 from [?]..sysindexes
    	   where indid < 2) + isnull(sum(convert(dec(15),used)), 0)
    	   from [?]..sysindexes
    	   where indid = 255))*8,
       data=(
    	  select (select sum(convert(dec(15),dpages))
    		 from [?]..sysindexes
    	   where indid < 2) + isnull(sum(convert(dec(15),used)), 0)
    	   from [?]..sysindexes
    	   where indid = 255)*8,
       unused=((
    	  select sum(convert(dec(15),reserved))
    		 from [?]..sysindexes
    	   where indid in (0, 1, 255))
    	   - (
    	  select sum(convert(dec(15),used))
    		 from [?]..sysindexes
    	   where indid in (0, 1, 255)))*8'
    
    EXEC sp_MSforeachdb @command1=@sSQL
    
    SELECT * FROM #TempSpace
    
    DROP TABLE #TempSpace
    Have you hugged your backup today?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •