Getting Fixed Drives Total Space and Free Space

Hello Hi
I have a procedure I use sp_diskspace which uses OLE Automation to get a
list of total disk space and free space on all fixed drives on a server.
This works fine on our SQL 7 and SQL 2000 servers but on our SQL 2005
servers, we have OLE Automation disabled as a security standard, hence this
script doesn't work on SQL 2005 servers.
I am wondering, is there another way to get this date? I am aware of
xp_fixeddrives, but this displays only the free space on drives, not the
total space.
Below is the script using OLE Automation that I use for SQL 7 and 2000.
Any help is appreciated.
Thanks.
Cheers.
Kunal.
----
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
create PROCEDURE sp_diskspace
/*** Borrowed from SQL Stripes Disk Size Stored Procedure ***/
AS
SET NOCOUNT ON
DECLARE @.hr int
DECLARE @.fso int
DECLARE @.drive char(1)
DECLARE @.odrive int
DECLARE @.TotalSize varchar(20) DECLARE @.MB Numeric ; SET @.MB = 1048576
CREATE TABLE #drives (drive char(1) PRIMARY KEY, FreeSpace int NULL,
TotalSize int 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 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
drive, TotalSize as 'Total(MB)',FreeSpace as 'Free(MB)',
CAST((FreeSpace/(TotalSize*1.0))*100.0 as int) as 'Free(%)' FROM #drives
ORDER BY drive DROP TABLE #drives Return
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
sp_diskspace
go
drop proc diskspace
go
----> I am wondering, is there another way to get this date?
You might consider gathering this info directly using an ActiveX script in a
scheduled job rather than Transact-SQL. Another option is a CLR proc or
function.
Hope this helps.
Dan Guzman
SQL Server MVP
"kunalap" <kunalap@.discussions.microsoft.com> wrote in message
news:CE948DD9-585E-458E-B32E-A58EA8E724CA@.microsoft.com...
> Hello Hi
> I have a procedure I use sp_diskspace which uses OLE Automation to get a
> list of total disk space and free space on all fixed drives on a server.
> This works fine on our SQL 7 and SQL 2000 servers but on our SQL 2005
> servers, we have OLE Automation disabled as a security standard, hence
> this
> script doesn't work on SQL 2005 servers.
> I am wondering, is there another way to get this date? I am aware of
> xp_fixeddrives, but this displays only the free space on drives, not the
> total space.
> Below is the script using OLE Automation that I use for SQL 7 and 2000.
> Any help is appreciated.
> Thanks.
> Cheers.
> Kunal.
> ----
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS OFF
> GO
> create PROCEDURE sp_diskspace
> /*** Borrowed from SQL Stripes Disk Size Stored Procedure ***/
> AS
> SET NOCOUNT ON
> DECLARE @.hr int
> DECLARE @.fso int
> DECLARE @.drive char(1)
> DECLARE @.odrive int
> DECLARE @.TotalSize varchar(20) DECLARE @.MB Numeric ; SET @.MB = 1048576
> CREATE TABLE #drives (drive char(1) PRIMARY KEY, FreeSpace int NULL,
> TotalSize int 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 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
> drive, TotalSize as 'Total(MB)',FreeSpace as 'Free(MB)',
> CAST((FreeSpace/(TotalSize*1.0))*100.0 as int) as 'Free(%)' FROM #drives
> ORDER BY drive DROP TABLE #drives Return
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
> sp_diskspace
> go
> drop proc diskspace
> go
> ----
>|||Also you could use an SSIS package to capture this via WMI...
"Dan Guzman" wrote:

