MSSQL Sever 性能,磁盘等监控建立(一) 服务器磁盘,使用量监控

  今天公司需要做MSSQL Sever 服务器各项指标,性能的监控. 这次是服务器各磁盘的使用量,剩余空间等监控.写了一段脚本.本着和大家交流学习的目的,望看到此文章的朋友对于文中不足或者错误的地方不吝指正.

  此段脚本针对于单台服务器查询. 如果在一个服务器集群或者多台设备统计时,可以选择linked server 或者查旬结果bcp到一个公共的共享区域,再汇总进行统计.出于安全性和权限控制,我采用的是后者,公司不允许建立link server. 但是我想应该还有更好的更方便的方法.

脚本如下:

--***************************************************************************************
--F.HY(2014/5/04)
--Target:Ready for SQL Traffic Test
--This script is to calculate SQL SERVER DISK FULL SIZE, FREE SPACE 
--***************************************************************************************
USE master
Go

IF EXISTS (SELECT name FROM tempdb..sysobjects WHERE name = '#_DriveSpace')
	DROP TABLE #_DriveSpace

IF EXISTS (SELECT name FROM tempdb..sysobjects WHERE name = '#_DriveInfo')
	DROP TABLE #_DriveInfo
	
IF EXISTS (SELECT name FROM tempdb.sys.tables WHERE name = 'Temp_DriveInfo')
	DROP TABLE tempdb..Temp_DriveInfo


DECLARE @Result INT
	, @objFSO INT
	, @Drv INT 
	, @cDrive VARCHAR(20) 
	, @Size VARCHAR(50) 
	, @Free VARCHAR(50)
	, @Label VARCHAR(20)

CREATE TABLE #_DriveSpace 
	(
	  DriveLetter CHAR(1) not null
	, FreeSpace VARCHAR(10) not null

	 )

CREATE TABLE #_DriveInfo
	(
	DriveLetter CHAR(1)
	, TotalSpace float
	, FreeSpace float
	, Label varchar(10)
	)

INSERT INTO #_DriveSpace 
	EXEC master.dbo.xp_fixeddrives


-- Iterate through drive letters.
DECLARE curDriveLetters CURSOR
	FOR SELECT driveletter FROM #_DriveSpace

DECLARE @DriveLetter char(1)
	OPEN curDriveLetters

FETCH NEXT FROM curDriveLetters INTO @DriveLetter
WHILE (@@fetch_status <> -1)
BEGIN
	IF (@@fetch_status <> -2)
	BEGIN
		 SET @cDrive = 'GetDrive("' + @DriveLetter + '")' 
			EXEC @Result = sp_OACreate 'Scripting.FileSystemObject', @objFSO OUTPUT 
			    print @Result

				IF @Result = 0 

					EXEC @Result = sp_OAMethod @objFSO, @cDrive, @Drv OUTPUT 

				IF @Result = 0 

					EXEC @Result = sp_OAGetProperty @Drv,'TotalSize', @Size OUTPUT 

				IF @Result = 0 

					EXEC @Result = sp_OAGetProperty @Drv,'FreeSpace', @Free OUTPUT 

				IF @Result = 0 

					EXEC @Result = sp_OAGetProperty @Drv,'VolumeName', @Label OUTPUT 

				IF @Result <> 0 
 
					EXEC sp_OADestroy @Drv 
					EXEC sp_OADestroy @objFSO 

			SET @Size = (CONVERT(BIGINT,@Size) / 1048576 )

			SET @Free = (CONVERT(BIGINT,@Free) / 1048576 )

			INSERT INTO #_DriveInfo
				VALUES (@DriveLetter, @Size, @Free, @Label)

	END
	FETCH NEXT FROM curDriveLetters INTO @DriveLetter
END

CLOSE curDriveLetters
DEALLOCATE curDriveLetters

--PRINT 'Drive information for server ' + @@SERVERNAME + '.'
--PRINT ''

-- Produce report.

--INSERT INTO MonitorDB..SERVER_DISKSPACE 
SELECT @@SERVERNAME as ServerName 
    ,DriveLetter
	,Label
	,ROUND(TotalSpace/1024.0,3) AS [TotalSpace(GB)]
	,ROUND(FreeSpace/1024.0,3) AS [FreeSpace(GB)]
	,ROUND((TotalSpace - FreeSpace)/1024.0,3) AS [UsedSpace(GB)]
	,ROUND((FreeSpace / TotalSpace) * 100.0,2) AS [PercentageFree(%)],
     CONVERT(varchar(100), GETDATE(), 20)  AS [CheckTime]
INTO Tempdb..Temp_DriveInfo
FROM #_DriveInfo
ORDER BY [DriveLetter] ASC	
GO

DROP TABLE #_DriveSpace
DROP TABLE #_DriveInfo
GO

--******************************************
--BCP OUT Result into txt 
--******************************************

--DECLARE @Bcp_Command_out NVARCHAR(200),@S_Name NVARCHAR(50);
--SET @S_Name = @@SERVERNAME --+ '_'+ convert(varchar(19),getdate(),12);
--SET @Bcp_Command_out = 'BCP Tempdb..Temp_DriveInfo out Address_Here'+ @S_Name + '_DriveInfo.txt -c -T'  --Update suitable folder path for bcp command


--EXEC XP_CMDSHELL @Bcp_Command_out


--DROP TABLE tempdb..Temp_DriveInfo
--GO