locked
sp_spaceused RRS feed

  • Question

  • Hi everyone,

     

    I'd like to retrieve the results for SP_SPACEUSED.

    I've created a table with 7 columns, something like that:

     

    CREATE TABLE [dbo].[ESPACIOUSADO](

          [DATABASE_NAME] VARCHAR(255) NULL,

          [DATABASE_SIZE] VARCHAR(120) NULL,

          [UNALLOCATED SPACE] VARCHAR(120) NULL,

          [RESERVED] VARCHAR(120) NULL,

          [DATA] VARCHAR(120) NULL,

          [INDEX_SIZE] VARCHAR(120) NULL,

          [UNUSED] VARCHAR(120) NULL,

          ) ON [PRIMARY]

     

     

    INSERT INTO ESPACIOUSADO([DATABASE_NAME] ,     [DATABASE_SIZE],

          [UNALLOCATED SPACE] ,   [RESERVED] ,      [DATA] ,

          [INDEX_SIZE] ,    [UNUSED])

    EXEC sp_spaceused

     

     

    But it doesn't works at all:

     

    Mens 213, Nivel 16, Estado 7, Procedimiento sp_spaceused, Línea 148

    Insert Error: Column name or number of supplied values does not match table definition.

     

     

    Let me know how do I this task, others times I've been able to retrieve information for other system stored procedures.

     

    On the other hand, is there any method for do this sort of stuff under generic rules???

    Thanks in advance and regards,

    Tuesday, December 19, 2006 1:46 PM

