Automated Monitoring Database Size Using sp_spaceused

  • Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/dpoole/2771.asp

  • Thanks for including discussion of your methodical development and validation of this DBA utility you present.  It's my opinion that writing and using scripts built on sp_msforeachdb is the mark of a true DBA.  And thanks for mentioning that "allow updates to system tables" can be set to 1 in SQL Server 2005-- but to no effect.  As a **database** programmer, I want info about the system tables and procedures, not "developer" junk (CLR) that encourages application programmers to regard the database as just a box that holds data.

    _________________
    "Look, those sheep have been shorn."
    data analyst replies, "On the sides that we can see.."

  • Great, thoughtful article. will deploy asap.

  • This cursorless loop was a new trick for me, and very appreciated. 


    WHILE @TableName IS NOT NULL

    BEGIN

    SELECT @TableName=MIN(TableName)

    FROM @UserTables

    WHERE TableName>@TableName

    IF @TableName IS NOT NULL

    BEGIN

    INSERT INTO #T

    exec sp_dba_spaceused @TableName

    END

    END


  • This trick eliminates loop as well as cursor. 

     

    1) generate a script

    select 'insert into #t exec sp_dba_spaceused ' + name

    from sysobjects where type = 'u'

    order by 1

     

    2) run the script

     

    When writing an automated process, I usually use the loop rather than this "select literal with select set to generate a script" trick, but it is possible to automate running the script that the select statement generates.

    _________________
    "Look, those sheep have been shorn."
    data analyst replies, "On the sides that we can see.."

  • I seem to remember that there is a non-documented stored procedure that runs the resultset of a query.

    In some situations I do build up a string of commands and then execute that string but I am always mindful of the limited string processing available within SQL Server.

    In SQL2000 sp_executeSQL required an NVARCHAR argument which meant that any statement had to be under 4,000 characters long.

    EXEC required a VARCHAR which meant a limit of 8,000 characters.

    Of course, now in SQL2005 we have VARCHAR(MAX) and NVARCHAR(MAX) to play with.

  • If anyone is interested the SQL2000 equivalent is

    USE Master
    GO
    CREATE PROC dbo.sp_DBA_spaceused_AllTables2
    @updateusage varchar(5)='false'
    AS
    SET NOCOUNT ON
    
    IF @updateusage IS NOT NULL
    BEGIN
    -- Allow for case sensitivity
    SET @updateusage=LOWER(@updateusage)
    IF @updateusage NOT IN ('true','false')
    BEGIN
    RAISERROR(15143,-1,-1,@updateusage)
    RETURN(1)
    END
    END
    
    -- Retrieve the current page size for the OS
    DECLARE @KB DEC(15)
    SELECT @KB = low/1024.0
    FROM master.dbo.spt_values
    WHERE number=1
    AND type='E'
    
    SELECT
    MAX(DB_NAME()) AS DatabaseName ,
    MAX(GETDATE()) AS DateSampled ,
    U.Name+'.'+O.Name AS TableName ,
    MAX(CASE WHEN I.Indid<2 THEN I.rows ELSE 0 END) AS rows,
    SUM(CASE WHEN I.Indid IN(0,1,255) THEN I.reserved ELSE 0 END) * @KB AS reserved ,
    SUM(CASE WHEN I.indid<2 THEN I.dpages ELSE 0 END
    + CASE WHEN I.indid=255 THEN used ELSE 0 END) * @KB AS data ,
    SUM(CASE WHEN I.indid<2 THEN used-dpages ELSE 0 END) * @KB AS index_size ,
    SUM(CASE WHEN I.indid IN(0,1,255) THEN I.reserved - I.used ELSE 0 END) *@KB AS Unused
    
    FROMdbo.sysobjects AS O
    INNER JOIN dbo.sysindexes AS I ON O.id = I.id
    INNER JOIN dbo.sysusers AS U ON O.uid = U.uid
    GROUP BY U.Name+'.'+O.Name 
    GO
    
  • Quite nice - however, your stored proc (like all the ones I've seen so far) does not take into account space used by XML indices - and that space is MASSIVE! I see very large discrepancies between any "home made" solutions using sys.indexes etc., and the output by sp_spaceused, when applied to a table with XML data and indices.

  • I must admit I am somewhat ambivalent to storing XML in a relational database.

    If I am going to store XML data it is because I want to retrieve it in entirety. Being able to search an XML document within a relational database engine strikes me as a kludge. If I wanted to search the content of an XML document I would have shredded into tables. It is one of those arguments where you either support "The Judean Peoples Front" or "The peoples Front of Judea"

    My method actually used the source of sp_spaceused minus some of the branches.

  • Thanks, David - and I agree, XML in a relational database might not be the best choice in all cases. But that discussion aside - what I was really hoping to find out is how to include the XML indices in your code snippet, e.g. how do I tweak your code so that the XML indices (which use up A LOT of space!) are also included? Any ideas? Anyone?

    Thanks!

    Marc

  • I'll look into it Marc. It should be possible but there is one thing I have found with SQL2005 and system tables. Basically you can't query them directly, you can only query the management views therefore coming up with a generic solution may not be possible.

    Just to clarify in SQL2000 you have sysobjects as a system table. In SQL2005 you have sysobjects as a view on underlying tables.

  • I'd like to give this a try on my 2000 engines. Can someone please post the SQL2000 equivalent for sp_DBA_spaceused?

  • Why doesn't Microsoft just provide this information in a DMV and be done with it?

    Sure would make a few DBAs happy...

  • What might it mean if I get negative numbers for the "Unused" column? Would having indexes on a separate filegroup affect these numbers?

  • what if we have partitioned tables :); row counts do not show up based on this script; I will work on getting that info to the script. Thanks David

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply