Script to find Disk Space including Mount points

Question

Script to find Disk Space including Mount points

genehunter29009

SSC Eights!

Points: 877
More actions
May 8, 2012 at 1:03 pm

#256095

Comments posted to this topic are about the item Script to find Disk Space including Mount points

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

kpsqlcent Old Hand Points: 385 More actions · Answer 1

Useful script - thank you. It worked on most of my SQL2000/5/8/8R2s. However no data was returned on some of our SQL2005 clusters, I *guess* because those SQLs lacked permission to run FSUTIL.

SQLSlack3r SSC-Addicted Points: 428 More actions · Answer 2

Hrmm xp_cmdshell, I've seen others using the sp_OA Ole Automation proc's too, both imo potential security risks.

Why should I have to enable either of those on what could be 100's of servers, just to monitor them?

I would think it would be much simpler/scalable/portable to have 1 DB, on my DBA-specific-repository SQL server, that I just run a PowerShell script that reads ServerName's from 1 table, then iterates through grabbing DB/Disk FreeSpace/Capacity, whatever I need, etc. and then writes that data back into my 1 DB where I can then report or alert on the results/trends.

still learning PowerShell though, but I think I'm going to carve some pieces out of this:

http://www.g33kdaddy.com/post/11670337032/using-powershell-to-monitor-servers-part-1

MCSA SQL 2014

genehunter29009 SSC Eights! Points: 877 More actions · Answer 3

I have another script for gathering information on all servers. Basically I run both against all servers and i have a unique key so the dups fail to enter the table. for some reason some servers need to fall back to the other method. but to each his own way.

SQLSlack3r SSC-Addicted Points: 428 More actions · Answer 4

Ahh got it, throw all this into a .ps1 file, then run it like:

powershell -FILE C:\yourScript.ps1

#Server/DB for your repository

$Server = "yourServerName"

$Database = "yourDBName"

## SQL connection function

# removes the need to install the SQL extensions for PowerShell

function Invoke-Sqlcmd2

{

param([string]$ServerInstance,

[string]$Database,

[string]$Query,

[Int32]$QueryTimeout=30

)

$conn=new-object System.Data.SqlClient.SQLConnection

$conn.ConnectionString="Server={0};Database={1};Integrated Security=True" -f $ServerInstance,$Database

$conn.Open()

$cmd=new-object system.Data.SqlClient.SqlCommand($Query,$conn)

$cmd.CommandTimeout=$QueryTimeout

$ds=New-Object system.Data.DataSet

$da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)

[void]$da.fill($ds)

$conn.Close()

$ds.Tables[0]

}

## Begin Code

$con = "Server=$Server;Database=$Database;Integrated Security=True"

$cmd = "SELECT DISTINCT ServerName FROM yourListofServers WHERE IsActive=1"

$da = new-object System.Data.SqlClient.SqlDataAdapter ($cmd, $con)

$dt = new-object System.Data.DataTable

$da.fill($dt) | out-null

foreach ($srv in $dt)

{

$ServerName = $srv.ServerName

$Reply = Test-Connection -ComputerName $ServerName -Count 1 -Quiet

# if ping returns then continue

if ($Reply –eq “True”)

{

#Write-Host $ServerName

$disks = Get-WMIObject -ComputerName $ServerName Win32_LogicalDisk -Filter "DriveType=3"

foreach ($disk in $disks)

{

[float]$freespace = $disk.FreeSpace;

[float]$capacity = $disk.Size;

$freeSpaceGB = [Math]::Round($freespace / 1073741824, 1);

$totalsizeGB = [Math]::Round($capacity / 1073741824, 1);

$driveletter = $disk.DeviceID

#Write-Host $disk.DeviceID", "$freeSpaceGB", "$totalsizeGB

$sql = "Exec spYourRefreshProc '$ServerName', '$driveletter', $freeSpaceGB, $totalsizeGB"

#Write-Host $sql

Invoke-Sqlcmd2 -serverinstance $Server -database $Database -query $sql

}

$Reply = ""

}

MCSA SQL 2014

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 5

akljfhnlaflkj

SSC Guru

Points: 76202

May 10, 2016 at 1:46 pm

#1877946

Thanks for the script.