These days suddenly have an idea: want to be able to automatically monitor and collect the database server disk capacity information, when a threshold is reached, automatically send an alarm message to the DBA, the database disk details to the DBA, remind the DBA to do a storage planning plan, The initial idea is to call the stored procedure through the job (called Once a day), so as to avoid every day I each database server go up to check, especially the database server at hand more than N, so that I can avoid wasting unnecessary time every day. If you have better suggestions and methods, welcome to pointing Twos, I organized, modified the three stored procedures are as follows:
Stored Procedure 1:SP_DISKCAPACITYALERT1.PRC
Description: You need to obtain disk information by calling the OLE automatic stored procedure, which, based on the security configuration of the server, is usually disabled, we open the service through sp_configure in the stored procedure, and after the service is finished, the service is disabled through sp_configure. In addition, the database server is located in the intranet, so security issues should be small.
Use master;
GO
SET ANSI_NULLS on
GO
SET QUOTED_IDENTIFIER ON
GO
IF EXISTS (SELECT 1 from dbo.sysobjects WHERE id = object_id (N ' Sp_diskcapacity_alert1 ') and OBJECTPROPERTY (ID, ' Isprocedur E ') =1)
DROP PROCEDURE Sp_diskcapacity_alert1;
GO
--============================================================================================================= =====
-- procedurename : sp_diskcapacity_alert1
-- Author : Kerry
-- createdate : 2013-05-02
-- Description : Gets the disk capacity of the server on which the database resides, sends an alert message when the threshold is reached, alerts the DBA to a storage planning plan
/************************************************************************************************************** ****
Modified Date Modified User Version Modified Reason
2013-05-6 Kerry V01.00.00 Modify the HTML output style. and disk capacity output to GB
*************************************************************************************************************** ****/
--============================================================================================================= =====
CREATE PROCEDURE [dbo]. [Sp_diskcapacity_alert1]
(
@Threshold NUMERIC
)
As
SET NOCOUNT on
DECLARE @Result INT;
DECLARE @objectInfo INT;
DECLARE @DriveInfo CHAR (1);
DECLARE @TotalSize VARCHAR (20);
DECLARE @OutDrive INT;
DECLARE @UnitMB BIGINT;
DECLARE @HtmlContent
DECLARE @FreeRat NUMERIC;
DECLARE @EmailHead VARCHAR (120);
SET @UnitMB = 1048576;
--Create temporary table to save server disk capacity information
CREATE TABLE #DiskCapacity
(
[DISKCD] CHAR (1),
Freesize INT ,
TotalSize INT
);
INSERT #DiskCapacity
EXEC master.dbo.xp_fixeddrives;
EXEC sp_configure ' show advanced options ', 1
RECONFIGURE with OVERRIDE;
EXEC sp_configure ' Ole Automation procedures ', 1;
RECONFIGURE with OVERRIDE;
EXEC @Result = Master.sys.sp_OACreate ' Scripting.FileSystemObject ', @objectInfo out;
DECLARE cr_diskinfo CURSOR LOCAL fast_forward
For SELECT diskcd from #DiskCapacity
ORDER by DISKCD
OPEN Cr_diskinfo;
FETCH NEXT from Cr_diskinfo to @DriveInfo
While @ @FETCH_STATUS =0
BEGIN
EXEC @Result = sp_OAMethod @objectInfo, ' getdrive ', @OutDrive out, @DriveInfo
EXEC @Result = sp_OAGetProperty @OutDrive, ' totalsize ', @TotalSize out
UPDATE #DiskCapacity
SET [Email protected]/@UnitMB
WHERE [email protected]
FETCH NEXT from Cr_diskinfo to @DriveInfo
END
CLOSE Cr_diskinfo
Deallocate Cr_diskinfo;
EXEC @Result =sp_oadestroy @objectInfo
EXEC sp_configure ' show advanced options ', 1
RECONFIGURE with OVERRIDE;
EXEC sp_configure ' Ole Automation procedures ', 0;
RECONFIGURE with OVERRIDE;
EXEC sp_configure ' show advanced options ', 0
RECONFIGURE with OVERRIDE;
SELECT @FreeRat =freerate
From (
SELECT row_number () over (ORDER by freesize/(TotalSize * 1.0) ASC) as RowIndex,
CAST ((Freesize/(TotalSize * 1.0)) * 100.0 as INT) as freerate
From #DiskCapacity
) T
WHERE RowIndex = 1;
IF @FreeRat <= @Threshold
BEGIN
SET @EmailHead = ' Database disk capacity alarm (Alarm level 3) '
ELSE IF @FreeRat >=5 and @FreeRat <=10
SET @EmailHead = ' Database disk capacity alarm (Alarm level 4) '
ELSE
SET @EmailHead = ' Database disk capacity alarm (Alarm level 5) '
SET @HtmlContent =
+ N '
+ N ' <style type= "text/css" > '
+ N ' td {Border:solid #9ec9ec; border-width:0px 1px 1px 0px; padding:4px 0px;} '
+ N ' table {border:1px solid #9ec9ec; width:100%;border-width:1px 0px 0px 1px;text-align:center;font-size:12px} '
+ N ' </style> '
+ N '
+ N ' <table > '
+ N ' <tr><th> disk drive letter </th><th> total size (GB) </th><th> used space (GB) </th><th > Remaining space (GB) </th> '
+ N ' <th> used ratio (%) </th><th> remaining ratio (%) </th></tr > ' +
TD = DISKCD , ",
td = STR (totalsize*1.0/1024,6,2) , ",
TD = STR ((totalsize-freesize) *1.0/1024,6,2) , ",
td = STR (freesize*1.0/1024,6,2) , ",
TD = STR ((totalsize-freesize) *1.0/(totalsize) * 100.0,6,2), ",
TD = STR ((freesize * 1.0/(totalsize )) * 100.0,6,2) , '
From #DiskCapacity
For XML PATH (' tr '), TYPE ) as NVARCHAR (MAX)) +
EXEC Msdb.dbo.sp_send_dbmail
@profile_name = ' database_ddl_event ', --Specify your own profile_name
@recipients = ' ****@163.com ', --Specify the mailbox you want to send to
@subject = ' Server disk space alert ',
@body = @HtmlContent,
END
DROP TABLE #DiskCapacity;
RETURN;
GO
Stored Procedure 2:SP_DISKCAPACITYALERT2.PRC
Note: You need to enable xp_cmdshell to get disk information about xp_cmdshell security implications, which is generally disabled.
Use [master]
GO
SET ANSI_NULLS on
GO
SET QUOTED_IDENTIFIER ON
GO
IF object_id (N ' Dbo.sp_diskcapacity_alert2 ') is not NULL
DROP PROCEDURE Dbo.sp_diskcapacity_alert2;
GO
--============================================================================================================= =====
-- procedurename : sp_diskcapacity_alert2
-- Author : Kerry
-- createdate : 2013-05-02
--- Description : Gets the disk capacity of the server on which the database resides, sends an alert message when the threshold is reached, alerts the DBA to a storage planning plan
/************************************************************************************************************** ****
Modified Date Modified User Version Modified Reason
2013-05-6 Kerry V01.00.00 Modify the HTML output style. and disk capacity output to GB
*************************************************************************************************************** ****/
--============================================================================================================= =====
CREATE PROCEDURE [dbo]. [Sp_diskcapacity_alert2]
(
@Threshold NUMERIC
)
BEGIN
SET NOCOUNT on;
DECLARE @HtmlContent
DECLARE @FreeRat NUMERIC;
DECLARE @EmailHead VARCHAR (200);
--Create temporary table to save server disk capacity information
CREATE TABLE #DiskCapacity
(
DISKCD CHAR (4),
Freesize INT ,
TotalSize BIGINT
);
INSERT into #DiskCapacity
(DISKCD, Freesize)
EXEC Master. xp_fixeddrives;
EXEC sp_configure ' show advanced options ', 1
RECONFIGURE
EXEC sp_configure ' xp_cmdshell ', 1
RECONFIGURE
EXEC sp_configure ' show advanced options ', 0
RECONFIGURE
CREATE TABLE #DriveInfo1 (ID INT IDENTITY (), DISKCD VARCHAR (12));
INSERT into #DriveInfo1 (DISKCD)
EXEC xp_cmdshell ' wmic logicaldisk get name ';
CREATE TABLE #DriveInfo2 (ID INT IDENTITY (), TotalSize VARCHAR (22));
INSERT into #DriveInfo2
(totalsize)
EXEC xp_cmdshell ' wmic logicaldisk get size ';
DELETE from #DriveInfo1 WHERE id=1;
DELETE from #DriveInfo2 WHERE id=1;
UPDATE #DriveInfo1 SET diskcd = REPLACE (diskcd, ': ', ');
SELECT * from #DiskCapacity
UPDATE #DiskCapacity SET totalsize = (SELECT CAST (left (n.totalsize, LEN (n.totalsize)-1) as BIGINT)/1024/1024 from # DriveInfo1 M INNER JOIN #DriveInfo2 N on m.id = n.id
WHERE M.DISKCD is isn't NULL and LEN (M.DISKCD) >1 and #DiskCapacity. DISKCD = Left (M.DISKCD, LEN (M.DISKCD)-1))
SELECT * from #DiskCapacity
EXEC sp_configure ' show advanced options ', 1
RECONFIGURE
EXEC sp_configure ' xp_cmdshell ', 0
RECONFIGURE
EXEC sp_configure ' show advanced options ', 0
RECONFIGURE
SELECT @FreeRat =freerate
From (
SELECT row_number () over (ORDER by freesize/(TotalSize * 1.0) ASC) as RowIndex,
CAST ((Freesize/(TotalSize * 1.0)) * 100.0 as INT) as freerate
From #DiskCapacity
) T
WHERE RowIndex = 1;
IF @FreeRat <= @Threshold
BEGIN
SET @EmailHead = ' Database disk capacity alarm (Alarm level 3) '
ELSE IF @FreeRat >=5 and @FreeRat <=10
SET @EmailHead = ' Database disk capacity alarm (Alarm level 4) '
ELSE
SET @EmailHead = ' Database disk capacity alarm (Alarm level 5) '
SET @HtmlContent =
+ N '
+ N ' <style type= "text/css" > '
+ N ' td {Border:solid #9ec9ec; border-width:0px 1px 1px 0px; padding:4px 0px;} '
+ N ' table {border:1px solid #9ec9ec; width:100%;border-width:1px 0px 0px 1px;text-align:center;font-size:12px} '
+ N ' </style> '
+ N '
+ N ' <table > '
+ N ' <tr><th> disk drive letter </th><th> total size (GB) </th><th> used space (GB) </th><th > Remaining space (GB) </th> '
+ N ' <th> used ratio (%) </th><th> remaining ratio (%) </th></tr > ' +
TD = DISKCD , ",
td = STR (totalsize*1.0/1024,6,2) , ",
TD = STR ((totalsize-freesize) *1.0/1024,6,2) , ",
td = STR (freesize*1.0/1024,6,2) , ",
TD = STR ((totalsize-freesize) *1.0/(totalsize) * 100.0,6,2), ",
TD = STR ((freesize * 1.0/(totalsize )) * 100.0,6,2) , '
From #DiskCapacity
For XML PATH (' tr '), TYPE ) as NVARCHAR (MAX)) +
EXEC Msdb.dbo.sp_send_dbmail
@profile_name = ' database_ddl_event ',--Specify your own profile_name
@recipients = ' [email protected]***.com ', --Specify the mailbox you want to send to
@subject = ' Server disk space alert ',
@body = @HtmlContent,
END
GO
Stored Procedure 3:SP_DISKCAPACITYALERT2.PRC
Note: This stored procedure does not use the above two stored procedures with security implications, but the overall information of the disk can not be obtained by a threshold to alarm, can only set the amount of space left on the disk when the alarm message generated.
Use [master]
GO
SET ANSI_NULLS on
GO
SET QUOTED_IDENTIFIER ON
GO
IF object_id (N ' dbo.sp_diskcapacity_alert3 ') is not NULL
DROP PROCEDURE Dbo.sp_diskcapacity_alert3;
GO
--============================================================================================================= =====
-- procedurename : sp_diskcapacity_alert3
-- Author : Kerry
-- createdate : 2013-05-02
-- Description : Gets the disk capacity of the server on which the database resides, sends an alert message when the remaining capacity of a disk is below a certain value.
-- remind DBAs to plan for storage planning
/************************************************************************************************************** ****
Modified Date Modified User Version Modified Reason
2013-05-6 Kerry V01.00.00 Modify the HTML output style. and disk capacity output to GB
*************************************************************************************************************** ****/
--============================================================================================================= =====
CREATE PROCEDURE [dbo]. [SP_DISKCAPACITY_ALERT3]
(
@DiskCapacity FLOAT
)
BEGIN
DECLARE @FreeSize INT;
DECLARE @EmailHead VARCHAR (200);
DECLARE @HtmlContent
--Create temporary table to save server disk capacity information
CREATE TABLE #DiskCapacity
(
DISKCD CHAR (4),
Freesize INT
);
INSERT into #DiskCapacity
(DISKCD, Freesize)
EXEC Master. xp_fixeddrives;
SELECT @FreeSize = freesize*1.0/1024
From (SELECT row_number () over (ORDER by Freesize ASC) as RowIndex,
Freesize as Freesize
From #DiskCapacity
) T
WHERE RowIndex = 1;
SELECT freesize*1.0/1024 from #DiskCapacity;
BEGIN
IF @FreeSize > 1
SET @EmailHead = ' Database disk capacity alarm (Alarm level 3) '
IF @FreeSize >= 0.5
SET @EmailHead = ' Database disk capacity alarm (Alarm level 4) '
SET @EmailHead = ' Database disk capacity alarm (Alarm level 5) '
SET @HtmlContent = +n '
+ N ' td {Border:solid #9ec9ec; border-width:0px 1px 1px 0px; padding:4px 0px;} '
+ N ' Table {border:1px solid #9ec9ec; width:100%;border-width:1px 0px 0px 1px;text-align:center;font-size:12px} '
+ N ' </style> '
+ N '
+ @EmailHead + ' </H1> ' + N ' <table > '
+ n ' <tr><th> disk letter </th><th> remaining space (GB) </th> ' + n ' </tr > '
+ CAST ((SELECT td = DISKCD,
‘‘ ,
td = STR (Freesize * 1.0/1024, 6, 2),
‘‘
From #DiskCapacity
For
XML PATH (' tr '),
TYPE
EXEC Msdb.dbo.sp_send_dbmail
@profile_name = ' database_ddl_event ', --Specify your own profile_name
@recipients = ' [email protected]***.com ', --Specify the mailbox you want to send to
@subject = ' Server disk space alert ',
@body = @HtmlContent,
END
END
GO
MS SQL Monitors disk space alarms