Posted By

pollusb on 03/12/09

Tagged

Versions (?)

Last Edited at 03/12/09 05:30pm

Statistics

Viewed 726 times

Favorited by 0 user(s)

Related snippets

Determine Free Disk Space in SQL Server

/ Published in: SQL

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.

Highlighted TextExpand

-- SQL 2005/2008 ---------------------------------------------------------------------------
CREATE PROCEDURE dbo.spExec_SufficientDiskSpace @MinMBFree int, @Drive char(1) AS
/*
----------------------------------------------------------------------------
-- Object Name: dbo.spExec_SufficientDiskSpace
-- Project: Admin Scripts
-- Business Process: Monthly Sales Reports
-- Purpose: Validate sufficient disk space
-- Detailed Description: Validate sufficient disk space based on based on the 
-- @MBfree and @Drive parameters 
-- Database: Admin
-- Dependent Objects: master.sys.xp_fixeddrives
-- Called By: Admin Scripts
-- Upstream Systems: Unknown
-- Downstream Systems: Unknown
-- 
--------------------------------------------------------------------------------------
-- Rev | CMR | Date Modified | Developer | Change Summary
--------------------------------------------------------------------------------------
-- 001 | N\A | 03.05.2009 | MSSQLTips | Original code
--
*/

SET NOCOUNT ON

-- 1 - Declare variables
DECLARE @MBfree int
DECLARE @CMD1 varchar(1000)

-- 2 - Initialize variables
SET @MBfree = 0
SET @CMD1 = ''

-- 3 - Create temp tables
CREATE TABLE #tbl_xp_fixeddrives
(Drive varchar(2) NOT NULL,
[MB free] int NOT NULL)

-- 4 - Populate #tbl_xp_fixeddrives
INSERT INTO #tbl_xp_fixeddrives(Drive, [MB free])
EXEC master.sys.xp_fixeddrives

-- 5 - Initialize the @MBfree value
SELECT @MBfree = [MB free]
FROM #tbl_xp_fixeddrives 
WHERE Drive = @Drive

-- 6 - Determine if sufficient fre space is available
IF @MBfree > @MinMBFree
 BEGIN
  RETURN
 END
ELSE
 BEGIN
  RAISERROR ('*** ERROR *** - Insufficient disk space.', 16, 1)
 END

-- 7 - DROP TABLE #tbl_xp_fixeddrives
DROP TABLE #tbl_xp_fixeddrives

SET NOCOUNT OFF
GO

-- SQL 2000 --------------------------------------------------------------------------------
CREATE PROCEDURE dbo.spExec_SufficientDiskSpace @MinMBFree int, @Drive char(1) AS
/*
----------------------------------------------------------------------------
-- Object Name: dbo.spExec_SufficientDiskSpace
-- Project: Admin Scripts
-- Business Process: Monthly Sales Reports
-- Purpose: Validate sufficient disk space
-- Detailed Description: Validate sufficient disk space based on based on the 
-- @MBfree and @Drive parameters 
-- Database: Admin
-- Dependent Objects: master.sys.xp_fixeddrives
-- Called By: Admin Scripts
-- Upstream Systems: Unknown
-- Downstream Systems: Unknown
-- 
--------------------------------------------------------------------------------------
-- Rev | CMR | Date Modified | Developer | Change Summary
--------------------------------------------------------------------------------------
-- 001 | N\A | 03.05.2009 | MSSQLTips | Original code
--
*/

SET NOCOUNT ON

-- 1 - Declare variables
DECLARE @MBfree int
DECLARE @CMD1 varchar(1000)

-- 2 - Initialize variables
SET @MBfree = 0
SET @CMD1 = ''

-- 3 - Create temp tables
CREATE TABLE #tbl_xp_fixeddrives
(Drive varchar(2) NOT NULL,
[MB free] int NOT NULL)

-- 4 - Populate #tbl_xp_fixeddrives
INSERT INTO #tbl_xp_fixeddrives(Drive, [MB free])
EXEC master.dbo.xp_fixeddrives

-- 5 - Initialize the @MBfree value
SELECT @MBfree = [MB free]
FROM #tbl_xp_fixeddrives 
WHERE Drive = @Drive

-- 6 - Determine if sufficient fre space is available
IF @MBfree > @MinMBFree
 BEGIN
  RETURN
 END
ELSE
 BEGIN
  RAISERROR ('*** ERROR *** - Insufficient disk space.', 16, 1)
 END

-- 7 - DROP TABLE #tbl_xp_fixeddrives
DROP TABLE #tbl_xp_fixeddrives

SET NOCOUNT OFF
GO

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

Report this snippet Tweet

Comments

Subscribe to comments

Comment:

You need to login to post a comment.