SQL Server Performance

T-SQL Disk Space

Discussion in 'SQL Server 2005 General DBA Questions' started by trev24, May 16, 2007.

  1. trev24 New Member

    Is there an easy way to run T-SQL scripts to get:
    1) Total drive space.
    2) Free drive space.
    3) Then calculate projected expected capacity lifetime e.g. time left for expected space usage.

    Thanks
    trev24, May 16, 2007
    #2

  2. satya Moderator

    satya, May 16, 2007
    #2

  3. trev24 New Member

    Thanks - this is good but does not provide a way to get the 'total drive space' e.g. Drive capacity.

    Any ideas????????
    trev24, May 16, 2007
    #2

  4. satya Moderator

    You could either use XP_FIXEDDRIVERS (undocumented SP) or use PERFMON to capture LogicalDisk:% Free Space & LogicalDisk:Free Megabytes counters. Lookat PERFMON for more physical disk counters in thsi case.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
    satya, May 16, 2007
    #2

  5. trev24 New Member

    Thanks - xp_fixeddrives only gives free space.

    Perfmon does use T-SQL - or does it???

    I need a T-SQL solution to find total drive capacity - if possible....???
    trev24, May 16, 2007
    #2

  6. satya Moderator

    http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=129133

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
    satya, May 16, 2007
    #2

  7. trev24 New Member

    I can't access thye info - can you paste it in please???
    trev24, May 16, 2007
    #2

  8. satya Moderator

    [<img src='/community/emoticons/emotion-1.gif' alt=':)' />] You need to register with SSC in this case, anyway here it is:<pre id="code"><font face="courier" size="2" id="code"><br /><br /><br />CREATE PROCEDURE sp_diskspace<br />AS<br />SET NOCOUNT ON<br />DECLARE @hr int<br />DECLARE @fso int<br />DECLARE @drive char(1)<br />DECLARE @odrive int<br />DECLARE @TotalSize varchar(20)<br />DECLARE @MB bigint ; SET @MB = 1048576<br />CREATE TABLE #drives (ServerName varchar(15),<br />drive char(1) PRIMARY KEY,<br />FreeSpace int NULL,<br />TotalSize int NULL,<br />FreespaceTimestamp DATETIME NULL)<br />INSERT #drives(drive,FreeSpace)<br />EXEC master.dbo.xp_fixeddrives<br />EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT<br />IF @hr &lt;&gt; 0 EXEC sp_OAGetErrorInfo @fso<br />DECLARE dcur CURSOR LOCAL FAST_FORWARD<br />FOR SELECT drive from #drives<br />ORDER by drive<br />OPEN dcur<br />FETCH NEXT FROM dcur INTO @drive<br />WHILE @@FETCH_STATUS=0<br />BEGIN<br />EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive<br />IF @hr &lt;&gt; 0 EXEC sp_OAGetErrorInfo @fso<br />EXEC @hr = sp_OAGetProperty @odrive,'TotalSize', @TotalSize OUT<br />IF @hr &lt;&gt; 0 EXEC sp_OAGetErrorInfo @odrive<br />UPDATE #drives<br />SET TotalSize=@TotalSize/@MB, ServerName = host_name(), FreespaceTimestamp = (GETDATE())<br />WHERE drive=@drive<br />FETCH NEXT FROM dcur INTO @drive<br />END<br />CLOSE dcur<br />DEALLOCATE dcur<br />EXEC @hr=sp_OADestroy @fso<br />IF @hr &lt;&gt; 0 EXEC sp_OAGetErrorInfo @fso<br />SELECT ServerName,<br />drive,<br />TotalSize as 'Total(MB)',<br />FreeSpace as 'Free(MB)',<br />CAST((FreeSpace/(TotalSize*1.0))*100.0 as int) as 'Free(%)',<br />FreespaceTimestamp<br />FROM #drives<br />ORDER BY drive<br />DROP TABLE #drives<br />RETURN<br />GO<br /><br />=====<br /><br /></font id="code"></pre id="code"><br /><br /><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Writer, Contributing Editor & 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. <hr noshade size="1">Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.</i></font id="size1"></font id="teal"></center>
    satya, May 16, 2007
    #2

  9. MohammedU New Member

    Here is another method without using SP_OAxxx procedures....

    Declare @BytesUsed Varchar(1000),
    @BytesFree Varchar(1000),
    @TotalBytes BIGINT,
    @IDENTITY INT,
    @Drive Char(1),
    @sql Varchar(1000)

    SET NOCOUNT ON
    Create table ##DiskSpace ( Drive Char(1), TotalSpace Bigint, FreeSpace Bigint,
    PercentageFree as (FreeSpace*100 / TotalSpace ) )
    Create table #Fixeddrives ( Drive Char(1), FreeSpace Bigint)
    create table ##Dir ( ID INT IDENTITY , DriveSize Varchar(2000))

    Insert into #Fixeddrives exec master.dbo.xp_fixeddrives
    --select * from #Fixeddrives
    insert into ##DiskSpace ( Drive , FreeSpace)
    select Drive , FreeSpace from #Fixeddrives
    -- select * from ##DiskSpace

    DECLARE Drive_cursor CURSOR FOR
    SELECT Drive from ##DiskSpace
    OPEN Drive_cursor
    FETCH NEXT FROM Drive_cursor INTO @Drive

    WHILE @@FETCH_STATUS = 0
    BEGIN

    select @sql = 'insert into ##Dir exec master.dbo.xp_cmdshell ''dir '+ @Drive+': /S /-C'''
    exec(@sql)
    SELECT @IDENTITY = @@IDENTITY
    delete from ##Dir whereID < @IDENTITY - 4

    select @BytesUsed = substring (drivesize, charIndex ('File(s)', drivesize, 0)+ 9 , 1000)
    from ##Dir where drivesize like '%File(s)%'

    while patindex('%[^0-9]%', @BytesUsed) > 0
    begin
    set @BytesUsed = stuff( @BytesUsed, patindex('%[^0-9]%', @BytesUsed), 1, '' )
    end

    select @BytesFree = substring (drivesize, charIndex ('Dir(s)', drivesize, 0)+ 9 , 1000)
    from ##Dir where drivesize like '%Dir(s)%'

    while patindex('%[^0-9]%', @BytesFree) > 0
    begin
    set @BytesFree = stuff( @BytesFree, patindex('%[^0-9]%', @BytesFree), 1, '' )
    end

    select @TotalBytes = Convert(bigint, @BytesUsed)+ Convert(bigint, @BytesFree)
    select @TotalBytes = (@TotalBytes/ 1024)/1024 -- Coverting to MB....
    -- select @TotalBytes
    Update ##DiskSpace set TotalSpace = @TotalBytes
    WHERE Drive = @Drive

    TRUNCATE TABLE ##Dir
    FETCH NEXT FROM Drive_cursor INTO @Drive

    END
    CLOSE Drive_cursor
    DEALLOCATE Drive_cursor


    select * from ##DiskSpace

    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
    MohammedU, May 16, 2007
    #2

  10. trev24 New Member

    Thanks so far!

    What is the specific code that displays the drive total capacity?

    What are sp_OAxxx procedures - I've looked on MSDN and the documentation is unclear.

    Thanks
    trev24, May 17, 2007
    #2

  11. satya Moderator

    Even the query posted by me uses same SP_OA procedures.
    One of the technet article refers
    quote:
    The sp_OA procedures (OA stands for OLE Automation) like sp_OACreate, sp_OAMethod, and so on, allow a connection, through Transact-SQL commands, to create and use Component Object Model (COM) based objects. The procedures are built into SQL Server as an extended stored procedure (XPROC), contained in Sqlole32.dll. This is another powerful example of how SQL Server behavior can be extended with an XPROC implementation. For more information, see the following article in the Microsoft Knowledge Base:

    152801 http://support.microsoft.com/kb/152801/EN-US/) : Examples of Sp_OA Procedure Use and SQLOLE.Transfer Object
    Most of them are undocumented and you will only get to know after using them.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
    satya, May 17, 2007
    #2

  12. trev24 New Member

    What does /S and /C mean in the line starting 'select @sql'?

    Are these flags - if so where can I look these up?


    Thanks
    trev24, May 17, 2007
    #2

  13. satya Moderator

    They are for DIR command in dos [<img src='/community/emoticons/emotion-1.gif' alt=':)' />], if you carefully look/count at the apostrophe you will get it.<br /><br /><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Writer, Contributing Editor & 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. <hr noshade size="1">Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.</i></font id="size1"></font id="teal"></center>
    satya, May 17, 2007
    #2

  14. trev24 New Member

    Thanks guys - I've got it!

    Just implemented it! Normally the drive space doesn't change that ofetn anyway!!!

    Thanks again - byt the way - where did you learn your skills and can I help you some days???
    trev24, May 17, 2007
    #2

  15. satya Moderator

    Its all from the experience and willingness to help others, that way you learn new things by looking at the problem they face. Whatever I see as a new to me in technology I save it to my resource library and use them when I need to. SSP is a common platform for like minded people[<img src='/community/emoticons/emotion-1.gif' alt=':)' />].<br /><br /><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Writer, Contributing Editor & 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. <hr noshade size="1">Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.</i></font id="size1"></font id="teal"></center>
    satya, May 17, 2007
    #2

  16. jasonwisdom New Member

    Hi there.
    I realize this is a while after the original thread was started.
    I am running into the same issues.
    I'd like to get free drive SPACE.
    But I'm getting security violations for SP_OU* and also for XP_CMDSHELL.
    There is no way I am turning on XP_CMDSHELL.
    But I want to find out free drive SPACE.
    How can I do so, without opening up security risks from SP_OU* or XP_CMDSHELL?
    jasonwisdom, Aug 25, 2008
    #3

Share This Page