January 19, 2016 at 11:28 am
Hi All,
I am trying to add more than 3 tables results in the HTML body. THe code is below...
DECLARE @MinExecutions int;
SET @MinExecutions = 5
IF OBJECT_ID('tempdb..#Results') IS NOT NULL
DROP TABLE #Results
SELECT EQS.total_worker_time AS TotalWorkerTime
,EQS.total_logical_reads + EQS.total_logical_writes AS TotalLogicalIO
,EQS.execution_count As ExeCnt
,EQS.last_execution_time AS LastUsage
,EQS.total_worker_time / EQS.execution_count as AvgCPUTimeMiS
,(EQS.total_logical_reads + EQS.total_logical_writes) / EQS.execution_count
AS AvgLogicalIO
,DB.name AS DatabaseName
,SUBSTRING(EST.text
,1 + EQS.statement_start_offset / 2
,(CASE WHEN EQS.statement_end_offset = -1
THEN LEN(convert(nvarchar(max), EST.text)) * 2
ELSE EQS.statement_end_offset END
- EQS.statement_start_offset) / 2
) AS SqlStatement
INTO #Results
-- Optional with Query plan; remove comment to show, but then the query takes !!much longer time!!
--,EQP.[query_plan] AS [QueryPlan]
FROM sys.dm_exec_query_stats AS EQS
CROSS APPLY sys.dm_exec_sql_text(EQS.sql_handle) AS EST
CROSS APPLY sys.dm_exec_query_plan(EQS.plan_handle) AS EQP
LEFT JOIN sys.databases AS DB
ON EST.dbid = DB.database_id
WHERE EQS.execution_count > @MinExecutions
AND EQS.last_execution_time > DATEADD(MONTH, -1, GETDATE())
AND DB.name NOT IN ('MASTER','MSDB','TEMPDB','MODEL')
AND EQS.total_worker_time > 1000000
ORDER BY AvgLogicalIo DESC
,AvgCPUTimeMiS DESC
-----------------------------------------
IF OBJECT_ID('tempdb..#MemResults') IS NOT NULL
DROP TABLE #MemResults
-- Note: querying sys.dm_os_buffer_descriptors
-- requires the VIEW_SERVER_STATE permission.
DECLARE @total_buffer INT;
SELECT @total_buffer = cntr_value
FROM sys.dm_os_performance_counters
WHERE RTRIM([object_name]) LIKE '%Buffer Manager'
AND counter_name = 'Database Pages';
;WITH src AS
(
SELECT
database_id, db_buffer_pages = COUNT_BIG(*)
FROM sys.dm_os_buffer_descriptors
--WHERE database_id BETWEEN 5 AND 32766
GROUP BY database_id
)
SELECT
[db_name1] = CASE [database_id] WHEN 32767
THEN 'Resource DB'
ELSE DB_NAME([database_id]) END,
db_buffer_pages,
db_buffer_MB = db_buffer_pages / 128,
db_buffer_percent = CONVERT(DECIMAL(6,3), db_buffer_pages * 100.0 / @total_buffer) into #MemResults
FROM src
WHERE DB_NAME([database_id]) NOT IN ('MASTER','MODEL','MSDB','TEMPDB')
--AND db_buffer_percent > 40
AND db_buffer_pages > = 100000
ORDER BY db_buffer_MB DESC;
DECLARE @tableHTMLA VARCHAR(MAX) ;
iF EXISTS (SELECT TOP 1 * FROM #MemResults)
BEGIN
SET @tableHTMLA =
N'<H1>Memory Usage</H1>' +
N'<table border="1">' +
N'<tr><th>DB Name</th>' +
N'<th>DB Buffer Pages</th>' +
N'<th>DB Buffer MB</th> </tr>' +
N'<th>DB Buffer Percent</th> </tr>' +
CAST ( ( SELECT td = db_name1, ' ',
td = db_buffer_pages, ' ',
td = db_buffer_MB, ' ' ,
td = db_buffer_percent, ' '
FROM #MemResults
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
END
ELSE
SET @tableHTMLA = 'No Results for Memory Usage'
DECLARE @tableHTMLB VARCHAR(MAX) ;
iF EXISTS (SELECT TOP 1 * FROM #Results)
BEGIN
SET @tableHTMLB =
N'<H1>Expensive Queries</H1>' +
N'<table border="1">' +
N'<tr><th>Database Name</th>' +
N'<th>Total Worker Time</th>' +
N'<th>Total Logical IO</th>' +
N'<th>Last Usage</th> ' +
N'<th>Avg CPU Time MiS</th>' +
N'<th>Avg Logical IO</th> </tr>' +
CAST ( ( SELECT td = DatabaseName, ' ' ,
td = TotalWorkerTime, ' ',
td = TotalLogicalIO, ' ',
td = LastUsage, ' ' ,
td = AvgCPUTimeMiS, ' ' ,
td = AvgLogicalIO, ' '
FROM #Results
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
END
ELSE
SET @tableHTMLB = 'No Results for Expensive Queries'
---------------------------------------------
IF OBJECT_ID('tempdb..#DBGrowth') IS NOT NULL
DROP TABLE #DBGrowth
-- Transact-SQL script to analyse the database size growth using backup history.
DECLARE @endDate datetime, @months smallint;
SET @endDate = GetDate(); -- Include in the statistic all backups from today
SET @months = 2; -- back to the last 6 months.
;WITH HIST AS
(SELECT BS.database_name AS DatabaseName
,YEAR(BS.backup_start_date) * 100
+ MONTH(BS.backup_start_date) AS YearMonth
,CONVERT(numeric(10, 1), MIN(BF.file_size / 1048576.0)) AS MinSizeMB
,CONVERT(numeric(10, 1), MAX(BF.file_size / 1048576.0)) AS MaxSizeMB
,CONVERT(numeric(10, 1), AVG(BF.file_size / 1048576.0)) AS AvgSizeMB
FROM msdb.dbo.backupset as BS
INNER JOIN
msdb.dbo.backupfile AS BF
ON BS.backup_set_id = BF.backup_set_id
WHERE NOT BS.database_name IN
('master', 'msdb', 'model', 'tempdb')
AND BF.file_type = 'D'
AND BS.backup_start_date BETWEEN DATEADD(mm, - @months, @endDate) AND @endDate
GROUP BY BS.database_name
,YEAR(BS.backup_start_date)
,MONTH(BS.backup_start_date))
SELECT MAIN.DatabaseName
,MAIN.YearMonth
,MAIN.MinSizeMB
,MAIN.MaxSizeMB
,MAIN.AvgSizeMB
,MAIN.AvgSizeMB
- (SELECT TOP 1 SUB.AvgSizeMB
FROM HIST AS SUB
WHERE SUB.DatabaseName = MAIN.DatabaseName
AND SUB.YearMonth < MAIN.YearMonth
ORDER BY SUB.YearMonth DESC) AS GrowthMB INTO #DBGROWTH
FROM HIST AS MAIN
ORDER BY MAIN.DatabaseName
,MAIN.YearMonth
DECLARE @tableHTMLC VARCHAR(MAX) ;
iF EXISTS (SELECT TOP 1 * FROM #DBGROWTH)
BEGIN
SET @tableHTMLC =
N'<H1>Database Growth</H1>' +
N'<table border="1">' +
N'<tr><th>Database Name</th>' +
N'<th>Year Month</th>' +
N'<th>Min Size MB</th>' +
N'<th>Max Size MB</th>' +
N'<th>Avg Size MB</th>' +
N'<th>Growth MB</th> </tr>' +
CAST ( ( SELECT td = DatabaseName, ' ',
td = YearMonth, ' ',
td = MinSizeMB, ' ' ,
td = MaxSizeMB, ' ' ,
td = AvgSizeMB, ' ' ,
td = AvgSizeMB, ' '
FROM #DBGROWTH
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
END
ELSE
SET @tableHTMLC = ' No Results for DB Growth'
---------------------------------------------
use msdb
go
--Alter Procedure usp_getErrorlog as
IF OBJECT_ID('tempdb..#errors') IS NOT NULL
DROP TABLE #errors
IF OBJECT_ID('tempdb..#temp_text2') IS NOT NULL
DROP TABLE #temp_text2
CREATE TABLE #errors (
LogDate datetime,
ProcessInfo varchar(20),
SQL_Log_Error nvarchar(2048)
)
CREATE CLUSTERED INDEX idx_msg ON #errors(LogDate)
create table #temp_text2 (
id int NOT NULL IDENTITY (1, 1),
LogDate datetime NULL,
SQL_Log_Error nvarchar(2048))
-- Read the Current Active SQL Error Log into the temporary error table
INSERT #errors EXEC master.dbo.xp_readerrorlog
--select * from #Errors
SET NOCOUNT ON
INSERT INTO #temp_text2 (LogDate,SQL_Log_Error)
SELECT LogDate,SQL_Log_Error FROM #errors
WHERE (
SQL_Log_Error LIKE '%Could not allocate space%' COLLATE SQL_Latin1_General_CP1_CI_AS OR
SQL_Log_Error LIKE '%Could not allocate a new page%' COLLATE SQL_Latin1_General_CP1_CI_AS OR
SQL_Log_Error LIKE '%The transaction log for database%' COLLATE SQL_Latin1_General_CP1_CI_AS OR
SQL_Log_Error LIKE '%operating system error%' COLLATE SQL_Latin1_General_CP1_CI_AS OR
SQL_Log_Error LIKE '%internal error%' COLLATE SQL_Latin1_General_CP1_CI_AS OR
SQL_Log_Error LIKE '%BACKUP LOG WITH TRUNCATE_ONLY%' COLLATE SQL_Latin1_General_CP1_CI_AS OR
SQL_Log_Error LIKE '%torn page%' COLLATE SQL_Latin1_General_CP1_CI_AS OR
SQL_Log_Error LIKE '%fatal Error%' COLLATE SQL_Latin1_General_CP1_CI_AS OR
SQL_Log_Error LIKE '%IO requests taking longer%' COLLATE SQL_Latin1_General_CP1_CI_AS OR
SQL_Log_Error LIKE '%taking longer%' COLLATE SQL_Latin1_General_CP1_CI_AS OR
SQL_Log_Error LIKE '%offline%' COLLATE SQL_Latin1_General_CP1_CI_AS OR
SQL_Log_Error LIKE '%Write failure on backup device%' COLLATE SQL_Latin1_General_CP1_CI_AS OR
SQL_Log_Error LIKE '%Write on%' COLLATE SQL_Latin1_General_CP1_CI_AS OR
SQL_Log_Error LIKE '%deadlock%' COLLATE SQL_Latin1_General_CP1_CI_AS OR
SQL_Log_Error LIKE '%single user%' COLLATE SQL_Latin1_General_CP1_CI_AS OR
SQL_Log_Error LIKE '%autogrow of file%' COLLATE SQL_Latin1_General_CP1_CI_AS or
SQL_Log_Error LIKE '%BackupMedium::ReportIoError%' COLLATE SQL_Latin1_General_CP1_CI_AS or
SQL_Log_Error LIKE '%SQL Server has encountered%' COLLATE SQL_Latin1_General_CP1_CI_AS or
SQL_Log_Error LIKE '%BackupDiskFile::%' COLLATE SQL_Latin1_General_CP1_CI_AS
)
--Additional exceptions to keep the event log messages themselves from getting cought as errors.
--)
--select * from #temp_text2
DECLARE @tableHTMLD VARCHAR(MAX) ;
iF EXISTS (SELECT TOP 1 * FROM #temp_text2)
BEGIN
SET @tableHTMLD = N'<H1>Critical Error Log</H1>' +
N'<table border="1">' +
N'<tr><th>Log Date</th>' +
N'<th>SQL Log Error</th> </tr>' +
CAST ( ( SELECT td = LogDate, ' ' ,
td = SQL_Log_Error, ' '
FROM #temp_text2
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
END
ELSE
SET @tableHTMLD = 'No Results for critical error log'
--drop table #errors
--drop table #temp_text2
----------------------------------------------
DECLARE @body2 NVARCHAR(MAX) ;
SET @body2 = @tableHTMLA + @tableHTMLB + @tableHTMLC +@tableHTMLD + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) + 'SQL Server Mail Sent By Database Team' + '</TABLE></BODY></HTML>' ;
EXEC msdb.dbo.sp_send_dbmail
@profile_name ='DBA_Alerts',
@recipients='abc@gmail.com',
@subject = 'Health Check Report - XXXX',
@body = @body2 ,
@body_format = 'HTML' ;
With the above code, I get the following error
Msg 137, Level 15, State 2, Line 78
Must declare the scalar variable "@tableHTMLA".
The same error doesn't come, when I remove the @tableHTMLD part from the above code.
Please let me know if there is any restriction on the number of tables to be appended in the HTML body section.
Thanks much in advance,
Priya
Regards
Priya
January 19, 2016 at 11:40 am
You need to remove the GO after 'use MSDB'.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 20, 2016 at 2:31 am
You are amazing... Thank you so much.
Regards
Priya
January 20, 2016 at 6:12 am
🙂 glad I could help
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply