Collecting disk information - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Collecting disk information

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-01-11 : 08:19:36

Thandi writes "Hi

I need to know if possible, how to collect information like disk space free from MSSQL server into an xls sheet on another server for administration.
Or is there a way to notify a dba when disk space is below/reaching a set limit that requires attention.

Thank you"

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2006-01-11 : 08:59:34

Q b)...use SQL alerts and Profiler

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2006-01-11 : 20:54:07

I forget where I found this code - probably here.

<CODE>
/*---------------------------------------------------------------------------
--- DiskAvailableSpace
---
--- Description: Table to hold historical disk space for trend analysis
---
---------------------------------------------------------------------------*/

if exists (select * from dbo.sysobjects
where id = object_id(N'dbo.DiskAvailableSpace')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
drop table dbo.DiskAvailableSpace
end
GO

create table dbo.DiskAvailableSpace (
ID int IDENTITY (1, 1) not Null,
DriveLetter char (1),
FreeMB int not Null,
TimeCollected smalldatetime not Null
constraint DF_DiskAvailableSpace_TimeCollected default (Current_Timestamp)
)
GO

/*---------------------------------------------------------------------------
--- usp_Trackusp_TrackDiskAvailableSpace
---
--- Description: Determine the current free space available and store the
--- results in the usp_TrackDiskAvailableSpace table'
---
--- Inputs: ---
---
--- Outputs: Returns - 0 iff OK; < 0 if error; > 0 if warning
---
---------------------------------------------------------------------------*/

--The following is the code for this stored procedure. After this stored procedure is executed, the results will be saved in the DiskAvailableSpace table. If you schedule this procedure to run every week, after a few weeks, you will be able to draw a chart of disk usage. This can be pretty valuable for trend analysis.

if exists(select * from Information_Schema.routines
where Routine_Name = 'usp_TrackDiskAvailableSpace'
and Routine_Schema = 'dbo'
and Routine_Type = 'PROCEDURE') -- vs 'FUNCTION'
drop proc dbo.usp_TrackDiskAvailableSpace
go

create procedure dbo.usp_TrackDiskAvailableSpace
as
begin
set NoCount ON

/*--------------------*/
/*--- Declarations ---*/
/*--------------------*/

declare
@Err int

/*----------------------*/
/*--- Initialization ---*/
/*----------------------*/

set @Err = 0 /* 0 ==> OK */

--- -------------------------------------------------------------------------
--- Create a temp table to hold disk space information
--- -------------------------------------------------------------------------

if exists (select * from tempdb..sysobjects
where id = object_id(N'tempdb..#disk_free_space'))
begin
drop table #disk_free_space
end

create table #disk_free_space (
DriveLetter char(1) not Null,
FreeMB int not Null
)

set @Err = @@Error
if @Err <> 0
begin
goto CommonExit
end

--- -------------------------------------------------------------------------
--- Populate #disk_free_space with data
--- -------------------------------------------------------------------------

insert into #disk_free_space
exec master..xp_fixeddrives

set @Err = @@Error
if @Err <> 0
begin
goto CommonExit
end

--- -------------------------------------------------------------------------
--- Populate DiskAvailableSpace with free space data.
--- -------------------------------------------------------------------------

insert into DiskAvailableSpace
(DriveLetter, FreeMB)
select DriveLetter, FreeMB
from #disk_free_space

set @Err = @@Error
if @Err <> 0
begin
goto CommonExit
end

--- -------------------------------------------------------------------------
--- Clean up. Drop the temp table
--- -------------------------------------------------------------------------

drop table #disk_free_space

--set @Err = @@Error
--if @Err <> 0
-- begin
-- goto CommonExit
-- end

/*-------------------------*/
/*--- Clean-Up and Exit ---*/
/*-------------------------*/

CommonExit:

return @Err

end -- ObjectName
GO
</CODE>

Westley
Posting Yak Master

229 Posts

Posted - 2006-01-12 : 04:11:03

I'm working on something like this, will post the code out once I got it work in csv (got it working in xls would like to have it in csv as well)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources