April 30, 2015 at 8:36 am
Hi Experts,
Is there any automated script available to - "Monitor Database Growth and if any DB is grown by 20%, sending mail alerts"? If not, can you give some ideas about the approach to write the T SQL script ?
Thanks
Thanks.
April 30, 2015 at 2:29 pm
I created an Agent job based on the code I found here:
http://strictlysql.blogspot.com/2011/01/finding-last-database-growth-date-and.html
It runs slowly, and b/c it's querying the trace file, I don't think you'd want this running constantly. I have it run nightly as an alert to me.
Rich
April 30, 2015 at 2:45 pm
20% compared to what? Previous day? Previous month?
You have to record the database size in a table at regular intervals and then run a job to check the increment over time.
BTW, if you enable the Data Collector, that information is collected by one of the system collection sets every 6 hours.
-- Gianluca Sartori
April 30, 2015 at 3:43 pm
I'll also add that it'll be a real surprise for someone if you wait for 20% growth on a terabyte database before you report it. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
April 30, 2015 at 3:45 pm
I managed that from msdb..backupset.
Thanks.
April 30, 2015 at 3:53 pm
HI Jeff,
Good Evening.
Below is the script, I have written..but my woorry is the table in Mailer, does not populate with the records,. I surprise it may be due to datatype conversion/using HTML script issue.
Is there any way, you can suggest how to fix the part..?
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'usp_growthcheck')
AND type = N'P')
DROP PROCEDURE usp_growthcheck;
GO
create Proc usp_growthcheck ( @dbmail_profile SYSNAME = null, @dbmail_recipient SYSNAME = null, @value INT= 6)
as
BEGIN
SET nocount ON
BEGIN try
/*
*/
IF OBJECT_ID('tempdb..##tbl_DataSize') IS NOT NULL
DROP TABLE ##tbl_DataSize
CREATE TABLE ##tbl_DataSize
(
SizeDECIMAL(20)
)
IF OBJECT_ID('tempdb..#tbl_GrowthData') IS NOT NULL
DROP TABLE #tbl_GrowthData
CREATE TABLE #tbl_GrowthData
(
DatabaseNameVARCHAR(max)
,NO_OF_SAMPLE_DAYSDECIMAL(20,3)
,DataSizeMBDECIMAL(20,3)
,LogSizeMBDECIMAL(20,3)
,BackupSizeMBDECIMAL(20,3)
,TotalDBSpaceMBDECIMAL(20,3)
,DataGrowthDECIMAL(20,3)
,LogGrowthDECIMAL(20,3)
,GrowthPercentageDECIMAL(20,3)
)
DECLARE
@iNoSamplesINT
,@nMaxBackupSizeDECIMAL
,@nMinBackupSizeDECIMAL
,@nMaxLogSizeDECIMAL
,@nMinLogSizeDECIMAL
,@nMaxDataSizeDECIMAL
,@nMinDataSizeDECIMAL
,@vcDatabaseNameVARCHAR(max)
,@dtMaxBackupTimeDATETIME
,@dtMinBackupTimeDATETIME
,@iMinBackupIDINT
,@iMaxBackupIDINT
IF (SELECT Cursor_status('global', 'file_cursor')) >=
-1
BEGIN
IF (SELECT Cursor_status('global',
'file_cursor')
) >
-1
BEGIN
CLOSE file_cursor
END
DEALLOCATE file_cursor
END
DECLARE file_cursor CURSOR FOR
SELECT [NAME]
FROM sys.databases
WHERE database_id IN (SELECT dbid
FROM sys.sysdatabases
EXCEPT
SELECT database_id
FROM msdb..suspect_pages)
AND NAME NOT IN ( 'master', 'msdb', 'tempdb', 'model',
'distribution' )
AND state_desc = 'ONLINE'
AND source_database_id IS NULL
AND state_desc = 'ONLINE'
AND is_read_only = 0;
OPEN file_cursor
FETCH NEXT FROM file_cursor
INTO @vcDatabaseName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @dtMaxBackupTime = (SELECT MAX(backup_finish_date)FROM msdb.dbo.backupset WHERE database_name = @vcDatabaseName AND [type] = 'D')
SET @dtMinBackupTime = (SELECT MIN(backup_finish_date)FROM msdb.dbo.backupset WHERE database_name = @vcDatabaseName AND [type] = 'D')
SET @iNoSamples =
DATEDIFF
(
dd
,@dtMinBackupTime
,@dtMaxBackupTime
)
SET @nMaxBackupSize= (SELECT backup_size FROM msdb.dbo.backupset WHERE database_name = @vcDatabaseName AND [type] = 'D' AND backup_finish_date = @dtMaxBackupTime)
SET @nMinBackupSize= (SELECT backup_size FROM msdb.dbo.backupset WHERE database_name = @vcDatabaseName AND [type] = 'D' AND backup_finish_date = @dtMinBackupTime)
SET @iMaxBackupID= (SELECT MAX(backup_set_id) FROM msdb.dbo.backupset WHERE database_name = @vcDatabaseName AND [type] = 'D' AND backup_finish_date = @dtMaxBackupTime)
SET @iMinBackupID= (SELECT MAX(backup_set_id) FROM msdb.dbo.backupset WHERE database_name = @vcDatabaseName AND [type] = 'D' AND backup_finish_date = @dtMinBackupTime)
SET @nMaxLogSize= (SELECT ((CAST((SUM(file_size)) AS DECIMAL(20,3))) / 1048576) FROM msdb.dbo.backupfileWHERE backup_set_id = @iMaxBackupID AND file_type = 'L')
SET @nMinLogSize= (SELECT ((CAST((SUM(file_size)) AS DECIMAL(20,3))) / 1048576) FROM msdb.dbo.backupfileWHERE backup_set_id = @iMinBackupID AND file_type = 'L')
SET @nMaxDataSize= (SELECT ((CAST((SUM(file_size)) AS DECIMAL(20,3))) / 1048576) FROM msdb.dbo.backupfileWHERE backup_set_id = @iMaxBackupID AND file_type = 'D')
SET @nMinDataSize= (SELECT ((CAST((SUM(file_size)) AS DECIMAL(20,3))) / 1048576) FROM msdb.dbo.backupfileWHERE backup_set_id = @iMinBackupID AND file_type = 'D')
EXEC ('
INSERT INTO ##tbl_DataSize
SELECT CAST((SUM(size)) as DECIMAL(20,3)) FROM ['+@vcDatabaseName+'].dbo.sysfiles'
)
INSERT INTO #tbl_GrowthData
SELECT
@vcDatabaseName DatabaseName
,@iNoSamples NO_OF_SAMPLE_DAYS
,@nMaxDataSize
,@nMaxLogSize
,@nMaxBackupSize / 1048576
,((size * 8192) / 1048576) TotalDBSpaceUsed
,@nMaxDataSize - @nMinDataSize
,@nMaxLogSize - @nMinLogSize
,(((@nMaxDataSize + @nMaxLogSize) - (@nMinDataSize+ @nMinLogSize)) / (@nMinDataSize+ @nMinLogSize)) * 100.00 GrowthPercentage
--growth percentage is calculated based upon the original data size, before the growth.
FROM ##tbl_DataSize
FETCH NEXT FROM file_cursor
INTO @vcDatabaseName
TRUNCATE TABLE ##tbl_DataSize
END
CLOSE file_cursor
DEALLOCATE file_cursor
DECLARE @columnList VARCHAR(max)
DECLARE @sqlCommand VARCHAR(max)
DECLARE @count INT
SET @columnList = 'DatabaseName, NO_OF_SAMPLE_DAYS, BackupSizeMB, TotalDBSpaceMB, DataGrowth, LogGrowth, GrowthPercentage'
-- Include Columns of your interest.
SET @value = 20 -- Growth >=20 %
SET @sqlCommand = 'SELECT ' + @columnList + ' FROM #tbl_GrowthData WHERE ISNULL(convert(varchar(100), GrowthPercentage),0) >='
+ convert(varchar(100),@value) + 'ORDER BY DatabaseName'
--SELECT @sqlcommand
--EXEC (@sqlCommand)
SELECT * FROM #tbl_GrowthData
SET @count = (SELECT Count(*) FROM #tbl_GrowthData
WHERE convert(INT,Growthpercentage) >= @value)
--Print @count
--DECLARE @body VARCHAR(max)
DECLARE @subject VARCHAR(max)
DECLARE @profile_name VARCHAR(max)
DECLARE @recipients VARCHAR(max)
IF @count > =0
BEGIN
SELECT @subject = 'CURRENTLY THERE ARE DBS IN THE SERVER WHICH HAS/HAVE GROWTH PERCENTAGE >= 20%: ' + ' '+ Substring(@@servername, 1, 20)
-- + ' ' + 'in Database: ' + @DBNAME;
declare @body1 nvarchar(max)
set @body1 = N'<H1> <Font Color = "red"> DB GROWTH RATE CHECK: </font> </H1>' +
N'<body><H3> <Font Color = "red"> Server: ' + @@servername + '</H3></font>' +
N'<H5> <Font Color = "Blue">Executed ON: ' + ' '+ CONVERT( VARCHAR( 20 ), GETDATE(), 113 ) + + '</H5></font>' + ' '+ '
'+
N'<table border="1">' +
N'<tr> <th>DatabaseName</th>
<th>NO_OF_SAMPLE_Days</th>
<th>DataGrowth</th>
<th>LogGrowth</th>
<th>GrowthPercentage</th></tr>'
+
CAST ( ( SELECT td = DatabaseName, '',
td = ISNULL(CONVERT(varchar(20),NO_OF_SAMPLE_Days), 'NO BKP JOB RAN'), '',
td = ISNULL(CONVERT(varchar(20),DataGrowth), 'NO DATA FOUND'), '',
td = ISNULL(CONVERT(varchar(20),LogGrowth), 'NO DATA FOUND'), '',
td = ISNULL(CONVERT(varchar(20),GrowthPercentage), 'NO DATA FOUND'), ''
FROM #tbl_GrowthData
WHERE ISNULL(CONVERT(varchar(20),GrowthPercentage), '0') >= Convert(varchar(10),@value)
ORDER By DatabaseName desc
FOR XML PATH('tr'), Elements
) AS NVARCHAR(MAX) )
+
N'</table>' ;
IF (@dbmail_profile IS NOT NULL) OR (@dbmail_recipient IS NOT NULL)
BEGIN
-- Sending Email to Recipients.
EXEC msdb.dbo.Sp_send_dbmail
@profile_name = @dbmail_profile,
@recipients = @dbmail_recipient,
@subject = @subject,
@body = @body1,
@body_format = 'HTML',
@query = 'SELECT * from ##tbl_DataSize',
@importance = 'HIGH'
END
/* Test Queued Email. ...
Query 1 : SELECT [profile_id]
,[name]
,[description]
,[last_mod_datetime]
,[last_mod_user]
,'EXEC msdb.dbo.sp_send_dbmail
@profile_name = ''' + name + ''',
@recipients = '''',
@subject = ''Test'',
@body = ''Message'',
@body_format = ''HTML'';' AS TestSQL
FROM [msdb].[dbo].[sysmail_profile]
Query 2: EXEC MSDB..sysmail_help_queue_sp @queue_type = 'Mail' ;
Query 3: SELECT * FROM msdb..sysmail_mailitems WHERE sent_date > DATEADD(DAY, -1,GETDATE())
*/
--EXEC msdb.dbo.sysmail_add_profile_sp
-- @profile_name = @dbmail_profile,
-- @recipients = @dbmail_profile,
-- @body = @body,
-- @subject = @subject
DROP TABLE ##tbl_DataSize
DROP TABLE #tbl_GrowthData
SET NOCOUNT OFF
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
Thanks.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply