using System;
using System.Data;using System.Data.SqlClient;using System.Data.SqlTypes;using System.Diagnostics;using Microsoft.SqlServer.Server;public partial class StoredProcedures{[Microsoft.SqlServer.Server.SqlProcedure]
public static void isp_DiskSpace(string serverName){
if (serverName == ""){
serverName =
Environment.MachineName;}
PerformanceCounterCategory pcc = new PerformanceCounterCategory("LogicalDisk", serverName);
SqlDataRecord record = new SqlDataRecord(
new SqlMetaData("Drive/MountPoint", SqlDbType.NVarChar, 256),
new SqlMetaData("Capacity (MB)", SqlDbType.VarChar, 256),
new SqlMetaData("Used Space (MB)", SqlDbType.VarChar, 256),
new SqlMetaData("Free Space (MB)", SqlDbType.VarChar, 256),
new SqlMetaData("Percent Free Space", SqlDbType.VarChar, 6));
SqlContext.Pipe.SendResultsStart(record);
foreach (string instanceName in pcc.GetInstanceNames()){
PerformanceCounter pcPercentFree = new PerformanceCounter("LogicalDisk", "% Free Space", instanceName, serverName);
PerformanceCounter pcFreeMbytes = new PerformanceCounter("LogicalDisk", "Free Megabytes", instanceName, serverName);
float percentfree = pcPercentFree.NextValue();
float freespace = pcFreeMbytes.NextValue();
float capacity = (freespace * 100) / percentfree;
float usedspace = capacity - freespace;
if (instanceName != "_Total"){
record.SetSqlString(0, instanceName);
record.SetSqlString(1, capacity.ToString());
record.SetSqlString(2, usedspace.ToString());
record.SetSqlString(3, freespace.ToString());
record.SetSqlString(4, percentfree.ToString());
SqlContext.Pipe.SendResultsRow(record);}
}
SqlContext.Pipe.SendResultsEnd();}
};
----------------------------------------------------------
Please note these points
Create Project in Visual Studio 2010 or earlier version named DLLproject and their output type is Class Library and the target framework is .Net Framework 3.0
Once used the above code and compiled / build - the output files will appear in bin\debug folder of the project.
Please copy those and keep it in the server where you want to use this.
--------------------------------------------------------------
USE masterGO
EXEC
sp_configure 'show advanced options', 1RECONFIGURE
EXEC sp_configure 'clr enabled', 1RECONFIGURE
ALTER DATABASE dbmonitor SET TRUSTWORTHY ONGO
USE
dbmonitorGOCREATE
ASSEMBLY DiskSpace FROM 'G:\Space\DLLProject.dll'WITH PERMISSION_SET = UNSAFEGO
CREATE
PROC dbo.isp_DiskSpace @serverName nvarchar(4000)WITH EXECUTE AS CALLERAS
EXTERNAL
NAME DiskSpace.StoredProcedures.isp_DiskSpaceGO--Create Table to store Table Space information
--local serverCreate Table T_SpaceCheck (Drive varchar(50), Total varchar(50),UsedSpace varchar(50),FreeSpace varchar(50),FreeSpacePercent decimal(13,2))GO
Insert into T_SpaceCheck (Drive,Total,UsedSpace,FreeSpace,FreeSpacePercent)EXEC dbo.isp_DiskSpace @serverName = ''GO
select * from t_spacecheck where LEN(drive)<=2 -- It will give the drive information leaving the mount drive.or EXEC dbo.isp_DiskSpace @serverName = 'LocalServe'GO--remote serverEXEC dbo.isp_DiskSpace @serverName = 'RemoteServer'
GO
ALERT PROCEDURE TO CALL FOR FREE DISK SPACE MONITORING:
USE [msdb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[P_SpaceCheck] (@Freepercentage int = 50 , @AlertFrequency INT = 60)
as
BEGIN
SET NOCOUNT ON;
DECLARE @BATCHID INT,
@COUNT INT,
@Body VARCHAR(max),
@subject VARCHAR(100),
@TableHead VARCHAR(max),
@TableTail VARCHAR(max),
@servername sysname,
@MAXID INT
SELECT @MAXID = ISNULL(MAX(ID),0) FROM T_SpaceCheckBatchID (NOLOCK)
SELECT @MAXID AS MAXID
IF @MAXID = 0 GOTO Branch_One
SELECT @COUNT=COUNT(1) from T_SpaceCheckBatchID
WHERE DATEDIFF(MINUTE,Updateddate,GETDATE()) > @AlertFrequency
AND STATUS = 2
AND ID=@MAXID
SELECT @COUNT AS COUNTBASEDONALERTFREQ
IF @COUNT > 0 GOTO Branch_One
IF @COUNT = 0 RETURN
Branch_One:
INSERT INTO T_SpaceCheckBatchID (Updateddate)
SELECT GETDATE()
SELECT @BATCHID= SCOPE_IDENTITY ()
CREATE TABLE #T_SpaceCheck
(
[Drive] [varchar](50) NULL,
[Total] [varchar](50) NULL,
[UsedSpace] [varchar](50) NULL,
[FreeSpace] [varchar](50) NULL,
[FreeSpacePercent] [decimal](13, 2) NULL
)
Insert into #T_SpaceCheck (Drive,Total,UsedSpace,FreeSpace,FreeSpacePercent)
EXEC dbo.isp_DiskSpace @serverName = ''
SELECT @COUNT = COUNT(1) FROM #T_SpaceCheck WHERE LEN(drive)<=2 AND
FreeSpacePercent < @Freepercentage
SELECT @COUNT AS CHECKINGFREEPERCENTAGE
IF (@COUNT = 0)
RETURN
INSERT INTO T_SpaceCheck (Batchid, Drive,Total,UsedSpace,FreeSpace,FreeSpacePercent)
SELECT @BATCHID,Drive,Total,UsedSpace,FreeSpace,FreeSpacePercent FROM #T_SpaceCheck
WHERE LEN(drive)<=2 AND FreeSpacePercent < @Freepercentage
SET @servername = @@servername
SET @TableTail = '</table></body></html>';
SET @TableHead = '<html><head>' +
'<style>' +
'td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:11pt;} ' +
'</style>' +
'</head>' +
'<body><table cellpadding=0 cellspacing=0 border=0>' +
'<tr bgcolor=#FFEFD8><td align=center><b>Drive</b></td>' +
'<td align=center><b>Total</b></td>' +
'<td align=center><b>UsedSpace</b></td>' +
'<td align=center><b>FreeSpace</b></td>' +
'<td align=center><b>FreeSpacePercent</b></td>' +
'<td align=center><b>Updateddate</b></td></tr>';
Select @Body = (Select Row_Number() Over(Order By scb.Updateddate) % 2 As [TRRow],
sc.Drive As [TD],
sc.Total As [TD],
sc.UsedSpace As [TD],
sc.FreeSpace As [TD],
sc.FreeSpacePercent As [TD],
scb.Updateddate As [TD]
FROM T_SpaceCheck sc
INNER JOIN T_SpaceCheckBatchID scb
ON sc.BatchID = scb.ID
WHERE sc.BatchID=@BATCHID AND LEN(sc.drive)<=2 AND sc.FreeSpacePercent < @Freepercentage
ORDER BY scb.Updateddate ASC
For XML raw('tr'), Elements)
-- Replace the entity codes and row numbers
Set @Body = Replace(@Body, '_x0020_', space(1))
Set @Body = Replace(@Body, '_x003D_', '=')
Set @Body = Replace(@Body, '<tr><TRRow>1</TRRow>', '<tr bgcolor=#C6CFFF>')
Set @Body = Replace(@Body, '<TRRow>0</TRRow>', '')
Select @Body = @TableHead + @Body + @TableTail
SET @subject = 'Disk Space Alert - SQL Server - '+@servername+'.'
-- return output
--select @subject;
EXEC msdb.dbo.sp_send_dbmail
--@profile_name = 'EventManager Alerting', -- replace with your SQL Database Mail Profile
@body = @Body,
@body_format ='HTML',
@recipients = 'test@hotmail.com, -- replace with your email address
@subject = @subject
UPDATE T_SpaceCheckBatchID SET [STATUS]=2
WHERE ID=@batchid and [STATUS]=1
SET NOCOUNT OFF;
END
No comments:
Post a Comment