PoshCode Logo PowerShell Code Repository

Get-SQLDatabaseFreespace (modification of post by view diff)
embed code: <script type="text/javascript" src="http://PoshCode.org/embed/1086"></script>download | new post

I created this script because of the need to monitor a MSDE 2000 database which have reached the 2 GB database limit until the application administrator deleted old data from the database. SQL Server 2008 Management Studio Express must be installed on the computer running the script as a scheduled task.

  1. ###########################################################################"
  2. #
  3. # NAME: Get-SQLDatabaseFreespace.ps1
  4. #
  5. # AUTHOR: Jan Egil Ring
  6. # EMAIL: jan.egil.ring@powershell.no
  7. #
  8. # COMMENT: Requires SQL Server 2008 Management Studio Express. The script gets free space from the specified SQL Database
  9. #              and sends the result to the specified e-mail address.
  10. #
  11. # You have a royalty-free right to use, modify, reproduce, and
  12. # distribute this script file in any way you find useful, provided that
  13. # you agree that the creator, owner above has no warranty, obligations,
  14. # or liability for such use.
  15. #
  16. # VERSION HISTORY:
  17. # 1.0 10.05.2009 - Initial release
  18. #
  19. ###########################################################################"
  20.  
  21. #Add SQL Server 2008 PowerShell snapin
  22. Add-Pssnapin SqlServerProviderSnapin100
  23.  
  24. #Get free space in specified database
  25. cd SQLSERVER:\SQL\SQL-server-name\Instance-name\Databases
  26. $DB =  Get-Item "Database01"
  27. $SpaceAvailable = $DB.SpaceAvailable
  28. $formattedresult = $SpaceAvailable
  29. $result = "{0:#.00}" -f ($SpaceAvailable/1kb)
  30.  
  31. #Send result to specified e-mail address
  32. $smtpServer = "smtp-server-name"
  33. $msg = new-object Net.Mail.MailMessage
  34. $smtp = new-object Net.Mail.SmtpClient($smtpServer)
  35. $msg.From = "sender@domain.local"
  36. $msg.To.Add("recipient@domain.local")
  37. $msg.Subject = "Free space in Database01"
  38. $msg.Body = "There are $result MB free space in Database01"
  39. $smtp.Send($msg)

Submit a correction or amendment below (click here to make a fresh posting)
After submitting an amendment, you'll be able to view the differences between the old and new posts easily.

Syntax highlighting:


Remember me