-2

Is it possible to script out the sp_spaceused stored procedure so I can edit it to include log space information and sort of create my own custom stored procedure?

I would like to put the results in a temp table so I can use it in a project I'm working on, so I also need to condense the 2 result sets into one result set.

No idea where to begin. Is there an easier way to get the same data?

  • Open SSMS, expand the system databases container, expand master, go to programmability/stored procedures/system stored procedures and then scroll down to sys.sp_spaceused, right click -> modify – Mr.Brownstone Jun 10 '16 at 15:17
5

Yeah you can do by using sp_helptext @objname = 'sp_spaceused'

It's going to be fiddly to do what you want... simply because you'll need to unpick the stored procedure and re-engineer it, so to speak.

I have sort of done the same thing myself for our in-house SQL inventory app that the DBA's use. The following produces a single result set which brings together the two result sets in sp_spaceused and also adds in some info from DBCC SQLPERF (logspace)

It uses dynamic SQL to get the data for all databases in the instance. It might not be exactly what you want, but it might do something for you.

Note that sometimes we have to explicitly COLLATE the joins so let me know if you get COLLATION errors and I'll try to help somehow.

The script:

/* ---------------------- */
/* Drop temporary objects */
/* ---------------------- */
IF OBJECT_ID('tempdb..#tmpLogUsage') IS NOT NULL
    DROP TABLE #tmpLogUsage;

IF OBJECT_ID('tempdb..##spaceused') IS NOT NULL
    DROP TABLE ##spaceused

/* ---------------------- */
/*    Create Variables    */
/* ---------------------- */
CREATE TABLE ##spaceused (
    databasename VARCHAR(500)
    ,database_size VARCHAR(50)
    ,unallocated_space VARCHAR(50)
    ,reserved VARCHAR(50)
    ,data VARCHAR(50)
    ,index_size VARCHAR(50)
    ,unused VARCHAR(50)
    ,dbsize VARCHAR(50)
    ,logsize VARCHAR(50)
    )

DECLARE @databases AS TABLE (
    ID INT identity(1, 1)
    ,databasename VARCHAR(500)
    )

INSERT INTO @databases
SELECT '['+NAME+']'
FROM sys.databases
WHERE database_id > 4 AND state = 0

DECLARE @recordcount AS INT = (
        SELECT MAX(ID)
        FROM @databases
        )
DECLARE @whilecount AS INT = 1
DECLARE @sql AS VARCHAR(MAX)
DECLARE @databasename AS VARCHAR(500)

/* ---------------------- */
/*   Get Log Information  */
/* ---------------------- */
DECLARE @logspace AS TABLE (
    DatabaseName VARCHAR(500)
    ,LogSizeMB DECIMAL(16, 2)
    ,LogSpaceUsedPerc DECIMAL(16, 2)
    ,STATUS INT
    )

INSERT INTO @logspace
EXECUTE ('DBCC SQLPERF (logspace)')

SELECT ls.DatabaseName
    ,ls.LogSpaceUsedPerc
    ,ROUND((ls.LogSizeMB / 100) * ls.LogSpaceUsedPerc, 2) AS LogSizeActualMB
    ,db.log_reuse_wait_desc
    ,db.recovery_model_desc
INTO #tmpLogUsage
FROM @logspace ls
INNER JOIN sys.databases db ON ls.DatabaseName = db.NAME 

/* ------------------------------------------------ */
/*  Loop through databases and get space used data  */
/* ------------------------------------------------ */
WHILE @whilecount <= @recordcount
BEGIN
    SET @databasename = (
            SELECT databasename
            FROM @databases
            WHERE ID = @whilecount
            )
    SET @sql = (
            'USE ' + @databasename + 
            ' INSERT INTO ##spaceused SELECT
    db_name() AS [database_name]
    ,ltrim(str((convert(DEC(15, 2), sf.dbsize) + convert(DEC(15, 2), sf.logsize)) * 8192 / 1048576, 15, 2) + '' MB'') AS [database_size]
    ,ltrim(str((
                CASE 
                    WHEN sf.dbsize >= pages.reservedpages
                        THEN (convert(DEC(15, 2), sf.dbsize) - convert(DEC(15, 2), pages.reservedpages)) * 8192 / 1048576
                    ELSE 0
                    END
                ), 15, 2) + '' MB'') AS [unallocated space]
    ,ltrim(str(pages.reservedpages * 8192 / 1024., 15, 0) + '' KB'') AS [reserved]
    ,ltrim(str(pages.pages * 8192 / 1024., 15, 0) + '' KB'') AS data
    ,ltrim(str((pages.usedpages - pages.pages) * 8192 / 1024., 15, 0) + '' KB'') AS index_size
    ,ltrim(str((pages.reservedpages - pages.usedpages) * 8192 / 1024., 15, 0) + '' KB'') AS unused

    ,ltrim(str((convert(DEC(15, 2), sf.dbsize)) * 8192 / 1048576, 15, 2) + '' MB'') AS dbsize
    ,ltrim(str((convert(DEC(15, 2), sf.logsize)) * 8192 / 1048576, 15, 2) + '' MB'') AS logsize
FROM (
    SELECT sum(convert(BIGINT, CASE 
                    WHEN STATUS & 64 = 0
                        THEN size
                    ELSE 0
                    END)) AS dbsize
        ,sum(convert(BIGINT, CASE 
                    WHEN STATUS & 64 <> 0
                        THEN size
                    ELSE 0
                    END)) AS logsize
    FROM dbo.sysfiles
    ) sf
    ,(
        SELECT sum(a.total_pages) AS reservedpages
            ,sum(a.used_pages) AS usedpages
            ,sum(CASE 
                    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) AS pages
        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
        ) pages;'
            )

    EXECUTE (@sql);

    SET @whilecount += 1
END

/* ----------------- */
/*   Output Dataset  */
/* ----------------- */
SELECT su.databasename AS DatabaseName
    ,lu.recovery_model_desc AS DatabaseRecoveryModel
    ,su.database_size AS DatabaseSizeIncLog
    ,su.unallocated_space AS UnallocatedSpace
    ,su.reserved AS ReservedbySQLServer
    ,su.data AS DataSize
    ,su.index_size AS IndexSize
    ,su.unused AS AllocatedUnused
    ,su.dbsize AS DataFileSize
    ,su.logsize AS LogFileSize
    ,lu.LogSpaceUsedPerc
    ,lu.LogSizeActualMB
    ,lu.log_reuse_wait_desc AS LogWaitDescription
FROM ##spaceused su
INNER JOIN #tmpLogUsage lu ON su.databasename = lu.DatabaseName
  • This worked great, how would add autogrowth settings for log and mdf? – Fallvind Jun 10 '16 at 16:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.