SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Script to find Disk Space including Mount points


Script to find Disk Space including Mount points

Author
Message
genehunter29009
genehunter29009
SSC Journeyman
SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)

Group: General Forum Members
Points: 91 Visits: 212
Comments posted to this topic are about the item Script to find Disk Space including Mount points
kpsqlcent
kpsqlcent
SSC-Enthusiastic
SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)

Group: General Forum Members
Points: 109 Visits: 532
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.



bobswi
bobswi
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 335
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
genehunter29009
genehunter29009
SSC Journeyman
SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)

Group: General Forum Members
Points: 91 Visits: 212
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.
bobswi
bobswi
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 335
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 = ""
}


Iwas Bornready
Iwas Bornready
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16044 Visits: 885
Thanks for the script.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search