SQL Server Performance Forum – Threads Archive

T-SQL Disk Space

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

http://searchsecurity.techtarget.com/tip/0,289483,sid87_gci1252873,00.html 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.

Thanks – this is good but does not provide a way to get the ‘total drive space’ e.g. Drive capacity. Any ideas????????

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.

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….???

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.

I can’t access thye info – can you paste it in please???

[<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] You need to register with SSC in this case, anyway here it is:<pre id="code"> CREATE PROCEDURE sp_diskspace AS SET NOCOUNT ON DECLARE @hr int DECLARE @fso int DECLARE @drive char(1) DECLARE @odrive int DECLARE @TotalSize varchar(20) DECLARE @MB bigint ; SET @MB = 1048576 CREATE TABLE #drives (ServerName varchar(15), drive char(1) PRIMARY KEY, FreeSpace int NULL, TotalSize int NULL, FreespaceTimestamp DATETIME NULL) INSERT #drives(drive,FreeSpace) EXEC master.dbo.xp_fixeddrives EXEC @hr=sp_OACreate ‘Scripting.FileSystemObject’,@fso OUT IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso DECLARE dcur CURSOR LOCAL FAST_FORWARD FOR SELECT drive from #drives ORDER by drive OPEN dcur FETCH NEXT FROM dcur INTO @drive WHILE @@FETCH_STATUS=0 BEGIN EXEC @hr = sp_OAMethod @fso,’GetDrive’, @odrive OUT, @drive IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso EXEC @hr = sp_OAGetProperty @odrive,’TotalSize’, @TotalSize OUT IF @hr <> 0 EXEC sp_OAGetErrorInfo @odrive UPDATE #drives SET [email protected]/@MB, ServerName = host_name(), FreespaceTimestamp = (GETDATE()) WHERE [email protected] FETCH NEXT FROM dcur INTO @drive END CLOSE dcur DEALLOCATE dcur EXEC @hr=sp_OADestroy @fso IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso SELECT ServerName, drive, TotalSize as ‘Total(MB)’, FreeSpace as ‘Free(MB)’, CAST((FreeSpace/(TotalSize*1.0))*100.0 as int) as ‘Free(%)’, FreespaceTimestamp FROM #drives ORDER BY drive DROP TABLE #drives RETURN GO ===== </pre id="code"> Satya SKJ Microsoft SQL Server MVP Writer, Contributing Editor & Moderator http://www.SQL-Server-Performance.Com</a> <center>This posting is provided AS IS with no rights for the sake of 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.</center>

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.

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

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.

What does /S and /C mean in the line starting ‘select @sql’? Are these flags – if so where can I look these up?
Thanks

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. Satya SKJ Microsoft SQL Server MVP Writer, Contributing Editor & Moderator http://www.SQL-Server-Performance.Com</a> <center>This posting is provided AS IS with no rights for the sake of 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.</center>

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???

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=’

‘ />]. Satya SKJ Microsoft SQL Server MVP Writer, Contributing Editor & Moderator http://www.SQL-Server-Performance.Com</a> <center>This posting is provided AS IS with no rights for the sake of 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.</center>

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?

]]>