February 5, 2014 at 5:48 am
Hi
I'm looking for the most straight forward way of populating a table that will record OS Disk space used for each drive and the OS disk drives capacity.
Annoyingly, using the SP 'xp_fixeddrives' only returns used space and not the total space of the drive!
Are there any other DMV's or SP's that I could use to simply populate my table to record OS disk capacity? As stated, I just need the drive letter, total space used and total space allocated.
February 5, 2014 at 6:15 am
first execute this
exec sp_configure 'Ole Automation Procedures', 1; reconfigure with override;
then USED THIS CODE
declare @ipServer varchar(20) ='10.1.1.95'
if exists (select name FROM tempdb..sysobjects where name = '#disks')
drop table #disks
if exists (select name FROM tempdb..sysobjects where name = '#spaceS')
drop table #spaceS
declare @rez int,
@ObjectZaFS int,
@diskN int,
@cDrive varchar(13),
@g varchar(50),
@free varchar(50)
create table #disks (
flag CHAR(1) not null,
freeSpace varchar(10) not null
)
create table #spaceS
(
flag CHAR(1)
, gC bigint
, freeSpace bigint
)
insert intO #disks
exec master.dbo.xp_fixeddrives
declare goDisk CURSOR
FOR select flag FROM #disks
declare @which char(1)
open goDisk
fetch next from goDisk intO @which
while @@fetch_status = 0
begin
set @cDrive = 'GetDrive("' + @which + '")'
exec @rez = sp_OACreate 'Scripting.FileSystemObject', @ObjectZaFS output
if @rez = 0
exec @rez = sp_OAMethod @ObjectZaFS, @cDrive, @diskN output
if @rez = 0
exec @rez = sp_OAGetProperty @diskN,'TotalSize', @g output
if @rez = 0
exec @rez = sp_OAGetProperty @diskN,'FreeSpace', @free output
if @rez <> 0
exec sp_OADestroy @diskN
exec sp_OADestroy @ObjectZaFS
set @g = (CONVERT(BIGint,@g) / 1048576 )
set @free = (CONVERT(BIGint,@free) / 1048576 )
insert intO #spaceS
values (@which, @g, @free)
fetch next from goDisk intO @which
end
close goDisk
deallocate goDisk
select flag
, freeSpace as [FREE MB]
, (gC- freeSpace) as [USED MB]
, gC as
, cast( ((cast(freeSpace as decimal(18,2)) / cast(gC as decimal(18,2))) * 100) as decimal(18,2)) as [% FREE]
, @ipServer
FROM #spaceS
ORDER BY [flag] ASC
February 5, 2014 at 6:47 am
thanks for the reply.
I want to stay away from enabling options on the server, I don't know much about 'Ole Automation Procedures'; not sure of the security implications or as to why this option is disabled in the first place?
February 5, 2014 at 7:05 am
would you consider adding a CLR procedure that gets the data instead? that seems like a much better way to go.
Lowell
February 5, 2014 at 7:20 am
there also be security concern for using CLR. You enable access to the .NET framework.
In that case you provides too many things and mechanisms on the system than sql by default can do.
read carefully http://msdn.microsoft.com/en-us/library/ms131071%28v=sql.90%29.aspx
February 5, 2014 at 8:00 am
Yeh, I want to stay away from enabling anything on the server.
It's proving to be a lot more complex than I first thought, for the sake of one missing metric from xp_fixeddrives!
February 5, 2014 at 8:13 am
wak_no1 (2/5/2014)
Yeh, I want to stay away from enabling anything on the server.It's proving to be a lot more complex than I first thought, for the sake of one missing metric from xp_fixeddrives!
Then, it basically can't be done from SQL Server. Considering that most of the options that could do this are useable only by SAs, I think it a mistake to not enable them at least temporarily (remember that any attacker that gets in with SA privs will be able to enable them).
The only other method that I can think of is to do a search for a PowerShell script for this. There are many that will put the output into an SQL Server table.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 5, 2014 at 8:26 am
I second Jeff's suggestion that powershell is the way to go. this is the script that I have built my free space report off of.
February 5, 2014 at 10:18 am
I'm a Powershell newbie so will need to look into first creating the script to get the data I need, then connecting to SQL Server via Powershell to populate my table with the data retrieved from the OS about disk capacity.
February 6, 2014 at 4:10 am
Right I've been able to connect to my instance of sql server via Powershell and run a simple select. I've also figured out the command to retrieve the information I need in regards to disk space, via Powershell.
I'm now attempting to tie the two together; so insert what I receive from my Powershell query in regards to disk space and insert into my sql server table. This will be then need to be part of a automated job which will run weekly.
Any help would be much appreciated! 🙂
February 6, 2014 at 7:49 am
February 6, 2014 at 8:44 am
Thank you.
Code to retrieve disk information needed via Powershell
Get-WMIObject Win32_LogicalDisk -filter "DriveType=3" -computer (Get-Content c:\computers.txt) | Select SystemName,DeviceID,VolumeName,@{Name="size(GB)"; Expression={"{0:N1}" -f($_.size/1gb)}},@{Name="freespace(GB)";Expression={"{0:N1}" -f($_.freespace/1gb)}}
Of the back of this, I've created a table on a test db within sql server, where I want the information from the above command to get written to:
CREATE TABLE [dbo].[DiskUsage](
[SystemName] [varchar](50) NOT NULL,
[DeviceID] [varchar](50) NOT NULL,
[VolumeName] [varchar](50) NULL,
[SizeGB] [numeric](18, 2) NOT NULL,
[FreeGB] [numeric](18, 2) NOT NULL )
Here's my connection string from Powershell (at the minitue, it's just contains a simple SELECT, which i want to replace with an INSERT of the data retrieved from within Powershell):
#set the security - set-executionpolicy unrestricted
$SQLServer = ".\SQLEXPRESS"
$SQLDBName = "AdventureWorks2008R2"
$SqlQuery = "select * from Person.Address where AddressID = 1"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
clear
$DataSet.Tables[0]
February 6, 2014 at 9:15 am
try this out
$ds = New-Object system.Data.DataSet
$ds = Get-WMIObject Win32_LogicalDisk -filter "DriveType=3" -computer (Get-Content c:\computers.txt) | Select SystemName,DeviceID,VolumeName,@{Name="size"; Expression={"{0:N1}" -f($_.size/1gb)}},@{Name="freespace";Expression={"{0:N1}" -f($_.freespace/1gb)}}
foreach ($d in $ds)
{
$systemname = $d.systemname
$deviceID = $d.DeviceID
$volumename = $d.volumename
$size = $d.size
$freespace = $d.freespace
$SQLServer = ".\SQLEXPRESS"
$SQLDBName = "AdventureWorks2008R2"
$SqlQuery = "insert into diskusage values('$systemname','$deviceID','$volumename','$size','$freespace')"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
}
February 6, 2014 at 10:20 am
Bob, thank you, this is working as I'd hoped. I can sort of see what I needed to do.
One thing has just occurred to me though, I need a date stamp to record when the data was recorded; is there another WMI object that I could query at the same time to get this info?
February 6, 2014 at 10:44 am
I usually do something like the following to get the date.
$date = Get-Date -Format "yyyyMMdd"
here is article about powershell date formatting
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply