1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32
| declare @i as int
declare @xml nvarchar(max)
declare @body nvarchar(max)
SET @xml = (SELECT top 10 [session_id] AS [td],
[login_name] AS [td],
DATEDIFF(mi, start_time, collection_time) AS [td],
case when blocking_session_id is null then '' else blocking_session_id end AS [td],
[HOST_NAME] AS [td],
database_name AS [td],
[program_name] AS [td],
collection_time AS [td],
CAST(CASE WHEN CAST(REPLACE(tempdb_allocations, ',','') AS FLOAT) > 2000000 THEN (tempdb_allocations) ELSE tempdb_allocations END AS XML) AS [td bgcolor=red],
tempdb_current AS [td]
FROM [Administrator].[securite].[WhoIsActive_TheLog]
WHERE ((DATEDIFF(mi, start_time, collection_time) > 10 AND login_name <> 'c8') OR(DATEDIFF(mi, start_time, collection_time) > 30 AND login_name = 'c8'))
FOR XML raw('tr'), ELEMENTS )
SET @body ='<html><body><H3>Un process roule depuis plus de 10 minutes sur SQLCLUSTER1\INST01
</H3>
<table border = 1>
<tr>
<th> Session_id </th> <th> Login_name </th> <th> Minutes </th> <th> blocking_session_id </th> <th> HOST_NAME </th> <th> database_name </th> <th> program_name </th> <th> collection_time </th><th> tempdb_allocations </th><th> tempdb_current </th>
</tr>'
SET @body = @body + @xml +'</table></body></html>'
Set @Body = Replace(@Body, '_x0020_', space(1))
Set @Body = Replace(@Body, '_x003D_', '=') |
Partager