Determine Free Disk Space in SQL Server with T-SQL Code
来源:互联网 发布:数据统计html 编辑:程序博客网 时间:2024/05/16 02:26
Determine Free Disk Space in SQL Server with T-SQL Code
Written By: Edgewood Solutions Engineers -- 3/12/2009 -- 1 comments
Stay informed - get the MSSQLTips.com newsletter and win - click here
Problem
At our organization we need to check for a minimum amount of free space before proceeding with some processes that run on SQL Server 2000, 2005 and 2008 SQL Server instances. Do you know of a way to find out the free disk space and then fail the process if it does not meet the minimum requirements? Can you provide some sample code?
Solution
Checking for free disk space before proceeding with a process is a wise move if disk space is tight or a high percentage of the drive is needed for the process. It is disconcerting to have a process run for hours only to fail towards the end of the process due to insufficient disk space. Although a few different options are available to check for disk space (CLR, WMI, PowerShell, etc.) in SQL Server, let's see how we can use the xp_fixeddrives extended stored procedure which is available in SQL Server 2000 to 2008.
Sample Stored Procedure to Assess the Free Disk Space on a SQL Server Disk Drive
In the sample stored procedure below, it is accepting a parameter for the minimum amount of megabytes (MB) free on a specific disk drive, then executing the master.sys.xp_fixeddrives extended stored procedure into a temporary table. Once the data is in the temporary table the current amount of free disk space is compared to the minimum amount of free disk space to determine if the process should continue or raise an error.
One item to keep in mind is that between SQL Server 2000 and SQL Server 2005/2008 the owner for the xp_fixeddrives extended stored procedure changed. In SQL Server 2000, xp_fixeddrives was owned by dbo and in SQL Server 2005/2008 the owner is sys. Due to this ownership change, two stored procedures are provided below. One for SQL Server 2005/2008 and a second for SQL Server 2000.
*** NOTE *** - SQL Server 2008 and 2005 Version
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 variablesDECLARE @MBfree intDECLARE @CMD1 varchar(1000)
-- 2 - Initialize variablesSET @MBfree = 0SET @CMD1 = ''
-- 3 - Create temp tablesCREATE TABLE #tbl_xp_fixeddrives(Drive varchar(2) NOT NULL,[MB free] int NOT NULL)
-- 4 - Populate #tbl_xp_fixeddrivesINSERT INTO #tbl_xp_fixeddrives(Drive, [MB free])EXEC master.sys.xp_fixeddrives
-- 5 - Initialize the @MBfree valueSELECT @MBfree = [MB free]FROM #tbl_xp_fixeddrives WHERE Drive = @Drive
-- 6 - Determine if sufficient fre space is availableIF @MBfree > @MinMBFree BEGIN RETURN ENDELSE BEGIN RAISERROR ('*** ERROR *** - Insufficient disk space.', 16, 1) END
-- 7 - DROP TABLE #tbl_xp_fixeddrivesDROP TABLE #tbl_xp_fixeddrives
SET NOCOUNT OFFGO
*** NOTE *** - SQL Server 2000 Version
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 variablesDECLARE @MBfree intDECLARE @CMD1 varchar(1000)
-- 2 - Initialize variablesSET @MBfree = 0SET @CMD1 = ''
-- 3 - Create temp tablesCREATE TABLE #tbl_xp_fixeddrives(Drive varchar(2) NOT NULL,[MB free] int NOT NULL)
-- 4 - Populate #tbl_xp_fixeddrivesINSERT INTO #tbl_xp_fixeddrives(Drive, [MB free])EXEC master.dbo.xp_fixeddrives
-- 5 - Initialize the @MBfree valueSELECT @MBfree = [MB free]FROM #tbl_xp_fixeddrives WHERE Drive = @Drive
-- 6 - Determine if sufficient fre space is availableIF @MBfree > @MinMBFree BEGIN RETURN ENDELSE BEGIN RAISERROR ('*** ERROR *** - Insufficient disk space.', 16, 1) END
-- 7 - DROP TABLE #tbl_xp_fixeddrivesDROP TABLE #tbl_xp_fixeddrives
SET NOCOUNT OFFGO
Next Steps
- If your organization has processes that require a large amount of disk space or a high percentage of a particular disk, be sure to validate the disk drives have sufficient storage at the beginning of the process or at key points. A quick check could save a great deal of time, frustration and re-work.
- Using the master.sys.xp_fixeddrives extended stored procedure as outlined in the stored procedures above is one of many ways to check for free disk space. CLR, PowerShell, WMI, etc. also have options to check for disk space, so do what makes sense for your code and environment.
- Check out these related tips on MSSQLTips:
- Accessing the Windows File System from SQL Server
- Easing the SQL Server Database Capacity Planning Burden
- Capacity Planning for SQL Server 2000 Database Storage
- Enabling xp_cmdshell in SQL Server 2005
- Where is the Surface Area Configuration tool in SQL Server 2008
- Determine Free Disk Space in SQL Server with T-SQL Code
- SQL Server :理解Page Free Space (PFS) 页 02
- SQL Server: Add column with default value and description in T-SQL
- Free, simple code to find out what SQL statements are running slow in SQL Server right now
- Why my SQL transaction log grows so large and my disk doesn't have enough space.
- Beginning T-SQL with Microsoft SQL Server 2005 and 2008
- SQL Server 2008 function types in T-SQL
- [原创]T-SQL Enhancement in SQL Server 2005 - Part II
- [原创]T-SQL Enhancement in SQL Server 2005 - Part I
- How to search the available space in SQL server.
- T-SQL with关键字
- T-SQL with关键字
- T-SQL with关键字
- T-SQL WITH AS
- T-SQL with关键字
- SQL Server 存储(4/8):理解Page Free Space (PFS) 页
- SQL Server cluster failover fails with Error Code: 0x80071398
- T-sql(SQL Server)
- javascript实现通用表单验证函数
- IP组播技术综述 (2)
- JFLEX--词法分析器用户手册
- 关于flex中mx_internal命名空间
- 获取GridView的TemplateField模版中某列值
- Determine Free Disk Space in SQL Server with T-SQL Code
- JS--自定义对象的几种方法
- 4.20
- ORACLE DUAL表详解
- DBI模块方法简介
- 节省日常开销的三十二种方法
- 实时验证用户输入的js代码
- pervasive数据集成器(Pervasive Data Integrator)
- SQL Server ID自增列重新从1开始算起