Advanced Search

Determine Free Disk Space in SQL Server

/ Published in: SQL
Save to your folder(s)
This is not my code but I really like the fact that it's not using a COM object to do it's task such as the one I've been using for years.

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. -- SQL 2005/2008 ---------------------------------------------------------------------------
  2. CREATE PROCEDURE dbo.spExec_SufficientDiskSpace @MinMBFree INT, @Drive CHAR(1) AS
  3. /*
  4. ----------------------------------------------------------------------------
  5. -- Object Name: dbo.spExec_SufficientDiskSpace
  6. -- Project: Admin Scripts
  7. -- Business Process: Monthly Sales Reports
  8. -- Purpose: Validate sufficient disk space
  9. -- Detailed Description: Validate sufficient disk space based on based on the 
  10. -- @MBfree and @Drive parameters 
  11. -- Database: Admin
  12. -- Dependent Objects: master.sys.xp_fixeddrives
  13. -- Called By: Admin Scripts
  14. -- Upstream Systems: Unknown
  15. -- Downstream Systems: Unknown
  16. -- 
  17. --------------------------------------------------------------------------------------
  18. -- Rev | CMR | Date Modified | Developer | Change Summary
  19. --------------------------------------------------------------------------------------
  20. -- 001 | N\A | 03.05.2009 | MSSQLTips | Original code
  21. --
  22. */
  23.  
  24.  
  25. SET NOCOUNT ON
  26.  
  27. -- 1 - Declare variables
  28. DECLARE @MBfree INT
  29. DECLARE @CMD1 VARCHAR(1000)
  30.  
  31. -- 2 - Initialize variables
  32. SET @MBfree = 0
  33. SET @CMD1 = ''
  34.  
  35. -- 3 - Create temp tables
  36. CREATE TABLE #tbl_xp_fixeddrives
  37. (Drive VARCHAR(2) NOT NULL,
  38. [MB free] INT NOT NULL)
  39.  
  40. -- 4 - Populate #tbl_xp_fixeddrives
  41. INSERT INTO #tbl_xp_fixeddrives(Drive, [MB free])
  42. EXEC master.sys.xp_fixeddrives
  43.  
  44. -- 5 - Initialize the @MBfree value
  45. SELECT @MBfree = [MB free]
  46. FROM #tbl_xp_fixeddrives 
  47. WHERE Drive = @Drive
  48.  
  49. -- 6 - Determine if sufficient fre space is available
  50. IF @MBfree > @MinMBFree
  51.  BEGIN
  52.   RETURN
  53.  END
  54. ELSE
  55.  BEGIN
  56.   RAISERROR ('*** ERROR *** - Insufficient disk space.', 16, 1)
  57.  END
  58.  
  59. -- 7 - DROP TABLE #tbl_xp_fixeddrives
  60. DROP TABLE #tbl_xp_fixeddrives
  61.  
  62. SET NOCOUNT OFF
  63. GO
  64.  
  65.  
  66. -- SQL 2000 --------------------------------------------------------------------------------
  67. CREATE PROCEDURE dbo.spExec_SufficientDiskSpace @MinMBFree INT, @Drive CHAR(1) AS
  68. /*
  69. ----------------------------------------------------------------------------
  70. -- Object Name: dbo.spExec_SufficientDiskSpace
  71. -- Project: Admin Scripts
  72. -- Business Process: Monthly Sales Reports
  73. -- Purpose: Validate sufficient disk space
  74. -- Detailed Description: Validate sufficient disk space based on based on the 
  75. -- @MBfree and @Drive parameters 
  76. -- Database: Admin
  77. -- Dependent Objects: master.sys.xp_fixeddrives
  78. -- Called By: Admin Scripts
  79. -- Upstream Systems: Unknown
  80. -- Downstream Systems: Unknown
  81. -- 
  82. --------------------------------------------------------------------------------------
  83. -- Rev | CMR | Date Modified | Developer | Change Summary
  84. --------------------------------------------------------------------------------------
  85. -- 001 | N\A | 03.05.2009 | MSSQLTips | Original code
  86. --
  87. */
  88.  
  89. SET NOCOUNT ON
  90.  
  91. -- 1 - Declare variables
  92. DECLARE @MBfree INT
  93. DECLARE @CMD1 VARCHAR(1000)
  94.  
  95. -- 2 - Initialize variables
  96. SET @MBfree = 0
  97. SET @CMD1 = ''
  98.  
  99. -- 3 - Create temp tables
  100. CREATE TABLE #tbl_xp_fixeddrives
  101. (Drive VARCHAR(2) NOT NULL,
  102. [MB free] INT NOT NULL)
  103.  
  104. -- 4 - Populate #tbl_xp_fixeddrives
  105. INSERT INTO #tbl_xp_fixeddrives(Drive, [MB free])
  106. EXEC master.dbo.xp_fixeddrives
  107.  
  108. -- 5 - Initialize the @MBfree value
  109. SELECT @MBfree = [MB free]
  110. FROM #tbl_xp_fixeddrives 
  111. WHERE Drive = @Drive
  112.  
  113. -- 6 - Determine if sufficient fre space is available
  114. IF @MBfree > @MinMBFree
  115.  BEGIN
  116.   RETURN
  117.  END
  118. ELSE
  119.  BEGIN
  120.   RAISERROR ('*** ERROR *** - Insufficient disk space.', 16, 1)
  121.  END
  122.  
  123. -- 7 - DROP TABLE #tbl_xp_fixeddrives
  124. DROP TABLE #tbl_xp_fixeddrives
  125.  
  126. SET NOCOUNT OFF
  127. GO

URL: http://www.mssqltips.com/tip.asp?tip=1706

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.