Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Create Report of Space Used/Allocated and Free Space in one Instance Expand / Collapse
Author
Message
Posted Monday, March 9, 2009 9:37 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 10:18 AM
Points: 186, Visits: 816
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

Thanks and regards,
JMSM ;)
Post #671653
Posted Monday, March 9, 2009 11:32 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Thursday, August 28, 2014 2:34 AM
Points: 1,454, Visits: 2,982
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 »

Add to briefcase

Permissions Expand / Collapse