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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Create Report of Space Used/Allocated and...

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

Rate Topic

Display Mode

Topic Options

Author

Message

JMSM

Posted Monday, March 09, 2009 9:37 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 8:18 AM
Points: 185, Visits: 750

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 ;)

Post #671653

Carolyn Richardson

Posted Monday, March 09, 2009 11:32 AM

Ten Centuries

Group: General Forum Members
Last Login: Friday, May 17, 2013 4:52 AM
Points: 1,397, Visits: 2,738

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),
[size] 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([size]) * 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 [size] = 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([size] 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

Post #671781

« Prev Topic | Next Topic »

Permissions