Answers

  • If you just want to list all space used, I believe I got this script from elsewhere as well


    SET NOCOUNT ON
    DECLARE @lngTabCount INTEGER
    DECLARE @lngLoopCount INTEGER
    DECLARE @strTabName SYSNAME

    CREATE TABLE #tTables
    (
    numID INTEGER IDENTITY(1,1)
    ,strTableName SYSNAME
    )

    INSERT INTO #tTables (strTableName)
    SELECT name FROM dbo.sysobjects WHERE xtype = 'u'

    SET @lngTabCount = @@ROWCOUNT
    SET @lngLoopCount = @lngTabCount

    WHILE @lngLoopCount <> 0
    BEGIN
    SET @strTabName = (SELECT strTableName FROM #tTables WHERE numID = @lngLoopCount)
    EXEC sp_spaceused @strTabName
    SET @lngLoopCount = @lngLoopCount - 1
    END

    DROP TABLE #tTables
    GO

    Tuesday, January 9, 2007 12:43 PM
  • Hi JohDas,

    Unfortunately, separately it doesn't works. Throws the same error.

    What's lacking? I'm sure that's a little silly thing.

    So, up to the moment, sp_spaceused is not guily but...

    regards,

     

    Wednesday, December 20, 2006 8:07 AM

All replies

  • Hi,

    the error message you get seems to be caused by the INSERT statement and not by the sp_spaceused.

    It says that you tried to insert data in a wrongly fashion.

    Recheck that the insert statement is valid and run the above query in 3 steps:

    [1]

    CREATE TABLE [dbo].[ESPACIOUSADO](

          [DATABASE_NAME] VARCHAR(255) NULL,

          [DATABASE_SIZE] VARCHAR(120) NULL,

          [UNALLOCATED SPACE] VARCHAR(120) NULL,

          [RESERVED] VARCHAR(120) NULL,

          [DATA] VARCHAR(120) NULL,

          [INDEX_SIZE] VARCHAR(120) NULL,

          [UNUSED] VARCHAR(120) NULL,

          ) ON [PRIMARY]

     

    [2]

     

    INSERT INTO ESPACIOUSADO([DATABASE_NAME] ,     [DATABASE_SIZE],

          [UNALLOCATED SPACE] ,   [RESERVED] ,      [DATA] ,

          [INDEX_SIZE] ,    [UNUSED])

     

    [3]

     

    EXEC sp_spaceused

     

     

    You'll probably get this error message before step[3], which means sp_spaceused is innocent :)

    Tuesday, December 19, 2006 1:58 PM
  • Hi JohDas,

    Unfortunately, separately it doesn't works. Throws the same error.

    What's lacking? I'm sure that's a little silly thing.

    So, up to the moment, sp_spaceused is not guily but...

    regards,

     

    Wednesday, December 20, 2006 8:07 AM
  • Hi enric,

    can you post the exact statement you executed (no pseudocode, insteadreal names & arguments)?

    I believe it is a simple syntax error in that statement

    regards

    Wednesday, December 20, 2006 10:28 AM
  • Hi,

    First nipplet of code is when you create the table:

    CREATE TABLE [dbo].[ESPACIOUSADO](

    [DATABASE_NAME] VARCHAR(255) NULL,

    [DATABASE_SIZE] VARCHAR(120) NULL,

    [UNALLOCATED SPACE] VARCHAR(120) NULL,

    [RESERVED] VARCHAR(120) NULL,

    [DATA] VARCHAR(120) NULL,

    [INDEX_SIZE] VARCHAR(120) NULL,

    [UNUSED] VARCHAR(120) NULL,

    ) ON [PRIMARY]

     

    GO

     

    The last one, is just when I try to load the results into the table:

    INSERT INTO ESPACIOUSADO([DATABASE_NAME] , [DATABASE_SIZE],

    [UNALLOCATED SPACE] , [RESERVED] , [DATA] ,

    [INDEX_SIZE] , [UNUSED])

    EXEC sp_spaceused

     

    GO

    Error is:

    Mens 213, Nivel 16, Estado 7, Procedimiento sp_spaceused, Línea 148

    Insert Error: Column name or number of supplied values does not match table definition.

    Thanks a lot!

     

    Wednesday, December 20, 2006 2:22 PM
  • If you don't specify the table name in sp_spaceused it returns 2 result sets:

    AdventureWorks 181.81 MB 12.31 MB

    171520 KB 86880 KB 77952 KB 6688 KB

    so I try this:

    EXEC SP_SPACEUSED N'ESPACIOUSADO'

    and it returns one result set:

    ESPACIOUSADO 0           0 KB 0 KB 0 KB 0 KB

    but it still returns the same error when included in the insert statement...

    I'll keep you informed

    Cheers

     

    EDITED:

     

    === Edited by JohDas @ 21 Dec 2006 7:50 AM UTC===
    Hey this works for me:

    INSERT INTO ESPACIOUSADO
    (
    [DATABASE_NAME]
    --,[DATABASE_SIZE]
    ,[UNALLOCATED SPACE]
    ,[RESERVED]
    ,[DATA]
    ,[INDEX_SIZE]
    ,[UNUSED]
    )
    EXEC SP_SPACEUSED N'ESPACIOUSADO';

    It seems you were trying to insert 2 different result sets in the ESPACIOUSADO table

    So there are two paths from here on:

    [1] You either stick with the above query and call SP_SPACEUSED N'ESPACIOUSADO';  in which case you'll need to rewrite the create table query (as the 2nd use of sp_spaceused returns one result set with 6 columns)

    OR

    [2] You try to figure out how to insert the two different result sets sp_spaceused returns when used with no arguments, in which case you dont need to rewrite the create table query.

    If you choose path 1 I have already answered your question
    If you pick path 2, I don't know if there is a way to 'grab' the two different result sets and somehow combine them into the ESPACIOUSADO table

    Let me know if you need xtra help

    Cheers

     

    Thursday, December 21, 2006 7:42 AM
  • If you just want to list all space used, I believe I got this script from elsewhere as well


    SET NOCOUNT ON
    DECLARE @lngTabCount INTEGER
    DECLARE @lngLoopCount INTEGER
    DECLARE @strTabName SYSNAME

    CREATE TABLE #tTables
    (
    numID INTEGER IDENTITY(1,1)
    ,strTableName SYSNAME
    )

    INSERT INTO #tTables (strTableName)
    SELECT name FROM dbo.sysobjects WHERE xtype = 'u'

    SET @lngTabCount = @@ROWCOUNT
    SET @lngLoopCount = @lngTabCount

    WHILE @lngLoopCount <> 0
    BEGIN
    SET @strTabName = (SELECT strTableName FROM #tTables WHERE numID = @lngLoopCount)
    EXEC sp_spaceused @strTabName
    SET @lngLoopCount = @lngLoopCount - 1
    END

    DROP TABLE #tTables
    GO

    Tuesday, January 9, 2007 12:43 PM
  • thanks for that.
    Tuesday, January 9, 2007 1:51 PM
  • Hi

    I also found this function not working. After spending several hours in finding out, that it is just stupid programmed, I created my own function by altering the built in sp_spaceused.

    Try this out:

    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    -- =============================================
    -- Author:		LotosBluete
    -- Create date: 
    -- Description:	replace sys.sp_spaceused with something more useful
    -- =============================================
    CREATE PROCEDURE proc_spaceused
    	-- Add the parameters for the stored procedure here
    @objname nvarchar(776) = null,		-- The object we want size on.
    @updateusage varchar(5) = false		-- Param. for specifying that
    					-- usage info. should be updated.
    as
    
    declare @id	int			-- The object id that takes up space
    		,@type	character(2) -- The object type.
    		,@pages	bigint			-- Working variable for size calc.
    		,@dbname sysname
    		,@dbsize bigint
    		,@logsize bigint
    		,@reservedpages  bigint
    		,@usedpages  bigint
    		,@rowCount bigint
    
    /*
    **  Check to see if user wants usages updated.
    */
    
    if @updateusage is not null
    	begin
    		select @updateusage=lower(@updateusage)
    
    		if @updateusage not in ('true','false')
    			begin
    				raiserror(15143,-1,-1,@updateusage)
    				return(1)
    			end
    	end
    /*
    **  Check to see that the objname is local.
    */
    if @objname IS NOT NULL
    begin
    
    	select @dbname = parsename(@objname, 3)
    
    	if @dbname is not null and @dbname <> db_name()
    		begin
    			raiserror(15250,-1,-1)
    			return (1)
    		end
    
    	if @dbname is null
    		select @dbname = db_name()
    
    	/*
    	**  Try to find the object.
    	*/
    	SELECT @id = object_id, @type = type FROM sys.objects WHERE object_id = object_id(@objname)
    
    	-- Translate @id to internal-table for queue
    	IF @type = 'SQ'
    		SELECT @id = object_id FROM sys.internal_tables WHERE parent_id = @id and internal_type = 201 --ITT_ServiceQueue
    
    	/*
    	**  Does the object exist?
    	*/
    	if @id is null
    		begin
    			raiserror(15009,-1,-1,@objname,@dbname)
    			return (1)
    		end
    
    	-- Is it a table, view or queue?
    	IF @type NOT IN ('U ','S ','V ','SQ','IT')
    	begin
    		raiserror(15234,-1,-1)
    		return (1)
    	end
    end 
    
    /*
    **  Update usages if user specified to do so.
    */
    
    if @updateusage = 'true'
    	begin
    		if @objname is null
    			dbcc updateusage(0) with no_infomsgs
    		else
    			dbcc updateusage(0,@objname) with no_infomsgs
    		print ' '
    	end
    
    set nocount on
    
    /*
    **  If @id is null, then we want summary data.
    */
    if @id is null
    begin
    	select @dbsize = sum(convert(bigint,case when status & 64 = 0 then size else 0 end))
    		, @logsize = sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))
    		from dbo.sysfiles
    
    	select @reservedpages = sum(a.total_pages),
    		@usedpages = sum(a.used_pages),
    		@pages = sum(
    				CASE
    					-- XML-Index and FT-Index internal tables are not considered "data", but is part of "index_size"
    					When it.internal_type IN (202,204,211,212,213,214,215,216) Then 0
    					When a.type <> 1 Then a.used_pages
    					When p.index_id < 2 Then a.data_pages
    					Else 0
    				END
    			)
    	from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id
    		left join sys.internal_tables it on p.object_id = it.object_id
    
    	/* unallocated space could not be negative */
    	select 
    		database_name = db_name(),
    		database_size = convert (dec (15,0),((convert (dec (15,2),@dbsize) + convert (dec (15,2),@logsize)) 
    			* 8192 / 1024)),
    		'unallocated space' = convert (dec (15,0),(case when @dbsize >= @reservedpages then
    			(convert (dec (15,2),@dbsize) - convert (dec (15,2),@reservedpages)) 
    			* 8192 / 1024 else 0 end))
    
    	/*
    	**  Now calculate the summary data.
    	**  reserved: sum(reserved) where indid in (0, 1, 255)
    	** data: sum(data_pages) + sum(text_used)
    	** index: sum(used) where indid in (0, 1, 255) - data
    	** unused: sum(reserved) - sum(used) where indid in (0, 1, 255)
    	*/
    	,--select
    		reserved = convert (dec (15,0),(@reservedpages * 8192 / 1024)),
    		data = convert (dec (15,0),(@pages * 8192 / 1024) ),
    		index_size = convert (dec (15,0),((@usedpages - @pages) * 8192 / 1024.)),
    		unused = convert (dec (15,0),((@reservedpages - @usedpages) * 8192 / 1024)),
    		unit = 'KB'
    end
    
    /*
    **  We want a particular object.
    */
    else
    begin
    	/*
    	** Now calculate the summary data. 
    	*  Note that LOB Data and Row-overflow Data are counted as Data Pages.
    	*/
    	SELECT 
    		@reservedpages = SUM (reserved_page_count),
    		@usedpages = SUM (used_page_count),
    		@pages = SUM (
    			CASE
    				WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
    				ELSE lob_used_page_count + row_overflow_used_page_count
    			END
    			),
    		@rowCount = SUM (
    			CASE
    				WHEN (index_id < 2) THEN row_count
    				ELSE 0
    			END
    			)
    	FROM sys.dm_db_partition_stats
    	WHERE object_id = @id;
    
    	/*
    	** Check if table has XML Indexes or Fulltext Indexes which use internal tables tied to this table
    	*/
    	IF (SELECT count(*) FROM sys.internal_tables WHERE parent_id = @id AND internal_type IN (202,204,211,212,213,214,215,216)) > 0 
    	BEGIN
    		/*
    		**  Now calculate the summary data. Row counts in these internal tables don't 
    		**  contribute towards row count of original table.
    		*/
    		SELECT 
    			@reservedpages = @reservedpages + sum(reserved_page_count),
    			@usedpages = @usedpages + sum(used_page_count)
    		FROM sys.dm_db_partition_stats p, sys.internal_tables it
    		WHERE it.parent_id = @id AND it.internal_type IN (202,204,211,212,213,214,215,216) AND p.object_id = it.object_id;
    	END
    
    	SELECT 
    		name = OBJECT_NAME (@id),
    		rows = convert (char(11), @rowCount),
    		reserved = convert (dec (15,0), (@reservedpages * 8) ),
    		data =convert (dec (15,0),(@pages * 8)),
    		index_size = convert (dec (15,0),((CASE WHEN @usedpages > @pages THEN (@usedpages - @pages) ELSE 0 END) * 8)),
    		unused = convert (dec (15,0), ((CASE WHEN @reservedpages > @usedpages THEN (@reservedpages - @usedpages) ELSE 0 END) * 8) ),
    		unit = 'KB'
    
    
    end
    Print 'Microsoft sucks'
    
    return (0) -- sp_spaceused
    
    GO
    
    
    
    

     

    Hope this helps others.

     

     

     

    Friday, September 16, 2011 6:54 PM