Harddrives space on your server

** This Procedure will check harddrives space on your server as well as on all
** linked server and send an email in case running out of space.
** Note:
** 1- Should be created in master database
** 2- SQL Mail has to be running in your sql server.
** 3- Linked servers have to be linked in the server where this stored procedure
** is running from
*/
create Procedure BSP_diskcheck as
declare @servname varchar(20)
declare @subject2 varchar(30)
declare @query2 varchar(50)
declare @minspace int
declare server_name cursor for select srvname from sysservers
open server_name
fetch next from server_name into @servname
WHILE @@FETCH_STATUS = 0
BEGIN
if object_id('fixeddrives') is null
create table fixeddrives
(drive char(1),
driveSize int)
else Drop Table fixeddrives
Insert into fixeddrives EXEC (@servname +'.master..xp_fixeddrives')
select * from fixeddrives where drivesize < 100
if @@rowcount>0
Begin
select @subject2= @servname + ' Running Out of space'
select @query2= 'select * from fixeddrives where drivesize<100'
if @servname='server1'
EXEC master..xp_sendmail @recipients = 'sysamin1@company.com',
@query=@query2,
@subject =@subject2
if @servname='server2'
EXEC master..xp_sendmail @recipients = 'sysadmin2@company.com',
@copy_recipients ='dba@company.com',
@query=@query2,
@subject =@subject2
end --@@rowcount
drop table fixeddrives
Fetch next from server_name into @servname
End --While
close server_name
DEALLOCATE server_name



next2none is what others feel about the maverick

Thanx Luis.... il work on more scripts and shall post to the forum...

Regards
Sidh

next2none is what others feel about the maverick

<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by satya</i><br /><br />I would refrain creating such USP on a system database and besides it is an undocumented SP and may be removed in next releases. <br />Don't you get same information using XP_FIXEDDRIVES, Only additional bit on the script is to send the results as a mail attachment.<br /><br /><hr noshade size="1"><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Contributing Editor & Forums Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided �AS IS� with no rights for the sake of <i>knowledge sharing.</i></font id="size1"></font id="teal"></center><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br /><br />This one might be a bit more what people are looking for... [<img src='/community/emoticons/emotion-2.gif' alt='' />]<br /><pre id="code"><font face="courier" size="2" id="code"><br />/******************************************************************************<br />** © Raylev Database Support & Consulting, 2005.<br />** This script is provided as is, and it's effects are not liable<br />**for any impacts/damage to your systems.<br />**<br />**<a target="_blank" href=http://members.cox.net/raylev.systems/>http://members.cox.net/raylev.systems/</a><br />*******************************************************************************<br />**<br />**Name: usp_GetDrive_Info.sql<br />**<br />**Description: Returns a drive list and relevant info a DBA wants...<br />** <br />**<br />**Return values: Drive usage.<br />**<br />**Author: G. Rayburn<br />**<br />**Date: 11/01/2005<br />**<br />**ToDo: <br />**<br />**<br />*******************************************************************************<br />**Modification History<br />*******************************************************************************<br />**<br />**Initial Creation: 11/01/2005 G. Rayburn<br />**<br />*******************************************************************************<br />**<br />******************************************************************************/<br /><br />SET NOCOUNT ON<br /><br /><br />IF EXISTS (SELECT name FROM tempdb..sysobjects WHERE name = '##_DriveSpace')<br />DROP TABLE ##_DriveSpace<br /><br />IF EXISTS (SELECT name FROM tempdb..sysobjects WHERE name = '##_DriveInfo')<br />DROP TABLE ##_DriveInfo<br /><br /><br />DECLARE @Result INT<br />, @objFSO INT<br />, @Drv INT <br />, @cDrive VARCHAR(13) <br />, @Size VARCHAR(50) <br />, @Free VARCHAR(50)<br />, @Label varchar(10)<br /><br />CREATE TABLE ##_DriveSpace <br />(<br /> DriveLetter CHAR(1) not null<br />, FreeSpace VARCHAR(10) not null<br /><br /> )<br /><br />CREATE TABLE ##_DriveInfo<br />(<br />DriveLetter CHAR(1)<br />, TotalSpace int<br />, FreeSpace int<br />, Label varchar(10)<br />)<br /><br />INSERT INTO ##_DriveSpace <br />EXEC master.dbo.xp_fixeddrives<br /><br /><br />-- Iterate through drive letters.<br />DECLARE curDriveLetters CURSOR<br />FOR SELECT driveletter FROM ##_DriveSpace<br /><br />DECLARE @DriveLetter char(1)<br />OPEN curDriveLetters<br /><br />FETCH NEXT FROM curDriveLetters INTO @DriveLetter<br />WHILE (@@fetch_status &lt;&gt; -1)<br />BEGIN<br />IF (@@fetch_status &lt;&gt; -2)<br />BEGIN<br /><br /> SET @cDrive = 'GetDrive("' + @DriveLetter + '")' <br /><br />EXEC @Result = sp_OACreate 'Scripting.FileSystemObject', @objFSO OUTPUT <br /><br />IF @Result = 0 <br /><br />EXEC @Result = sp_OAMethod @objFSO, @cDrive, @Drv OUTPUT <br /><br />IF @Result = 0 <br /><br />EXEC @Result = sp_OAGetProperty @Drv,'TotalSize', @Size OUTPUT <br /><br />IF @Result = 0 <br /><br />EXEC @Result = sp_OAGetProperty @Drv,'FreeSpace', @Free OUTPUT <br /><br />IF @Result = 0 <br /><br />EXEC @Result = sp_OAGetProperty @Drv,'VolumeName', @Label OUTPUT <br /><br />IF @Result &lt;&gt; 0 <br /> <br />EXEC sp_OADestroy @Drv <br />EXEC sp_OADestroy @objFSO <br /><br />SET @Size = (CONVERT(BIGINT,@Size) / 1048576 )<br /><br />SET @Free = (CONVERT(BIGINT,@Free) / 1048576 )<br /><br />INSERT INTO ##_DriveInfo<br />VALUES (@DriveLetter, @Size, @Free, @Label)<br /><br />END<br />FETCH NEXT FROM curDriveLetters INTO @DriveLetter<br />END<br /><br />CLOSE curDriveLetters<br />DEALLOCATE curDriveLetters<br /><br />PRINT 'Drive information for server ' + @@SERVERNAME + '.'<br />PRINT ''<br /><br />-- Produce report.<br />SELECT DriveLetter<br />, Label<br />, FreeSpace AS [FreeSpace MB]<br />, (TotalSpace - FreeSpace) AS [UsedSpace MB]<br />, TotalSpace AS [TotalSpace MB]<br />, ((CONVERT(NUMERIC(6,0),FreeSpace) / CONVERT(NUMERIC(6,0),TotalSpace)) * 100) AS [Percentage Free]<br /><br />FROM ##_DriveInfo<br />ORDER BY [DriveLetter] ASC<br />GO<br /><br />DROP TABLE ##_DriveSpace<br />DROP TABLE ##_DriveInfo</font id="code"></pre id="code">

Very nice Satya, many thanks again. One of these days I'll figure out how the code formatting works (I can't seem to get it right, still). <img src='/community/emoticons/emotion-1.gif' alt='' />

Help Sayta or anyone else...

I know you posted your code "Edited by - satya on 08/03/2006 02:42:30" but I just found it.

I don't see how this code can be used for a linked server... I see N/As code can be run against a linked server but I don't see how Sayta's code can run against a linked server

Thanks

My code (the script you are referring to) will not work on a linked server. It is intended to be used on the server in question, not through a link.