Revision: 12400
Initial Code
Initial URL
Initial Description
Initial Title
Initial Tags
Initial Language
at March 12, 2009 17:30 by pollusb
Initial Code
-- 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
Initial URL
http://www.mssqltips.com/tip.asp?tip=1706
Initial Description
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.
Initial Title
Determine Free Disk Space in SQL Server
Initial Tags
Initial Language
SQL