SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 disk space alert
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sqlfresher2k7
Aged Yak Warrior

609 Posts

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

Sachin.Nand
Flowing Fount of Yak Knowledge

2937 Posts

Posted - 06/04/2012 :  14:16:55  Show Profile  Reply with Quote
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

USA
36953 Posts

Posted - 06/04/2012 :  14:40:20  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

Edited by - tkizer on 06/04/2012 14:40:42
Go to Top of Page

sqlfresher2k7
Aged Yak Warrior

609 Posts

Posted - 06/04/2012 :  15:53:30  Show Profile  Reply with Quote
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

USA
36953 Posts

Posted - 06/04/2012 :  16:18:55  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

USA
15671 Posts

Posted - 06/04/2012 :  16:38:00  Show Profile  Visit robvolk's Homepage  Reply with Quote
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

USA
36953 Posts

Posted - 06/04/2012 :  16:41:21  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

609 Posts

Posted - 06/04/2012 :  22:35:06  Show Profile  Reply with Quote
Thanks Tara/robvolk

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

sqlfresher2k7
Aged Yak Warrior

609 Posts

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

tkizer
Almighty SQL Goddess

USA
36953 Posts

Posted - 06/04/2012 :  23:19:59  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

2937 Posts

Posted - 06/05/2012 :  01:55:03  Show Profile  Reply with Quote
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

USA
36953 Posts

Posted - 06/05/2012 :  13:05:19  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

2937 Posts

Posted - 06/06/2012 :  13:53:16  Show Profile  Reply with Quote
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

USA
14 Posts

Posted - 12/17/2012 :  14:08:57  Show Profile  Reply with Quote
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

USA
36953 Posts

Posted - 12/17/2012 :  16:08:14  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

USA
8 Posts

Posted - 12/20/2012 :  00:56:01  Show Profile  Reply with Quote
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 - 12/27/2012 :  02:10:29  Show Profile  Reply with Quote
unspammed
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.25 seconds. Powered By: Snitz Forums 2000