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

[] You need to register with SSC in this case, anyway here it is:

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 TotalSize=@TotalSize/@MB, ServerName = host_name(), FreespaceTimestamp = (GETDATE())
WHERE drive=@drive
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

=====

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.

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 where ID < @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 [], 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
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 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[].

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.