> You might consider gathering this info directly using an ActiveX script in
a
> scheduled job rather than Transact-SQL. Another option is a CLR proc or
> function.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "kunalap" <kunalap@.discussions.microsoft.com> wrote in message
> news:CE948DD9-585E-458E-B32E-A58EA8E724CA@.microsoft.com...
>|||Dan,
I don't know ActiveX. Is there a script around for the same?
Ben,
I tried the SSIS package using WMI queries. Works well. Problem is...when
connecting to some servers for WMI query in the package I get the error:
"Failed to connect to the specified server with the following error: "Access
is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))". The serve
r
name may be invalid."
Some of these server are complete identical. I have even checked the WMI
security on these servers.
Additionally, is there a way for me to have a text file with server name,
and then run the WMI Data Reader task for each server mentioned there.
Because currently, I am manually creating individual WMI Data Reader tasks
for each server. For 100-150 server, it can get quiet tedious. Additionally,
if one of the servers is not connectable the whole package would fail.
Thanks a lots. You guys have been really helpful. I would really appreciate
it if you could help me sort out these WMI problems for me.
Thanks.
Kunal.|||> I don't know ActiveX. Is there a script around for the same?
Here's a simple VBScript example that runs a WMI query and displays the
results.
Option Explicit
Dim SQL, Results, ServerName
Dim oWin32_LogicalDisks, oWin32_LogicalDisk
'list local disks on remote server
SQL = "SELECT Caption, FreeSpace, Size " & _
" FROM Win32_LogicalDisk WHERE DriveType = 3"
Set oWin32_LogicalDisks = _
GetObject("winmgmts:{impersonationLevel=impersonate}!//" & _
"MyServer" & _
"/root/cimv2").ExecQuery(SQL, , 48)
For Each oWin32_LogicalDisk In oWin32_LogicalDisks
Results = Results & _
oWin32_LogicalDisk.Caption & "," & _
oWin32_LogicalDisk.FreeSpace & "," & _
oWin32_LogicalDisk.Size &_
VbCrLf
Next
MsgBox(Results)

> Additionally, is there a way for me to have a text file with server name,
> and then run the WMI Data Reader task for each server mentioned there.
One method is to create an XML file with your server list. For example:
<ServerList>
<Server>SERVER1</Server>
<Server>SERVER2</Server>
<Server>SERVER3</Server>
</ServerList>
Create a file connection for the server list xml file.
Create a Foreach Loop Container with the following properties:
Collection:
Enumerator: Foreach NodeList Enumerator
DocumentSourceType: FileConnection
DocumentSourceSource: server list file connection
Enumeration Type: NodeText
OuterXPathStringSourceType: Direct Input
OuterXPathString: ServerList/Server/text()
Variable Mappings:
create a user variable for the server name
Place your WMI Data Reader inside the Foreach Loop Container.
In the WMI Connection properties, specify an expression to map the
ConnectionString to the variable specified in the Foreach Loop varaible
mapping.
Hope this helps.
Dan Guzman
SQL Server MVP
"kunalap" <kunalap@.discussions.microsoft.com> wrote in message
news:3F55FB9D-2B19-4D18-9E03-7A0800DB2DE0@.microsoft.com...
> Dan,
> I don't know ActiveX. Is there a script around for the same?
> Ben,
> I tried the SSIS package using WMI queries. Works well. Problem is...when
> connecting to some servers for WMI query in the package I get the error:
> "Failed to connect to the specified server with the following error:
> "Access
> is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))". The
> server
> name may be invalid."
> Some of these server are complete identical. I have even checked the WMI
> security on these servers.
> Additionally, is there a way for me to have a text file with server name,
> and then run the WMI Data Reader task for each server mentioned there.
> Because currently, I am manually creating individual WMI Data Reader tasks
> for each server. For 100-150 server, it can get quiet tedious.
> Additionally,
> if one of the servers is not connectable the whole package would fail.
> Thanks a lots. You guys have been really helpful. I would really
> appreciate
> it if you could help me sort out these WMI problems for me.
> Thanks.
> Kunal.|||Wow. That is some cool stuff.
I'm not sure I'll be able to implement all that but I'm going to try my
best. Probably will need lots and lots of debugging and help.
Thanks a lot.
Kunal.
"Dan Guzman" wrote:

> Here's a simple VBScript example that runs a WMI query and displays the
> results.
> Option Explicit
> Dim SQL, Results, ServerName
> Dim oWin32_LogicalDisks, oWin32_LogicalDisk
> 'list local disks on remote server
> SQL = "SELECT Caption, FreeSpace, Size " & _
> " FROM Win32_LogicalDisk WHERE DriveType = 3"
> Set oWin32_LogicalDisks = _
> GetObject("winmgmts:{impersonationLevel=impersonate}!//" & _
> "MyServer" & _
> "/root/cimv2").ExecQuery(SQL, , 48)
> For Each oWin32_LogicalDisk In oWin32_LogicalDisks
> Results = Results & _
> oWin32_LogicalDisk.Caption & "," & _
> oWin32_LogicalDisk.FreeSpace & "," & _
> oWin32_LogicalDisk.Size &_
> VbCrLf
> Next
> MsgBox(Results)
>
> One method is to create an XML file with your server list. For example:
> <ServerList>
> <Server>SERVER1</Server>
> <Server>SERVER2</Server>
> <Server>SERVER3</Server>
> </ServerList>
> Create a file connection for the server list xml file.
> Create a Foreach Loop Container with the following properties:
> Collection:
> Enumerator: Foreach NodeList Enumerator
> DocumentSourceType: FileConnection
> DocumentSourceSource: server list file connection
> Enumeration Type: NodeText
> OuterXPathStringSourceType: Direct Input
> OuterXPathString: ServerList/Server/text()
> Variable Mappings:
> create a user variable for the server name
> Place your WMI Data Reader inside the Foreach Loop Container.
> In the WMI Connection properties, specify an expression to map the
> ConnectionString to the variable specified in the Foreach Loop varaible
> mapping.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "kunalap" <kunalap@.discussions.microsoft.com> wrote in message
> news:3F55FB9D-2B19-4D18-9E03-7A0800DB2DE0@.microsoft.com...
>|||hi dan,
thanks again for ur help. but i'm trying to setup the xml parsing for wmi
connection string to pickup server names from there.
i've set up everything as u mentioned below, but stuck at the following:
> In the WMI Connection properties, specify an expression to map the
> ConnectionString to the variable specified in the Foreach Loop varaible
> mapping.
In the WMI connection proerties i specified
Servername=\\@.[User. servername1];Namespace=\root\cimv2;UseNt
Auth=True;Us
erName=;
But doesn't work.
Can you please help me out on how to pass the value read from xml file into
the variable and then from variable to connectstring.
i don't recall setting xml to read into the variable either.
thanks.
kunal.
"Dan Guzman" wrote:

