Sql-server – Get disk space percentage free with SQL Server 2005

disk-spacesql-server-2005

I am currently using SQL Server 2005 and (undocumented I believe) master..xp_fixeddrives to get free space on my database volumes as part of my monitoring.

However, this only gives me an absolute number of MB free. What I really need is percentage free.

Is there another way in SQL Server 2005 to get this? If not, is there some other light-weight way to get it? If I can, I want to avoid installing a Java JRE, or Perl, or Python on my database server. Perhaps vbscript, or a small Windows executable on the file system?

Yes, I know I can Google this, and I have. It looks like there are a few ways to accomplish it, and I'm curious how my DBA brethren have handled this.

Best Answer

This can be done through PowerShell. See the below:

Get-PSDrive |
    Where-Object {$_.Provider -like "*FileSystem"} |
    Select-Object Name, Used, Free, @{Name = "Used_Percent"; expression = {$_.Used / ($_.Used + $_.Free) * 100}}

Or, through WMI:

Get-WmiObject -Class Win32_LogicalDisk |
    Select-Object DeviceID, VolumeName, @{Name = "Used_Percent"; expression = {($_.Size - $_.FreeSpace) / $_.Size * 100}}

Please see Aaron's comment to your question. I completely agree with him, this is not something that is easily done through SQL Server. You can finagle this to be run within SQL Server, but it's not necessarily a native way to get this information directly from SQL Server.

Related Solutions

How to free up disk space? which logs/directories to clean

You can change the default retention policy of oracle log and trace files.

Source: https://streetkiter.wordpress.com/2011/04/06/do-you-really-need-one-year-old-logs-and-traces-for-your-oracle-database/

Relevant extract:
There are two parameters: SHORTP_POLICY (default 720 hours) and LONGP_POLICY (default 8720 hours). so SHORTP_POLICY is 30 days while LONGP_POLICY is 365 days.

From the commandline run "adrci". In adrci, use the following commands.

adrci> show homes
....(homes are listed)...
adrci> set home diag/....(the location)
adrci> show control
.... (current settings are listed) ....
adrci> set control (SHORTP_POLICY = 168)
adrci> set control (LONGP_POLICY = 720)

And now the traces are dropped after one week (SHORTP) or one month (LONGP).

Sql-server – SQL Server 2005: Full-text search space requirements

Here is some old code I wrote for SQL 2000. It still works on SQL 2005. You can use this to come up with a rough estimate of how much space you are using per document, then multiply that by your estimated number of documents.

   select 'CatalogName'   = left([name],30),
          'Status'        = case(FULLTEXTCATALOGPROPERTY ([name],'populatestatus'))
                              when 0 then 'Idle'
                              when 1 then 'Full population in progress'
                              when 2 then 'Paused'
                              when 3 then 'Throttled'
                              when 4 then 'Recovering'
                              when 5 then 'Shutdown'
                              when 6 then 'Incremental population in progress'
                              when 7 then 'Building index'
                              when 8 then 'Disk is full.  Paused.'
                              when 9 then 'Change tracking'
                              else 'Unknown'       
                            end,
          'ItemCount'     = FULLTEXTCATALOGPROPERTY (name,'itemcount'),
          'IndexSize(MB)' = FULLTEXTCATALOGPROPERTY (name,'indexsize'),
          'UniqueWords'   = FULLTEXTCATALOGPROPERTY (name,'uniquekeycount'),
          'ErrorLogBytes' = FULLTEXTCATALOGPROPERTY (name,'logsize'),
          'Location'      = left(s.path,50) 
   from sysfulltextcatalogs s

Best Answer

Related Solutions

How to free up disk space? which logs/directories to clean

Sql-server – SQL Server 2005: Full-text search space requirements

Related Question