Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 disk space alert

Author  Topic 

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2012-06-04 : 13:56:00
I would like to setup a low disk space alert when space more than 70.
Please suggest..

Sachin.Nand

2937 Posts

Posted - 2012-06-04 : 14:16:55
Use the xp_fixeddrives proc.

After Monday and Tuesday even the calendar says W T F ....
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-06-04 : 14:40:20
If you are using mount points like we are, xp_fixeddrives won't show the space. It only shows the space for the root drives, which is not helpful when your critical databases are on mount points.

I had to write this code to handle mount points: http://weblogs.sqlteam.com/tarad/archive/2007/12/18/60435.aspx

We are no longer using that code because SCOM provides the monitoring that we need, but if you need a T-SQL way to do it, you can use it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2012-06-04 : 15:53:30
Thanks Tara !

How should i setup an alert when the disk space is low with isp_DiskSpace procedure (CLR).
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-06-04 : 16:18:55
Depends how you are alerting now. We used Database Mail to send out alerts. We logged the output of the stored procedure for all of our servers into one table, believe we ran it hourly or maybe even more frequently. We then had another job that read that table to find issues and sent emails when there were issues. The emails were either regular emails or it went to our mobile devices. We set thresholds of 80-89 full as regular emails and 90+ full were sent to mobile devices.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-06-04 : 16:38:00
There's also a way to find mount point free space using PowerShell:

http://blogs.technet.com/b/josebda/archive/2010/04/08/using-powershell-v2-to-gather-info-on-free-space-on-the-volumes-of-your-remote-file-server.aspx
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-06-04 : 16:41:21
I have a PS script as well for this. I had to roll my own as none of the ones I found on the Internet worked on all of our servers.

Here's my PS script:



# Helper function to translate the scriptblock output into a DataTable
Function Out-DataTable {

$dt = new-object Data.datatable
$First = $true

foreach ($item in $input){
$DR = $DT.NewRow()
$Item.PsObject.get_properties() | foreach {
if ($first) {
$Col = new-object Data.DataColumn
$Col.ColumnName = $_.Name.ToString()
$DT.Columns.Add($Col) }
if ($_.value -eq $null) {
$DR.Item($_.Name) = "[empty]"
}
elseif ($_.IsArray) {
$DR.Item($_.Name) =[string]::Join($_.value ,";")
}
else {
$DR.Item($_.Name) = $_.value
}
}
$DT.Rows.Add($DR)
$First = $false
}

return @(,($dt))

} #Out-DataTable

# Get disk space information for each volume (drives and mounts)
Function Get-VolumeSpace ([string]$ServerName="localhost")
{
$wql="Select SystemName, Name, Capacity, FreeSpace From Win32_Volume Where DriveType=2 or DriveType=3"
$volSpace=Get-WmiObject -ComputerName $ServerName -Query $wql
$volSpace | Select SystemName, Name, Capacity, FreeSpace
} #Get-VolumeSpace


$dataTable = Get-VolumeSpace | Out-DataTable

$connectionString = "Data Source=Server1\Instance1;Integrated Security=true;Initial Catalog=DBA;"
$bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $connectionString
$bulkCopy.DestinationTableName = "VolumeSpace"
$bulkCopy.WriteToServer($dataTable)


Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2012-06-04 : 22:35:06
Thanks Tara/robvolk

I have only one server..
Go to Top of Page

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2012-06-04 : 22:43:47
Probably i would like to do this thru performance monitor alerts. Any idea?
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-06-04 : 23:19:59
PerfMon already contains a counter. No code needed. Check it out in the Logical Disk counters.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Sachin.Nand

2937 Posts

Posted - 2012-06-05 : 01:55:03
Looks like similar kind of question was asked here.

http://www.sqlservercentral.com/Forums/Topic1310911-1550-1.aspx

After Monday and Tuesday even the calendar says W T F ....
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-06-05 : 13:05:19
I hate cross posters, such a waste of time for us.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Sachin.Nand

2937 Posts

Posted - 2012-06-06 : 13:53:16
quote:
Originally posted by tkizer

I hate cross posters, such a waste of time for us.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog



But with this post at-least I understood that xp_fixeddrives are useless when you have mount points.

Thanks sqlfresher2k7 for posting it here.

After Monday and Tuesday even the calendar says W T F ....
Go to Top of Page

future_is_me
Starting Member

14 Posts

Posted - 2012-12-17 : 14:08:57
How can I make use of this script? If I put this script in action part of a basic task wizard (task scheduler) would this send me an email whenever the size falls below a specific value?

quote:
Originally posted by tkizer

I have a PS script as well for this. I had to roll my own as none of the ones I found on the Internet worked on all of our servers.

Here's my PS script:



# Helper function to translate the scriptblock output into a DataTable
Function Out-DataTable {

$dt = new-object Data.datatable
$First = $true

foreach ($item in $input){
$DR = $DT.NewRow()
$Item.PsObject.get_properties() | foreach {
if ($first) {
$Col = new-object Data.DataColumn
$Col.ColumnName = $_.Name.ToString()
$DT.Columns.Add($Col) }
if ($_.value -eq $null) {
$DR.Item($_.Name) = "[empty]"
}
elseif ($_.IsArray) {
$DR.Item($_.Name) =[string]::Join($_.value ,";")
}
else {
$DR.Item($_.Name) = $_.value
}
}
$DT.Rows.Add($DR)
$First = $false
}

return @(,($dt))

} #Out-DataTable

# Get disk space information for each volume (drives and mounts)
Function Get-VolumeSpace ([string]$ServerName="localhost")
{
$wql="Select SystemName, Name, Capacity, FreeSpace From Win32_Volume Where DriveType=2 or DriveType=3"
$volSpace=Get-WmiObject -ComputerName $ServerName -Query $wql
$volSpace | Select SystemName, Name, Capacity, FreeSpace
} #Get-VolumeSpace


$dataTable = Get-VolumeSpace | Out-DataTable

$connectionString = "Data Source=Server1\Instance1;Integrated Security=true;Initial Catalog=DBA;"
$bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $connectionString
$bulkCopy.DestinationTableName = "VolumeSpace"
$bulkCopy.WriteToServer($dataTable)


Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-12-17 : 16:08:14
quote:
Originally posted by future_is_me

How can I make use of this script? If I put this script in action part of a basic task wizard (task scheduler) would this send me an email whenever the size falls below a specific value?




The script that I posted just gets the information. You would have to write code to read the table and take action as necessary. We use this table for growth purposes and not for alerting purposes. We have separate alerting software (SCOM).

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

lopez
Starting Member

8 Posts

Posted - 2012-12-20 : 00:56:01
Whenever working with SQL Server it is a very difficult task to get information related to the hard disk space that is consumed by the SQL Server and its databases, and to perform required tasks to save the disk space from being misused or wasted with the Databases of the SQL Server

You can follow the step by step operation in SQL Server Management Studio for alerting

http://databases.about.com/od/sqlserver/ht/sqlserveralerts.htm
Go to Top of Page

gtopawb
Starting Member

5 Posts

Posted - 2012-12-27 : 02:10:29
unspammed
Go to Top of Page
   

- Advertisement -