April 30, 2015 at 1:34 pm
Hi,
I am trying to send an email alert when the dbgrowth >=20%
Error 8115, Severity 16, State 6, Procedure usp_growthcheck, Line 144
Arithmetic overflow error converting nvarchar to data type numeric.
CAn you suggest where do I make the mistake?
CREATE TABLE #capture_data
(
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 @tableHTML VARCHAR(MAX);
SET @tableHTML =
N'<H1> <Font Color = "red"> DB GROWTH RATE CHECK: </font> </H1>' +
N'<body><H3> <Font Color = "red"> Server: ' + @@servername + '</H3></font>' +
N'<table border="1">' +
N'<tr> <th>DatabaseName</th>
<th>NO_OF_SAMPLE_DAYS</th>' +
N'<th>BackupSizeMB</th>
<th>TotalDBSpaceMB</th>
<th>DataGrowth</th>
<th>LogGrowth</th>
<th>GrowthPercentage</th>' +
CAST ( ( SELECT td = DatabaseName, '',
td = ISNULL(NO_OF_SAMPLE_DAYS,'NO Backup Job Ran'), '',
td = ISNULL(BackupSizeMB,'NO Data Found'), '',
td = ISNULL(TotalDBSpaceMB,'NO Data Found'), '',
td = ISNULL(DataGrowth,'NO Data Found'), '',
td = ISNULL(LogGrowth,'NO Data Found'), '',
td = CAST(ISNULL(GrowthPercentage,0) AS nvarchar(max))
FROM #capture_data
WHERE GrowthPercentage >= convert(nvarchar(max),@value)
ORDER By DatabaseName
FOR XML PATH('tr'), ELEMENTS
) AS NVARCHAR(MAX) ) +
N'</table>' ;
Thanks.
April 30, 2015 at 1:40 pm
With all of these statements...
ISNULL(NO_OF_SAMPLE_DAYS,'NO Backup Job Ran'), '',
td = ISNULL(BackupSizeMB,'NO Data Found'), '',
td = ISNULL(TotalDBSpaceMB,'NO Data Found'), '',
td = ISNULL(DataGrowth,'NO Data Found'), '',
td = ISNULL(LogGrowth,'NO Data Found'), '',
Take the first one for example. NO_OF_SAMPLE_DAYS is Decimal. But if it is Null, you want it to be the string 'NO Backup Job Ran'.
-SQLBill
April 30, 2015 at 2:23 pm
You're comparing a nvarchar(max) to a decimal.
For 20%, I assume that you need 0.2
April 30, 2015 at 2:26 pm
SQLBill (4/30/2015)
With all of these statements...ISNULL(NO_OF_SAMPLE_DAYS,'NO Backup Job Ran'), '',
td = ISNULL(BackupSizeMB,'NO Data Found'), '',
td = ISNULL(TotalDBSpaceMB,'NO Data Found'), '',
td = ISNULL(DataGrowth,'NO Data Found'), '',
td = ISNULL(LogGrowth,'NO Data Found'), '',
Take the first one for example. NO_OF_SAMPLE_DAYS is Decimal. But if it is Null, you want it to be the string 'NO Backup Job Ran'.
-SQLBill
Oh yeah, that too. 😀
To prevent that error, you could convert the columns inside the ISNULL(). e.g. ISNULL(CONVERT( varchar(20), NO_OF_SAMPLE_DAYS),'NO Backup Job Ran').
April 30, 2015 at 2:50 pm
It's stuck in the conversion...Tried a lot, no luck. 🙁 Confused in the conversion..
NO Error....Only thing is that my mail alert does not have the table/with the details in it. Missing. 🙁
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>' +
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), 0.00), ''
FROM #tbl_GrowthData
WHERE convert(INT,GrowthPercentage) >= @value
ORDER By DatabaseName desc
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
IF (@dbmail_profile IS NOT NULL) OR (@dbmail_recipient IS NOT NULL)
BEGIN
PRINT 1
-- 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 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply