SQL Server Performance

Harddrives space on your server

Discussion in 'Contribute Your SQL Server Scripts' started by Anonymous, Aug 1, 2006.

  1. Anonymous New Member

    ** 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
  2. Luis Martin Moderator

    That is good!.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided “AS IS” with no warranties for accuracy.



  3. Anonymous New Member

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

    Regards
    Sidh

    next2none is what others feel about the maverick
  4. satya Moderator

    I would refrain creating such USP on a system database and besides it is an undocumented SP and may be removed in next releases.
    Don't you get same information using XP_FIXEDDRIVES, Only additional bit on the script is to send the results as a mail attachment.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided �AS IS� with no rights for the sake of knowledge sharing.
  5. Haywood New Member

    <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=':D' />]<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">
  6. FrankKalis Moderator

    Personally I would appreciate if the code tags will be used when posting code. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />It really makes it more readable, methinks.<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a>
  7. satya Moderator

    I hope it is now.[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />Gordon's script is more relevant to stand as the topic.<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>
  8. Haywood New Member

    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=':)' />
  9. satya Moderator

    Tip: WHen you're replying a post you will see HTML tags, that will help you here.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided �AS IS� with no rights for the sake of knowledge sharing.
  10. FrankKalis Moderator

    Well, not if you use the "Quick Reply" feature as I do. In that case you need to type them manually [ code ] and [/ code ] without the blanks, of course. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  11. varriam New Member

    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
  12. Haywood New Member

    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.
  13. satya Moderator

    Varriam,
    I hope you have got the reply from the script originator here, if you can be more specific about your requirement with a seperate post in relevant forums we can help you out.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.

Share This Page