recursive sp_spaceused query

  • I have a few servers and approx. 40 DB's per server... I'm trying to determine the size of all DBs which I can get by simply using "sp_spaceused" against each database however it is a lot of work to do it manually.

    I can't find anything online that can loop it through and give me the results. I've tried the below:

    DECLARE @DBName TABLE (

    id INT IDENTITY(1,1),

    Name VARCHAR(255)

    )

    DECLARE @MinID INT

    DECLARE @MaxID INT

    DECLARE @DB VARCHAR(255)

    declare @sql varchar(max)

    INSERT INTO @DBName (

    Name

    )

    SELECT Name

    FROM [master].[dbo].[sysdatabases]

    SELECT @MinID = (SELECT MIN(ID) FROM @DBName)

    SELECT @MaxID = (SELECT MAX(ID) FROM @DBName)

    WHILE @MinID <= @MaxID

    BEGIN

    SELECT @sql = ('USE ' + Name + ' GO sp_spaceused' )

    FROM @DBName

    WHEREID = @MinID

    select @sql

    SELECT @MinID = @MinID + 1

    END

    It gives me the outcome of "USE [DBName] GO sp_spaceused" but it doesn't work as it throws an error since you only need to say "sp_spaceused" on the DB itself..

    any idea or does someone have a script previously used?

  • as usual ... soon as I post it, I find the solution.

    EXECUTE master.sys.sp_MSforeachdb 'USE [?]; EXEC sp_spaceused'

    Is there anyway to get this into 1 table though as its across all the multiple result sets??

  • Tava here is what i created, based on a post her eon SCC that i found;

    I've enhanced it over time, but i just put this in master and run it on my server when i need to peek at disk space and free space.

    IF OBJECT_ID('[dbo].[sp_dbspaceused]') IS NOT NULL

    DROP PROCEDURE [dbo].[sp_dbspaceused]

    GO

    --#################################################################################################

    --developer utility function added by Lowell, used in SQL Server Management Studio

    --Purpose: disk psace per db, with consdieration for db's that ar eoffline

    --#################################################################################################

    CREATE PROCEDURE sp_dbspaceused

    AS

    BEGIN

    SET NOCOUNT ON

    DECLARE @CurrentDB NVARCHAR(128)

    -- The table #tblServerDatabases holds the names of databases on the server.

    -- This table is used here to allow us to loop through each database,

    -- rather than to run undocumented procedures, such as sp_msforeachdb

    -- (which eliminated the need for a loop).

    IF OBJECT_ID('tempdb..#tblServerDatabases', 'U') IS NOT NULL

    DROP TABLE #tblServerDatabases

    CREATE TABLE #tblServerDatabases (DBName NVARCHAR(128),state_desc NVARCHAR(128))

    -- The table #tblDBFilesExtendedInfo holds the data and log files info

    -- (name, size, used, free spaces, etc.).

    IF OBJECT_ID('tempdb..#tblDBFilesExtendedInfo', 'U') IS NOT NULL

    DROP TABLE #tblDBFilesExtendedInfo

    CREATE TABLE #tblDBFilesExtendedInfo (

    Idx INT IDENTITY(1, 1),

    FileID INT,

    FileGroupID INT,

    TotalExtents BIGINT,

    UsedExtents BIGINT,

    DBFileName NVARCHAR(128),

    LogicalFileName NVARCHAR(128),

    DBFilePath NVARCHAR(1024),

    DBFileType VARCHAR(16),

    DBName NVARCHAR(128),

    [TotalFileSize(MB)] MONEY,

    [TotalUsed(MB)] MONEY,

    [TotalFree(MB)] MONEY,

    [SpaceUsed(%)] MONEY,

    Status INT)

    -- This table will hold the output of sp_helpfile, for each database.

    -- This is needed in order to get the log file path and file name.

    IF OBJECT_ID('tempdb..#tblDBFilesBasicInfo', 'U') IS NOT NULL

    DROP TABLE #tblDBFilesBasicInfo

    CREATE TABLE #tblDBFilesBasicInfo (

    DBName NVARCHAR(128),

    DBFileName NVARCHAR(128),

    FileID INT,

    FilePath NVARCHAR(1024),

    FileGroupDesc NVARCHAR(128),

    FileSizeKB NVARCHAR(64),

    MaxSizeDesc NVARCHAR(64),

    Growth NVARCHAR(64),

    Usage NVARCHAR(64))

    -- First - the data files are handled throught the

    -- DBCC SHOWFILESTATS command.

    INSERT INTO #tblServerDatabases (DBName,state_desc)

    SELECT dbz.[name],state_desc

    FROM master.sys.databases dbz

    SELECT @CurrentDB = MIN(DBName)

    FROM #tblServerDatabases

    WHERE state_desc='ONLINE'

    WHILE @CurrentDB IS NOT NULL

    BEGIN

    INSERT INTO #tblDBFilesExtendedInfo(

    FileID,

    FileGroupID,

    TotalExtents,

    UsedExtents,

    DBFileName,

    DBFilePath)

    EXEC ('USE [' + @CurrentDB + '] DBCC SHOWFILESTATS')

    UPDATE #tblDBFilesExtendedInfo

    SET DBName = @CurrentDB,

    DBFileType = 'Data File'

    WHERE DBName IS NULL

    -- Run the sp_helpfile in order to get log file data.

    INSERT INTO #tblDBFilesBasicInfo(

    DBFileName,

    FileID,

    FilePath,

    FileGroupDesc,

    FileSizeKB,

    MaxSizeDesc,

    Growth,

    Usage)

    EXEC ('USE [' + @CurrentDB + '] EXEC sp_helpfile ')

    UPDATE #tblDBFilesBasicInfo

    SET DBName = @CurrentDB

    WHERE DBName IS NULL

    SELECT @CurrentDB = MIN(DBName)

    FROM #tblServerDatabases WITH (NOLOCK)

    WHERE DBName > @CurrentDB

    AND state_desc='ONLINE'

    END

    -- Update the total file size, used and free space, based on the

    -- extents information returned from DBCC SHOWFILESTATS.

    UPDATE #tblDBFilesExtendedInfo

    SET [DBFileName] = RIGHT(DBFilePath, CHARINDEX('\', REVERSE(DBFilePath)) -1),

    [TotalFileSize(MB)] = CAST(((TotalExtents*64) / 1024.00) AS MONEY),

    [TotalUsed(MB)] = CAST(((UsedExtents*64) / 1024.00) AS MONEY),

    [TotalFree(MB)] = CAST(((TotalExtents*64) / 1024.00) AS MONEY)

    - CAST(((UsedExtents*64) / 1024.00) AS MONEY),

    [SpaceUsed(%)] = CASE

    WHEN CAST(((TotalExtents*64) / 1024.00) AS MONEY) = 0.0 THEN 0.0

    ELSE (CAST(((UsedExtents*64) / 1024.00) AS MONEY)*100)

    / CAST(((TotalExtents*64) / 1024.00) AS MONEY)

    END

    -- We are now done with the data file statuses, and we shall move

    -- on to get the log files info, by using DBCC SQLPERF(LOGSPACE)

    INSERT INTO #tblDBFilesExtendedInfo (DBName, [TotalFileSize(MB)], [SpaceUsed(%)], Status)

    EXEC('DBCC SQLPERF(LOGSPACE)')

    UPDATE a

    SET [TotalUsed(MB)] = (a.[SpaceUsed(%)]/100.00)*a.[TotalFileSize(MB)],

    [TotalFree(MB)] = (1.0 - (a.[SpaceUsed(%)]/100.00))*a.[TotalFileSize(MB)],

    DBFileType = 'Log file',

    DBFilePath = b.FilePath,

    DBFileName = RIGHT(b.FilePath, CHARINDEX('\', REVERSE(b.FilePath)) -1)

    FROM #tblDBFilesExtendedInfo a

    INNER JOIN #tblDBFilesBasicInfo b

    ON a.DBName = b.DBName

    WHERE a.DBFileType IS NULL

    AND b.Usage = 'log only'

    --we want the logical file name as well, in case we need it for other purposes, like getting everything in a single spot to move files

    UPDATE MyTarget

    SET MyTarget.LogicalFileName = mff.name

    FROM #tblDBFilesExtendedInfo MyTarget

    INNER JOIN sys.master_files mff

    ON MyTarget.DBFilePath = mff.physical_name

    SET NOCOUNT OFF

    SELECT * FROM #tblServerDatabases WHERE DBname NOT IN(SELECT DbName FROM #tblDBFilesExtendedInfo)

    -- That's it. We now need to return a readable recordset.

    SELECT DBName,

    DBFileType,

    LogicalFileName,

    DBFileName,

    DBFilePath,

    [TotalFileSize(MB)],

    [TotalUsed(MB)],

    [SpaceUsed(%)],

    [TotalFree(MB)]

    FROM #tblDBFilesExtendedInfo WITH (NOLOCK)

    UNION ALL

    SELECT DBname, state_desc,state_desc,state_desc,state_desc,NULL,NULL,NULL,NULL FROM #tblServerDatabases WHERE DBname NOT IN(SELECT DbName FROM #tblDBFilesExtendedInfo)

    ORDER BY DBName ASC, DBFileType ASC

    -- Cleanup

    IF OBJECT_ID('tempdb..#tblServerDatabases', 'U') IS NOT NULL

    DROP TABLE #tblServerDatabases

    IF OBJECT_ID('tempdb..#tblDBFilesExtendedInfo', 'U') IS NOT NULL

    DROP TABLE #tblDBFilesExtendedInfo

    IF OBJECT_ID('tempdb..#tblDBFilesBasicInfo', 'U') IS NOT NULL

    DROP TABLE #tblDBFilesBasicInfo

    END -- PROC

    GO

    --#################################################################################################

    --Mark as a system object

    EXECUTE sp_ms_marksystemobject '[dbo].[sp_dbspaceused]'

    --#################################################################################################

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Thanks for sharing that script... appreciate it

  • Wouldn't it all be easier to just use a SELECT from sys.master_files? Do you really need to know things like the amount of free space and how much space was used by data vs indexes?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (5/12/2016)


    Wouldn't it all be easier to just use a SELECT from sys.master_files? Do you really need to know things like the amount of free space and how much space was used by data vs indexes?

    Unfortunately they want to know the whole lot... Index vs data vs unallocated

Viewing 6 posts - 1 through 5 (of 5 total)

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