March 9, 2009 at 9:37 am
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 😉
March 9, 2009 at 11:33 am
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply