-- 查看磁盤(pán)可用空間
EXEC master.dbo.xp_fixeddrives

--======================================================================
--查看數(shù)據(jù)庫(kù)文件使用的磁盤(pán)空間使用情況
WITH T1 AS (
SELECT DISTINCT
REPLACE(vs.volume_mount_point,':\','') AS Drive_Name ,
CAST(vs.total_bytes / 1024.0 / 1024 / 1024 AS NUMERIC(18,2)) AS Total_Space_GB ,
CAST(vs.available_bytes / 1024.0 / 1024 / 1024 AS NUMERIC(18,2)) AS Free_Space_GB
FROM  sys.master_files AS f
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id) AS vs
)
SELECT
Drive_Name,
Total_Space_GB,
Total_Space_GB-Free_Space_GB AS Used_Space_GB,
Free_Space_GB,
CAST(Free_Space_GB*100/Total_Space_GB AS NUMERIC(18,2)) AS Free_Space_Percent
FROM T1

USE [monitor]
GO

/****** Object: StoredProcedure [dbo].[usp_get_disk_free_size]  Script Date: 2016/5/25 18:21:11 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:    GGA
-- Create date:  2016-2-1
-- Description:  收集磁盤(pán)剩余空間信息
-- =============================================
CREATE PROCEDURE [dbo].[usp_get_disk_free_size]
AS
BEGIN
  SET NOCOUNT ON;
  SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

--==========================================
--創(chuàng)建相關(guān)表

IF OBJECT_ID('server_disk_usage') IS NULL
BEGIN
  CREATE TABLE [dbo].[server_disk_usage](
    [disk_num] [nvarchar](10) NOT NULL,
    [total_size_mb] [bigint] NOT NULL CONSTRAINT [DF_server_disk_usage_total_size_mb] DEFAULT ((0)),
    [free_siez_mb] [bigint] NOT NULL CONSTRAINT [DF_server_disk_usage_free_siez_mb] DEFAULT ((0)),
    [disk_info] [nvarchar](400) NOT NULL CONSTRAINT [DF_server_disk_usage_disk_info] DEFAULT (''),
    [check_time] [datetime] NOT NULL CONSTRAINT [DF_server_disk_usage_check_time] DEFAULT (getdate()),
     CONSTRAINT [PK_server_disk_usage] PRIMARY KEY CLUSTERED 
    (
      [disk_num] ASC
    )
  ) ON [PRIMARY]
END

--==========================================
--查看所有數(shù)據(jù)庫(kù)使用到的磁盤(pán)剩余空間
DECLARE @disk TABLE(
    [disk_num] VARCHAR(50),
    [free_siez_mb] INT)
INSERT INTO @disk
EXEC xp_fixeddrives

--更新當(dāng)前磁盤(pán)的剩余空間信息
UPDATE M
SET M.[free_siez_mb]=D.[free_siez_mb]
FROM [dbo].[server_disk_usage] AS M
INNER JOIN @disk AS D
ON M.[disk_num]=D.[disk_num]

--插入新增磁盤(pán)的剩余空間信息
INSERT INTO [dbo].[server_disk_usage]
(
  [disk_num],
  [free_siez_mb]
)
SELECT 
[disk_num],
[free_siez_mb]
FROM @disk AS D
WHERE NOT EXISTS(
  SELECT 1
  FROM [dbo].[server_disk_usage] AS M 
  WHERE M.[disk_num]=D.[disk_num] )

END

GO

/****** Object: StoredProcedure [dbo].[usp_get_disk_total_size]  Script Date: 2016/5/25 18:21:11 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:    GGA
-- Create date:  2016-2-1
-- Description:  收集磁盤(pán)總空間信息
-- =============================================
CREATE PROCEDURE [dbo].[usp_get_disk_total_size]
AS
BEGIN
  SET NOCOUNT ON;
  SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

IF NOT EXISTS(SELECT * FROM [dbo].[server_disk_usage]
    WHERE [total_size_mb] = 0)
BEGIN
  RETURN;
END

--==========================================
--開(kāi)啟CMDShell
EXEC sp_configure 'show advanced options',1;

RECONFIGURE WITH OVERRIDE;

EXEC sp_configure 'xp_cmdshell',1;

RECONFIGURE WITH OVERRIDE

--========================================
--創(chuàng)建臨時(shí)表用來(lái)存放每個(gè)盤(pán)符的數(shù)據(jù)
CREATE TABLE #tempDisks
(
  ID INT IDENTITY(1,1),
  DiskSpace NVARCHAR(200)
)
--============================================
--將需要檢查的磁盤(pán)放入臨時(shí)表#checkDisks
SELECT 
ROW_NUMBER()OVER(ORDER BY [disk_num]) AS RID,
[disk_num]
INTO #checkDisks
FROM [dbo].[server_disk_usage] 
WHERE [total_size_mb] = 0;

--============================================
--循環(huán)臨時(shí)表#checkDisks檢查每個(gè)磁盤(pán)的總量

DECLARE @disk_num NVARCHAR(20)
DECLARE @total_size_mb INT
DECLARE @sql NVARCHAR(200)
DECLARE @max INT
DECLARE @min INT
SELECT @max=MAX(RID),@min=MIN(RID) FROM #checkDisks

WHILE(@min<=@max)
BEGIN
SELECT @disk_num=[disk_num] 
FROM #checkDisks WHERE RID=@min

SET @sql = N'EXEC sys.xp_cmdshell ''fsutil volume diskfree '+@disk_num+':'+''''
PRINT @sql

INSERT INTO #tempDisks
EXEC sys.sp_executesql @sql

SELECT @total_size_mb=CAST((RIGHT(DiskSpace,LEN(DiskSpace)
  -CHARINDEX(': ',DiskSpace)-1)) AS BIGINT)/1024/1024
FROM #tempDisks WHERE id = 2

SELECT @total_size_mb,@disk_num

UPDATE [dbo].[server_disk_usage]
SET [total_size_mb]=@total_size_mb
WHERE [disk_num]=@disk_num

--SELECT * FROM #tempDisks

TRUNCATE TABLE #tempDisks

SET @min=@min+1

END

--==========================================
--CMDShell

EXEC sp_configure 'xp_cmdshell',0;

EXEC sp_configure 'show advanced options',1;

RECONFIGURE WITH OVERRIDE;

END

GO

/****** Object: StoredProcedure [dbo].[usp_get_disk_usage]  Script Date: 2016/5/25 18:21:11 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:    GGA
-- Create date:  2016-2-1
-- Description:  收集磁盤(pán)總空間信息
-- =============================================
CREATE PROCEDURE [dbo].[usp_get_disk_usage]
AS
BEGIN
  SET NOCOUNT ON;
  SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

  EXEC [dbo].[usp_get_disk_free_size]
  EXEC [dbo].[usp_get_disk_total_size]

  SELECT 
  [disk_num] AS Drive_Name
  ,CAST([total_size_mb]/1024.0 AS NUMERIC(18,2)) AS Total_Space_GB
  ,CAST(([total_size_mb]-[free_siez_mb])/1024.0 AS NUMERIC(18,2)) AS Used_Space_GB
  ,CAST([free_siez_mb]/1024.0 AS NUMERIC(18,2)) AS Free_Space_GB
  ,CAST([free_siez_mb]*100/[total_size_mb] AS NUMERIC(18,2)) AS Free_Space_Percent
  ,[disk_info]
  ,[check_time]
  FROM [monitor].[dbo].[server_disk_usage]
END
GO
--==================================
--查看磁盤(pán)空間使用
EXEC [dbo].[usp_get_disk_usage]

BEGIN TRAN
EXEC sp_configure 'show advanced options',1;
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'xp_cmdshell',1;
RECONFIGURE WITH OVERRIDE;
COMMIT

配置選項(xiàng) 'show advanced options' 已從 0 更改為 1。請(qǐng)運(yùn)行 RECONFIGURE 語(yǔ)句進(jìn)行安裝。
消息 574，級(jí)別 16，狀態(tài) 0，第 3 行
在用戶事務(wù)內(nèi)不能使用 CONFIG 語(yǔ)句。
配置選項(xiàng) 'xp_cmdshell' 已從 0 更改為 1。請(qǐng)運(yùn)行 RECONFIGURE 語(yǔ)句進(jìn)行安裝。
消息 574，級(jí)別 16，狀態(tài) 0，第 5 行
在用戶事務(wù)內(nèi)不能使用 CONFIG 語(yǔ)句。

DECLARE @sql VARCHAR(2000)
SET @sql ='
COMMIT;
EXEC sp_configure ''show advanced options'',1;
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure ''xp_cmdshell'',1;
RECONFIGURE WITH OVERRIDE;
'
EXEC(@sql)

消息 3902，級(jí)別 16，狀態(tài) 1，第 2 行
COMMIT TRANSACTION 請(qǐng)求沒(méi)有對(duì)應(yīng)的 BEGIN TRANSACTION。
配置選項(xiàng) 'show advanced options' 已從 1 更改為 1。請(qǐng)運(yùn)行 RECONFIGURE 語(yǔ)句進(jìn)行安裝。
配置選項(xiàng) 'xp_cmdshell' 已從 1 更改為 1。請(qǐng)運(yùn)行 RECONFIGURE 語(yǔ)句進(jìn)行安裝。

--====================================
--使用xp_cmdshell來(lái)執(zhí)行CMD命令
--獲取磁盤(pán)扇區(qū)信息
EXEC sp_configure 'show advanced options',1 
GO
RECONFIGURE
GO
sp_configure 'xp_cmdshell',1 
GO
RECONFIGURE
GO
EXEC xp_cmdshell 'fsutil fsinfo ntfsinfo D: | find "每個(gè)"';
GO
sp_configure 'xp_cmdshell',0 
GO
RECONFIGURE
GO
sp_configure 'show advanced options', 0 
GO
RECONFIGURE
GO