Create the table "Capacity_DiskSpaceTracking". Script is available in the commented section.
Build the procedure
Run it:
exec [usp_Disk_Free_Space_Check]
Build the procedure
Run it:
exec [usp_Disk_Free_Space_Check]
@dbmail_profile= 'Operators',
@dbmail_recipient = 'youremail@domain.com' ,
@deleteEntry = 360; -- No Of Days!
USE [DB_Maint]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID(N'[dbo].[usp_Disk_Free_Space_Check]') IS NOT NULL
BEGIN
DROP PROCEDURE [dbo].[usp_Disk_Free_Space_Check]
END
GO
CREATE PROC [dbo].[usp_Disk_Free_Space_Check] (@dbmail_profile sysname = NULL, @dbmail_recipient sysname = NULL, @deleteEntry int = 360)
AS
BEGIN
SET NOCOUNT ON
BEGIN TRY
/*
Desc: 1. Calcualates each drive's growth rate and report them as Alerts.
2. If free disk space <=30, it will throw alert.
3. If free disk space <=20, it will throw alert.
4. If free disk space <=10, it will throw WARNING!! alert.
RUNNING Instructions :=
exec [usp_Disk_Free_Space_Check]
@dbmail_profile= 'Operators',
@dbmail_recipient = 'youremail@domain.com' ,
@deleteEntry = 360; -- No Of Days!
*/
/*
-----------------------------------------------------------
Step 1: Create the table.
USE [DB_Maint]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Capacity_DiskSpaceTracking](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Drive] varchar(10) NOT NULL,
[ServerName] nvarchar(4000) NULL,
[Free(MB)] varchar(1000) NULL,
[Total(MB)] varchar(1000) NULL,
[Free(%)] varchar(1000) NULL,
[Date_Entered] datetime NULL,
[login_sname] [sysname] NOT NULL DEFAULT (suser_sname())
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
*/
-- xp_fixeddrives
DECLARE @hr int
DECLARE @fso int
DECLARE @drive char(1)
DECLARE @odrive int
DECLARE @TotalSize varchar(20)
DECLARE @MB bigint;
SET @MB = 1048576 -- 1 GB
CREATE TABLE #drives (
drive char(1) PRIMARY KEY,
FreeSpace int NULL,
TotalSize int NULL
)
INSERT #drives (drive, FreeSpace)
EXEC master.dbo.xp_fixeddrives
EXEC @hr = sp_OACreate 'Scripting.FileSystemObject',
@fso OUT
IF @hr <> 0
EXEC sp_OAGetErrorInfo @fso
DECLARE dcur CURSOR LOCAL FAST_FORWARD FOR
SELECT
drive
FROM #drives
ORDER BY drive
OPEN dcur
FETCH NEXT FROM dcur INTO @drive
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC @hr = sp_OAMethod @fso,
'GetDrive',
@odrive OUT,
@drive
IF @hr <> 0
EXEC sp_OAGetErrorInfo @fso
EXEC @hr = sp_OAGetProperty @odrive,
'TotalSize',
@TotalSize OUT
IF @hr <> 0
EXEC sp_OAGetErrorInfo @odrive
UPDATE #drives
SET TotalSize = @TotalSize / @MB
WHERE drive = @drive
FETCH NEXT FROM dcur INTO @drive
END
CLOSE dcur
DEALLOCATE dcur
EXEC @hr = sp_OADestroy @fso
IF @hr <> 0
EXEC sp_OAGetErrorInfo @fso
SELECT
@@servername AS ServerName,
drive,
FreeSpace AS 'Free(MB)',
TotalSize AS 'Total(MB)',
CAST((FreeSpace / (TotalSize * 1.0)) * 100.0 AS int) AS 'Free(%)',
GETDATE() AS Date_Entered INTO #result_set
FROM #drives
-- Print Intermediate Results
--select * from #result_set
INSERT INTO [DB_Maint].[DBO].[Capacity_DiskSpaceTracking] ([ServerName],
[drive],
[Free(MB)],
[Total(MB)],
[Free(%)],
[Date_Entered])
SELECT
ServerName,
drive,
[Free(MB)],
[Total(MB)],
[Free(%)],
Date_Entered
FROM #result_set
--INSERT INTO Capacity_DiskSpaceTracking
--(DriveLetter, Label, FreeSpaceMB,UsedSpaceMB,TotalSpaceMB,FreeSpacePercentage)
--SELECT DriveLetter
-- , Label
-- , FreeSpace
-- , (TotalSpace - FreeSpace) AS [UsedSpace MB]
-- , TotalSpace
-- , ((CONVERT(NUMERIC(9,2),FreeSpace) / CONVERT(NUMERIC(9,2),TotalSpace)) * 100) AS [Percentage Free]
--FROM ##_DriveInfo
--ORDER BY [DriveLetter] ASC
-- Display Results.
SELECT
*
FROM [DB_Maint].[DBO].[Capacity_DiskSpaceTracking]
DECLARE @servername nvarchar(100),
@drive1 nvarchar(2),
@freeMB int,
@totalMB int,
@free int,
@date_entered nvarchar(50)
DECLARE db_crsr_T CURSOR FOR
SELECT
[ServerName],
[drive],
[Free(MB)],
[Total(MB)],
[Free(%)],
[Date_Entered]
FROM #result_set
OPEN db_crsr_T
FETCH NEXT FROM db_crsr_T INTO @servername, @drive1, @FreeMB, @totalMB, @free, @date_entered
WHILE @@fetch_status = 0
BEGIN
IF @free < 30
AND @free > 10
BEGIN
DECLARE @msg1 nvarchar(1000),
@subject nvarchar(4000)
SELECT
@subject = 'DISK SPACE ALERT !! ' + ' ' + SUBSTRING(@@servername, 1, 20) + ' Drive has < 30% Free Space '
-- + ' ' + 'in Database: ' + @DBNAME;
--declare @body1 nvarchar(max)
SET @msg1 = N'<H1> <Font Color = "red"> DRIVE FREE SPACE CHECK: </font> </H1>' +
N'<body><H3> <Font Color = "red"> Server: ' + @@servername + '</H3></font>' +
'<BR>' + '<BR>';
SET @msg1 = @msg1 + 'Instance ' + RTRIM(@servername) + ' only has ' + CONVERT(nvarchar(9), @freeMB) + ' MB free on disk ' + CONVERT(char(1), @drive1) + ':\. The percentage free is ' + CONVERT(nvarchar(3), @free) + '. Drive ' + CONVERT(char(1), @drive1) + ':\ has a total size of ' + LTRIM(CONVERT(nvarchar(10), @totalMB)) + ' MB and ' + CONVERT(nvarchar(9), @freeMB) + ' MB free.'
EXEC msdb.dbo.sp_send_dbmail @profile_name = @dbmail_profile, -- CHANGE THIS TO YOUR SERVERS MAIL PROFILE NAME...
@recipients = @dbmail_recipient, --CHANGE THIS TO YOUR EMAIL ADDRESS...
@body = @msg1,
@subject = @subject,
@body_format = 'HTML',
@importance = 'HIGH';
END
IF @free < 10
BEGIN
DECLARE @msg2 nvarchar(1000),
@subject2 nvarchar(4000);
SELECT
@subject2 = 'DISK SPACE ALERT !! ' + ' ' + SUBSTRING(@@servername, 1, 20) + ' Drive has < 10% Free Space ';
SET @msg2 = N'<H1> <Font Color = "red"> DRIVE FREE SPACE CHECK: WARNING !!! One Or more Drive has <10% free space !!! </font> </H1>' +
N'<body><H3> <Font Color = "red"> Server: ' + @@servername + '</H3></font>' +
'<BR>' + '<BR>';
SET @msg2 = @msg2 + '<Font Color = "red">WARNING!! </font> Instance ' + RTRIM(@servername) + ' only has ' + CONVERT(nvarchar(9), @freeMB) + ' MB free on disk ' + CONVERT(char(1), @drive1) + ':\. The percentage free is ' + CONVERT(nvarchar(3), @free) + '. Drive ' + CONVERT(char(1), @drive1) + ':\ has a total size of ' + LTRIM(CONVERT(nvarchar(10), @totalMB)) + ' MB and ' + CONVERT(nvarchar(9), @freeMB) + ' MB free.'
EXEC msdb.dbo.sp_send_dbmail @profile_name = @dbmail_profile, -- CHANGE THIS TO YOUR SERVERS MAIL PROFILE NAME...
@recipients = @dbmail_recipient, --CHANGE THIS TO YOUR EMAIL ADDRESS...
@body = @msg2,
@subject = @subject2,
@body_format = 'HTML',
@importance = 'HIGH';
END
FETCH NEXT FROM db_crsr_T INTO @servername, @drive1, @FreeMB, @totalMB, @free, @date_entered
END
CLOSE db_crsr_T
DEALLOCATE db_crsr_T
DROP TABLE #drives
DROP TABLE #result_set
-- Cleanup from physical table.
DECLARE @NumRecords varchar(20)
PRINT @deleteEntry;
-- Retain records for 60 days Old. Keep @deleteEntry = 60
DELETE FROM [DB_Maint].[DBO].[Capacity_DiskSpaceTracking]
WHERE [Date_Entered] <= (GETDATE() - @deleteEntry)
SELECT
@NumRecords = @@ROWCOUNT
SELECT
@NumRecords
IF @NumRecords > 0
BEGIN
--DELETE FROM Capacity_DiskSpaceTracking WHERE DATEDIFF(DAY, TimeCollected, GETDATE()) > @deleteEntry
DBCC CHECKIDENT ([DB_Maint.dbo.Capacity_DiskSpaceTracking], RESEED, 0);
END
END TRY
BEGIN CATCH
DECLARE @ErrorNumber int;
DECLARE @ErrorSeverity int;
DECLARE @ErrorState int;
DECLARE @ErrorLine int;
DECLARE @ErrorProcedure nvarchar(4000);
DECLARE @ErrorMessage nvarchar(4000);
SELECT
@ErrorNumber = ERROR_NUMBER(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(),
@ErrorLine = ERROR_LINE(),
@ErrorProcedure = ERROR_PROCEDURE();
SELECT
@ErrorMessage =
N'Error %d, Level %d, State %d, Procedure %s, Line %d, '
+ 'Message: ' + ERROR_MESSAGE();
SELECT
@ErrorMessage AS [Error_Message];
SELECT
@ErrorProcedure AS [Error_Procedure];
PRINT 'Error '
+ CONVERT(varchar(50), ERROR_NUMBER())
+ ', Severity '
+ CONVERT(varchar(5), ERROR_SEVERITY())
+ ', State '
+ CONVERT(varchar(5), ERROR_STATE())
+ ', Procedure '
+ ISNULL(ERROR_PROCEDURE(), '-') + ', Line '
+ CONVERT(varchar(5), ERROR_LINE());
PRINT ERROR_MESSAGE();
END CATCH
SET NOCOUNT OFF
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID(N'[dbo].[usp_Disk_Free_Space_Check]') IS NOT NULL
BEGIN
DROP PROCEDURE [dbo].[usp_Disk_Free_Space_Check]
END
GO
CREATE PROC [dbo].[usp_Disk_Free_Space_Check] (@dbmail_profile sysname = NULL, @dbmail_recipient sysname = NULL, @deleteEntry int = 360)
AS
BEGIN
SET NOCOUNT ON
BEGIN TRY
/*
Desc: 1. Calcualates each drive's growth rate and report them as Alerts.
2. If free disk space <=30, it will throw alert.
3. If free disk space <=20, it will throw alert.
4. If free disk space <=10, it will throw WARNING!! alert.
RUNNING Instructions :=
exec [usp_Disk_Free_Space_Check]
@dbmail_profile= 'Operators',
@dbmail_recipient = 'youremail@domain.com' ,
@deleteEntry = 360; -- No Of Days!
*/
/*
-----------------------------------------------------------
Step 1: Create the table.
USE [DB_Maint]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Capacity_DiskSpaceTracking](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Drive] varchar(10) NOT NULL,
[ServerName] nvarchar(4000) NULL,
[Free(MB)] varchar(1000) NULL,
[Total(MB)] varchar(1000) NULL,
[Free(%)] varchar(1000) NULL,
[Date_Entered] datetime NULL,
[login_sname] [sysname] NOT NULL DEFAULT (suser_sname())
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
*/
-- xp_fixeddrives
DECLARE @hr int
DECLARE @fso int
DECLARE @drive char(1)
DECLARE @odrive int
DECLARE @TotalSize varchar(20)
DECLARE @MB bigint;
SET @MB = 1048576 -- 1 GB
CREATE TABLE #drives (
drive char(1) PRIMARY KEY,
FreeSpace int NULL,
TotalSize int NULL
)
INSERT #drives (drive, FreeSpace)
EXEC master.dbo.xp_fixeddrives
EXEC @hr = sp_OACreate 'Scripting.FileSystemObject',
@fso OUT
IF @hr <> 0
EXEC sp_OAGetErrorInfo @fso
DECLARE dcur CURSOR LOCAL FAST_FORWARD FOR
SELECT
drive
FROM #drives
ORDER BY drive
OPEN dcur
FETCH NEXT FROM dcur INTO @drive
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC @hr = sp_OAMethod @fso,
'GetDrive',
@odrive OUT,
@drive
IF @hr <> 0
EXEC sp_OAGetErrorInfo @fso
EXEC @hr = sp_OAGetProperty @odrive,
'TotalSize',
@TotalSize OUT
IF @hr <> 0
EXEC sp_OAGetErrorInfo @odrive
UPDATE #drives
SET TotalSize = @TotalSize / @MB
WHERE drive = @drive
FETCH NEXT FROM dcur INTO @drive
END
CLOSE dcur
DEALLOCATE dcur
EXEC @hr = sp_OADestroy @fso
IF @hr <> 0
EXEC sp_OAGetErrorInfo @fso
SELECT
@@servername AS ServerName,
drive,
FreeSpace AS 'Free(MB)',
TotalSize AS 'Total(MB)',
CAST((FreeSpace / (TotalSize * 1.0)) * 100.0 AS int) AS 'Free(%)',
GETDATE() AS Date_Entered INTO #result_set
FROM #drives
-- Print Intermediate Results
--select * from #result_set
INSERT INTO [DB_Maint].[DBO].[Capacity_DiskSpaceTracking] ([ServerName],
[drive],
[Free(MB)],
[Total(MB)],
[Free(%)],
[Date_Entered])
SELECT
ServerName,
drive,
[Free(MB)],
[Total(MB)],
[Free(%)],
Date_Entered
FROM #result_set
--INSERT INTO Capacity_DiskSpaceTracking
--(DriveLetter, Label, FreeSpaceMB,UsedSpaceMB,TotalSpaceMB,FreeSpacePercentage)
--SELECT DriveLetter
-- , Label
-- , FreeSpace
-- , (TotalSpace - FreeSpace) AS [UsedSpace MB]
-- , TotalSpace
-- , ((CONVERT(NUMERIC(9,2),FreeSpace) / CONVERT(NUMERIC(9,2),TotalSpace)) * 100) AS [Percentage Free]
--FROM ##_DriveInfo
--ORDER BY [DriveLetter] ASC
-- Display Results.
SELECT
*
FROM [DB_Maint].[DBO].[Capacity_DiskSpaceTracking]
DECLARE @servername nvarchar(100),
@drive1 nvarchar(2),
@freeMB int,
@totalMB int,
@free int,
@date_entered nvarchar(50)
DECLARE db_crsr_T CURSOR FOR
SELECT
[ServerName],
[drive],
[Free(MB)],
[Total(MB)],
[Free(%)],
[Date_Entered]
FROM #result_set
OPEN db_crsr_T
FETCH NEXT FROM db_crsr_T INTO @servername, @drive1, @FreeMB, @totalMB, @free, @date_entered
WHILE @@fetch_status = 0
BEGIN
IF @free < 30
AND @free > 10
BEGIN
DECLARE @msg1 nvarchar(1000),
@subject nvarchar(4000)
SELECT
@subject = 'DISK SPACE ALERT !! ' + ' ' + SUBSTRING(@@servername, 1, 20) + ' Drive has < 30% Free Space '
-- + ' ' + 'in Database: ' + @DBNAME;
--declare @body1 nvarchar(max)
SET @msg1 = N'<H1> <Font Color = "red"> DRIVE FREE SPACE CHECK: </font> </H1>' +
N'<body><H3> <Font Color = "red"> Server: ' + @@servername + '</H3></font>' +
'<BR>' + '<BR>';
SET @msg1 = @msg1 + 'Instance ' + RTRIM(@servername) + ' only has ' + CONVERT(nvarchar(9), @freeMB) + ' MB free on disk ' + CONVERT(char(1), @drive1) + ':\. The percentage free is ' + CONVERT(nvarchar(3), @free) + '. Drive ' + CONVERT(char(1), @drive1) + ':\ has a total size of ' + LTRIM(CONVERT(nvarchar(10), @totalMB)) + ' MB and ' + CONVERT(nvarchar(9), @freeMB) + ' MB free.'
EXEC msdb.dbo.sp_send_dbmail @profile_name = @dbmail_profile, -- CHANGE THIS TO YOUR SERVERS MAIL PROFILE NAME...
@recipients = @dbmail_recipient, --CHANGE THIS TO YOUR EMAIL ADDRESS...
@body = @msg1,
@subject = @subject,
@body_format = 'HTML',
@importance = 'HIGH';
END
IF @free < 10
BEGIN
DECLARE @msg2 nvarchar(1000),
@subject2 nvarchar(4000);
SELECT
@subject2 = 'DISK SPACE ALERT !! ' + ' ' + SUBSTRING(@@servername, 1, 20) + ' Drive has < 10% Free Space ';
SET @msg2 = N'<H1> <Font Color = "red"> DRIVE FREE SPACE CHECK: WARNING !!! One Or more Drive has <10% free space !!! </font> </H1>' +
N'<body><H3> <Font Color = "red"> Server: ' + @@servername + '</H3></font>' +
'<BR>' + '<BR>';
SET @msg2 = @msg2 + '<Font Color = "red">WARNING!! </font> Instance ' + RTRIM(@servername) + ' only has ' + CONVERT(nvarchar(9), @freeMB) + ' MB free on disk ' + CONVERT(char(1), @drive1) + ':\. The percentage free is ' + CONVERT(nvarchar(3), @free) + '. Drive ' + CONVERT(char(1), @drive1) + ':\ has a total size of ' + LTRIM(CONVERT(nvarchar(10), @totalMB)) + ' MB and ' + CONVERT(nvarchar(9), @freeMB) + ' MB free.'
EXEC msdb.dbo.sp_send_dbmail @profile_name = @dbmail_profile, -- CHANGE THIS TO YOUR SERVERS MAIL PROFILE NAME...
@recipients = @dbmail_recipient, --CHANGE THIS TO YOUR EMAIL ADDRESS...
@body = @msg2,
@subject = @subject2,
@body_format = 'HTML',
@importance = 'HIGH';
END
FETCH NEXT FROM db_crsr_T INTO @servername, @drive1, @FreeMB, @totalMB, @free, @date_entered
END
CLOSE db_crsr_T
DEALLOCATE db_crsr_T
DROP TABLE #drives
DROP TABLE #result_set
-- Cleanup from physical table.
DECLARE @NumRecords varchar(20)
PRINT @deleteEntry;
-- Retain records for 60 days Old. Keep @deleteEntry = 60
DELETE FROM [DB_Maint].[DBO].[Capacity_DiskSpaceTracking]
WHERE [Date_Entered] <= (GETDATE() - @deleteEntry)
SELECT
@NumRecords = @@ROWCOUNT
SELECT
@NumRecords
IF @NumRecords > 0
BEGIN
--DELETE FROM Capacity_DiskSpaceTracking WHERE DATEDIFF(DAY, TimeCollected, GETDATE()) > @deleteEntry
DBCC CHECKIDENT ([DB_Maint.dbo.Capacity_DiskSpaceTracking], RESEED, 0);
END
END TRY
BEGIN CATCH
DECLARE @ErrorNumber int;
DECLARE @ErrorSeverity int;
DECLARE @ErrorState int;
DECLARE @ErrorLine int;
DECLARE @ErrorProcedure nvarchar(4000);
DECLARE @ErrorMessage nvarchar(4000);
SELECT
@ErrorNumber = ERROR_NUMBER(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(),
@ErrorLine = ERROR_LINE(),
@ErrorProcedure = ERROR_PROCEDURE();
SELECT
@ErrorMessage =
N'Error %d, Level %d, State %d, Procedure %s, Line %d, '
+ 'Message: ' + ERROR_MESSAGE();
SELECT
@ErrorMessage AS [Error_Message];
SELECT
@ErrorProcedure AS [Error_Procedure];
PRINT 'Error '
+ CONVERT(varchar(50), ERROR_NUMBER())
+ ', Severity '
+ CONVERT(varchar(5), ERROR_SEVERITY())
+ ', State '
+ CONVERT(varchar(5), ERROR_STATE())
+ ', Procedure '
+ ISNULL(ERROR_PROCEDURE(), '-') + ', Line '
+ CONVERT(varchar(5), ERROR_LINE());
PRINT ERROR_MESSAGE();
END CATCH
SET NOCOUNT OFF
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
No comments:
Post a Comment