> Here's a simple VBScript example that runs a WMI query and displays the
> results.
> Option Explicit
> Dim SQL, Results, ServerName
> Dim oWin32_LogicalDisks, oWin32_LogicalDisk
> 'list local disks on remote server
> SQL = "SELECT Caption, FreeSpace, Size " & _
> " FROM Win32_LogicalDisk WHERE DriveType = 3"
> Set oWin32_LogicalDisks = _
> GetObject("winmgmts:{impersonationLevel=impersonate}!//" & _
> "MyServer" & _
> "/root/cimv2").ExecQuery(SQL, , 48)
> For Each oWin32_LogicalDisk In oWin32_LogicalDisks
> Results = Results & _
> oWin32_LogicalDisk.Caption & "," & _
> oWin32_LogicalDisk.FreeSpace & "," & _
> oWin32_LogicalDisk.Size &_
> VbCrLf
> Next
> MsgBox(Results)
>
> One method is to create an XML file with your server list. For example:
> <ServerList>
> <Server>SERVER1</Server>
> <Server>SERVER2</Server>
> <Server>SERVER3</Server>
> </ServerList>
> Create a file connection for the server list xml file.
> Create a Foreach Loop Container with the following properties:
> Collection:
> Enumerator: Foreach NodeList Enumerator
> DocumentSourceType: FileConnection
> DocumentSourceSource: server list file connection
> Enumeration Type: NodeText
> OuterXPathStringSourceType: Direct Input
> OuterXPathString: ServerList/Server/text()
> Variable Mappings:
> create a user variable for the server name
> Place your WMI Data Reader inside the Foreach Loop Container.
> In the WMI Connection properties, specify an expression to map the
> ConnectionString to the variable specified in the Foreach Loop varaible
> mapping.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "kunalap" <kunalap@.discussions.microsoft.com> wrote in message
> news:3F55FB9D-2B19-4D18-9E03-7A0800DB2DE0@.microsoft.com...
>|||OK I got the expression thing worked out...and at run time it does create th
e
following connectstring
Servername=\\FinServer432;Namespace=\roo
t\cimv2;UseNtAuth=True;UserName=;
But it errors out saying:
[WMI Data Reader Task] Error: An error occurred with the following error
message
: "The connection
" Servername=\\FunServer432;Namespace=\roo
t\cimv2;UseNtAuth=True;UserName=;"
is not found. This error is thrown by Connections collection when the
specific connection element is not found. ".
I'm guessing it is looking for a connection by that name. Do I have to
create all 150 server connections first? I thought the whole point was to
avoid that.
cheers.
Kunal.
"Dan Guzman" wrote:

> Here's a simple VBScript example that runs a WMI query and displays the
> results.
> Option Explicit
> Dim SQL, Results, ServerName
> Dim oWin32_LogicalDisks, oWin32_LogicalDisk
> 'list local disks on remote server
> SQL = "SELECT Caption, FreeSpace, Size " & _
> " FROM Win32_LogicalDisk WHERE DriveType = 3"
> Set oWin32_LogicalDisks = _
> GetObject("winmgmts:{impersonationLevel=impersonate}!//" & _
> "MyServer" & _
> "/root/cimv2").ExecQuery(SQL, , 48)
> For Each oWin32_LogicalDisk In oWin32_LogicalDisks
> Results = Results & _
> oWin32_LogicalDisk.Caption & "," & _
> oWin32_LogicalDisk.FreeSpace & "," & _
> oWin32_LogicalDisk.Size &_
> VbCrLf
> Next
> MsgBox(Results)
>
> One method is to create an XML file with your server list. For example:
> <ServerList>
> <Server>SERVER1</Server>
> <Server>SERVER2</Server>
> <Server>SERVER3</Server>
> </ServerList>
> Create a file connection for the server list xml file.
> Create a Foreach Loop Container with the following properties:
> Collection:
> Enumerator: Foreach NodeList Enumerator
> DocumentSourceType: FileConnection
> DocumentSourceSource: server list file connection
> Enumeration Type: NodeText
> OuterXPathStringSourceType: Direct Input
> OuterXPathString: ServerList/Server/text()
> Variable Mappings:
> create a user variable for the server name
> Place your WMI Data Reader inside the Foreach Loop Container.
> In the WMI Connection properties, specify an expression to map the
> ConnectionString to the variable specified in the Foreach Loop varaible
> mapping.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "kunalap" <kunalap@.discussions.microsoft.com> wrote in message
> news:3F55FB9D-2B19-4D18-9E03-7A0800DB2DE0@.microsoft.com...
>|||sorry for the msg bombing. i figured out everything. im a little slow i
apologize.
when creating a wmi connection, it does ask for values...so i enter some
dummy values and then create an expression to reflect the variable @.sn1 that
im using.
the expression field reads,
ConnectionString -
"Servername=\\\\"+@.sn1+" ;Namespace=\\root\\cimv2;UseNtAuth=True;
UserName=;"
in the ConnectionString filed itself, there is the same value. same applied
for servername which reads \\@.sn1
but when i execute the foreachloop container, i keep getting the message
'rpc server not available'. i'm guessing its because it keeps on using "@.sn1
"
as the servername. if i substitute @.sn1 with a server name, it runs 10
iteration on the same server (xml has 10 values).
what am i missing here?
cheers.
kunal.
"Dan Guzman" wrote:

