Etiket arşivi: SQL open tran nedir

MSSQL Open Tran Bilgilerini Mail Göndermek

MSSQL de açık transactionları istediğiniz aralıklarla mail almak için aşağıdaki scripti job olarak set edebilirsiniz. Script Html formatında mail atıyor.

BEGIN
DECLARE @htm NVARCHAR(MAX);
SET @htm =
N'<H1>Acik Transactionlar</H1>' +
N'<table border = 1>' + 
N'<tr bgcolor=lightblue><th>Spid</th><th>DbName</th>' +
N'<th>HostName</th>' +
N'<th>Status</th><th>LoginName</th><th>loginDate</th><th>LastBatchDate</th></tr>' +
CAST ( ( SELECT 
spid AS [td], '', 
db_name(dbid) AS [td], '',
hostname AS [td], '',
status AS [td], '',
loginame AS [td], '',
/* CONVERT (char (8) ,login_time , 112) AS [td], '' */
CAST(login_time AS smalldatetime) AS [td], '',
CAST(last_batch AS smalldatetime) AS [td], ''
from sys.sysprocesses
where open_tran>0 and lastwaittype not in ('BROKER_RECEIVE_WAITFOR')
/* and Datediff (hour,last_batch , getdate ()) >= 1 */
order by login_time asc 
FOR XML PATH('tr'), TYPE 
) AS NVARCHAR(MAX) ) +
N'</table>';
/* select @htm */
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SMTP',
@recipients = 'omer@omeracar.com.tr',
@subject = 'Open Tran Mail',
@body = @htm,
@body_format = 'HTML'; 
END

Open tran ları kendiniz kontrol etmek isterseniz;

Use Databaseadi
GO
DBCC OPENTRAN

yazarak kontrol edebilirsiniz..