Create Report of Space Used/Allocated and Free Space in one Instance

  • Hello again,

    I need to get all space used/space allocated/space free of all databases in one instance.

    I only need the final result (that gives me one row) with sumatory of the Total Space Used, Total Space Allocated, Total Free Space and the instance Name.

    The query(ies) must aplly to SQL Server 2000 and in SQL Server 2005.

    I need get a query that gaves me the following information in SQL Server 2000 and in SQL Server 2005.

    Instance Name -> xyz

    Total Space Allocated -> in (MB) / (GB)

    Total Space Used -> in (MB) / (GB)

    Total Free Space Used -> in (MB) / (GB)

    Hope you can help me in this query :sick:

    Thanks and regards,

    JMSM 😉

  • This is almost what you want....

    CREATE PROCEDURE [dbo].[usp_dbspace]

    AS

    /*=========================================================================

    Stored Proc:usp_dbspace

    Purpose:Generates System settings information

    Author:Carolyn Richardson

    Date:1/09/2007

    Description:Lists Database Space used

    =========================================================================*/

    SET NOCOUNT ON

    DECLARE @Result Table (

    [DBName] Varchar(100),

    int,

    Log_Size float,

    Log_Space float

    )

    DECLARE @DBName Varchar(100)

    DECLARE @SIZE int

    declare @RECCNT varchar(500)

    declare @DeviceName varchar(500)

    declare @CMD Nvarchar(500)

    DECLARE tmpcursor CURSOR FOR select DBName from @Result

    INSERT INTO @Result (DBName)

    Select [name] from sysdatabases where [status] <> 536

    IF EXISTS (Select [name] from sysobjects where xtype = 'u' and [name] = '#temp_table')

    DROP TABLE #temp_table

    create table #temp_table (

    Database_Name varchar(100),

    Log_Size float,

    Log_Space float,

    Status varchar(100)

    )

    insert into #temp_table

    EXEC ('DBCC sqlperf(LOGSPACE) WITH NO_INFOMSGS')

    declare @temp_table table (

    Database_Name varchar(100),

    Log_Size float,

    Log_Space float,

    Status varchar(100)

    )

    insert into @temp_table

    select * from #temp_table

    drop table #temp_table

    OPEN tmpcursor

    FETCH NEXT FROM tmpcursor INTO @DBName

    WHILE (@@fetch_status <> -1)

    BEGIN

    IF (@@fetch_status <> -2)

    BEGIN

    SET @CMD = N'use ' + quotename(@DBName) + N' SELECT @SIZE=(SUM() * 8) from sysfiles'-- where [name] = @RECCNT'

    exec sp_executesql @CMD,

    N'@DeviceName varchar(100) out, @SIZE int out, @RECCNT varchar(100)',

    @DBName,

    @SIZE out,

    @RECCNT

    update @Result

    set = LTRIM(RTRIM(@SIZE))

    where DBName = @DBName

    update @Result set Log_Size = (Select Log_Size from @temp_table where Database_Name = @DBName) where DBName = @DBName

    update @Result set Log_Space = (Select Log_Space from @temp_table where Database_Name = @DBName) where DBName = @DBName

    END

    FETCH NEXT FROM tmpcursor INTO @DBName

    END

    select DBName, CONVERT(char,CAST( as int),1) as 'DB size', CONVERT(char,CAST([Log_Size] as int),1) as 'Log Size', CONVERT(char,CAST([Log_Space] as int),1) as 'Log Space Used (%)' from @Result

    order by DBName

    CLOSE tmpcursor

    DEALLOCATE tmpcursor

    SET NOCOUNT OFF

    Facts are stubborn things, but statistics are more pliable - Mark Twain
    Carolyn
    SQLServerSpecialists[/url]

Viewing 2 posts - 1 through 1 (of 1 total)

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