> Here's a simple VBScript example that runs a WMI query and displays the
> results.
> Option Explicit
> Dim SQL, Results, ServerName
> Dim oWin32_LogicalDisks, oWin32_LogicalDisk
> 'list local disks on remote server
> SQL = "SELECT Caption, FreeSpace, Size " & _
> " FROM Win32_LogicalDisk WHERE DriveType = 3"
> Set oWin32_LogicalDisks = _
> GetObject("winmgmts:{impersonationLevel=impersonate}!//" & _
> "MyServer" & _
> "/root/cimv2").ExecQuery(SQL, , 48)
> For Each oWin32_LogicalDisk In oWin32_LogicalDisks
> Results = Results & _
> oWin32_LogicalDisk.Caption & "," & _
> oWin32_LogicalDisk.FreeSpace & "," & _
> oWin32_LogicalDisk.Size &_
> VbCrLf
> Next
> MsgBox(Results)
>
> One method is to create an XML file with your server list. For example:
> <ServerList>
> <Server>SERVER1</Server>
> <Server>SERVER2</Server>
> <Server>SERVER3</Server>
> </ServerList>
> Create a file connection for the server list xml file.
> Create a Foreach Loop Container with the following properties:
> Collection:
> Enumerator: Foreach NodeList Enumerator
> DocumentSourceType: FileConnection
> DocumentSourceSource: server list file connection
> Enumeration Type: NodeText
> OuterXPathStringSourceType: Direct Input
> OuterXPathString: ServerList/Server/text()
> Variable Mappings:
> create a user variable for the server name
> Place your WMI Data Reader inside the Foreach Loop Container.
> In the WMI Connection properties, specify an expression to map the
> ConnectionString to the variable specified in the Foreach Loop varaible
> mapping.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "kunalap" <kunalap@.discussions.microsoft.com> wrote in message
> news:3F55FB9D-2B19-4D18-9E03-7A0800DB2DE0@.microsoft.com...
>|||> but when i execute the foreachloop container, i keep getting the message
> 'rpc server not available'. i'm guessing its because it keeps on using
> "@.sn1"
> as the servername. if i substitute @.sn1 with a server name, it runs 10
> iteration on the same server (xml has 10 values).
I was able to reproduce your problem but I haven't yet identified the cause.
As a workaround, try moving the WMI Data Reader to a separate package and
then executing from the ForEach loop with an Execute Package task. You'll
need to pass the server name from parent to child package using a child
package configuration. See the Books Online for details.
Hope this helps.
Dan Guzman
SQL Server MVP
"kunalap" <kunalap@.discussions.microsoft.com> wrote in message
news:3A4C8197-C7BB-4F5B-8E0C-C41E7936006D@.microsoft.com...[vbcol=seagreen]
> sorry for the msg bombing. i figured out everything. im a little slow i
> apologize.
> when creating a wmi connection, it does ask for values...so i enter some
> dummy values and then create an expression to reflect the variable @.sn1
> that
> im using.
> the expression field reads,
> ConnectionString -
> "Servername=\\\\"+@.sn1+" ;Namespace=\\root\\cimv2;UseNtAuth=True;
UserName=;
"
> in the ConnectionString filed itself, there is the same value. same
> applied
> for servername which reads \\@.sn1
> but when i execute the foreachloop container, i keep getting the message
> 'rpc server not available'. i'm guessing its because it keeps on using
> "@.sn1"
> as the servername. if i substitute @.sn1 with a server name, it runs 10
> iteration on the same server (xml has 10 values).
> what am i missing here?
> cheers.
> kunal.
> "Dan Guzman" wrote:
>