Sperren automatisch erkennen und melden als E-Mail:
/*
Erkennen von Sperren und Emails wenn Session dabei warten muessen
- -
Changes:
2015-09-24 11:47 ODFR: Die CSV-Datei kleiner gemacht + den echten Zeitpunkt einer Sperre die behindert!
2015-09-29 12:34 ODFR: Kommentare sollen nur Slash+Stern sein, sonst Gefahr auf fehlerhafter Funktionalit‰t
2015-09-30 13:22 ODFR: Auch Backup-Jobs m¸ssen ggf. auch dargestellt werden (diese sind keine Transactions)
*/
/*Vorbereitungen:*/
DECLARE @iMinuten int;
SET @iMinuten = 2;
DECLARE @iMinutenSeitSperre int;
DECLARE @cQuery_attachment_filename varchar(64);
SET @cQuery_attachment_filename='Sperren_'+replace(replace(replace(convert(char(16), Getdate(), 121),' ','_'),'-',''),':','')+'.csv';
DECLARE @cQuery_result_separator char(1);
SET @cQuery_result_separator=CHAR(9);
/*Die Query vorbereiten, die sowohl zum Bewerten von Sperren, als auch ggf. in einer Email benutzt wird:*/
DECLARE @cQuery varchar(8000);
SET @cQuery = 'SELECT DISTINCT L.request_session_id AS request_session_id, /* Anfragender System Process Identifier */
t2.blocking_session_id as blocking_session_id, /*Blockierender System Process Identifier */
/*request_owner_guid AS UOW,*/ /* Identifiziert die Arbeitseinheits-ID (Unit of Work, UOW) verteilter Transaktionen */
DB_NAME(L.resource_database_id) AS DatabaseName, /* Databasename */
O.Name AS LockedObjectName, /* Object name */
P.object_id AS LockedObjectId, /* Eindeutige Object identification number */
L.resource_type AS LockedResource, /* Resource which is locked: DATABASE, FILE, OBJECT, PAGE, KEY, EXTENT, RID, APPLICATION, METADATA, HOBT, ALLOCATION_UNIT. */
L.request_mode AS LockType, /* Lock Type: Exclusive locks (X), Intent locks (I), Shared locks (S), Update locks (U), Schema locks (Sch) */
/*L.resource_description AS LockDescription,*/ /* LockDescription */
replace(replace(replace( ST.text ,CHAR(9),'' '') ,CHAR(13),''/*CR*/'') ,CHAR(10),''/LF*/'') AS SqlStatementText, /* SQL Statement */
ES.login_name AS LoginName, /* Loginname mit dem der User an der DB angemeldet ist */
ES.host_name AS HostName, /* Hostname des verbunden clients */
TST.is_user_transaction as IsUserTransaction, /* 1 = Die Transaktion wurde von einer Benutzeranforderung initiiert. 0 = Systemtransaktion. */
AT.name as TransactionName, /* Transaktionsname */
AT.transaction_begin_time as TransactionStartTime,/* Uhrzeit des Transaktionsbeginns. */
coalesce(AT.transaction_begin_time, ES.last_request_start_time) as SQLJobStartTime, /* Uhrzeit des Beginns des laufenden SQLs. */
CN.auth_scheme as AuthenticationMethod /* Login Methode SQL / Windows Authentifizierung */
FROM sys.dm_tran_locks L
left JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id
left JOIN sys.objects O ON O.object_id = P.object_id
left JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
left JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id
left JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id
left JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id
CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST
left JOIN sys.dm_os_waiting_tasks as t2
ON L.lock_owner_address = t2.resource_address
WHERE 1=1
/*and resource_database_id = db_id(''MYDATABASE'')*/ /*nur Locks einer bestimmten Datenbank betrachten*/
/*and L.request_mode in (''X'', ''IX'') */ /* nur Exklusive Locks */
and (t2.blocking_session_id IS NOT NULL OR L.request_mode like ''%X%'' or L.request_mode = ''NULL'' or L.request_mode is NULL) /*mˆgliche Stˆrer und klare Opfer darstellen*/
ORDER BY L.request_session_id';
/*Code zum Bewerten von Sperren:*/
Declare @dTransactionStartTime Datetime;
Declare @tTable Table (
request_session_id int, /* Anfragender System Process Identifier */
blocking_session_id int, /*Blockierender System Process Identifier */
DatabaseName nvarchar(255), /* Databasename */
LockedObjectName nvarchar(255), /* Object name */
LockedObjectId int, /* Eindeutige Object identification number */
LockedResource nvarchar(255), /* Resource which is locked: DATABASE, FILE, OBJECT, PAGE, KEY, EXTENT, RID, APPLICATION, METADATA, HOBT, ALLOCATION_UNIT. */
LockType nvarchar(10), /* Lock Type: Exclusive locks (X), Intent locks (I), Shared locks (S), Update locks (U), Schema locks (Sch) */
SqlStatementText nvarchar(max), /* SQL Statement */
LoginName nvarchar(255), /* Loginname mit dem der User an der DB angemeldet ist */
HostName nvarchar(255), /* Hostname des verbunden clients */
IsUserTransaction int, /* 1 = Die Transaktion wurde von einer Benutzeranforderung initiiert. 0 = Systemtransaktion. */
TransactionName nvarchar(255), /* Transaktionsname */
TransactionStartTime datetime,/* Uhrzeit des Transaktionsbeginns. */
SQLJobStartTime datetime,/* Uhrzeit des Transaktionsbeginns. */
AuthenticationMethod nvarchar(255) /* Login Methode SQL / Windows Authentifizierung */
)
/*Einmal die Eckwerte aller aktuellen Session einlesen, die entweder sperren oder selber warten muessen*/
Insert into @tTable
exec ( @cQuery )
/* Aelteste Sperre, egal ob ein echtes Warten vorliegt oder nicht */
/*
SELECT TOP 1 @dTransactionStartTime=AT.transaction_begin_time
FROM sys.dm_tran_locks L
left JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id
left JOIN sys.objects O ON O.object_id = P.object_id
left JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
left JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id
left JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id
WHERE AT.transaction_begin_time IS NOT NULL
and L.request_mode NOT in ('S','IS')
ORDER BY AT.transaction_begin_time ASC;
*/
/* Nur Sperren beachten, wenn ein andere Session wartet */
/*
Select @dTransactionStartTime=Min(AT.transaction_begin_time)
FROM sys.dm_tran_locks L
left JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
left JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id
left JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id
left JOIN sys.dm_os_waiting_tasks as t2 ON L.lock_owner_address = t2.resource_address
having count(t2.blocking_session_id) > 0
*/
/*Tabelle definieren nur für sperrende und abhaengige Sessions:*/
Declare @tTable2 Table (
request_session_id int, /* Anfragender System Process Identifier */
blocking_session_id int, /*Blockierender System Process Identifier */
TransactionStartTime datetime /* Uhrzeit des Transaktionsbeginns. */
)
/*Und nun die sperrende und abhaengige Sessions aus vorhin befuellten Tabelle bestimmen:*/
;With hLocks (request_session_id, blocking_session_id, TransactionStartTime)
as (
Select request_session_id, blocking_session_id, SQLJobStartTime
From @tTable t1
where blocking_session_id is NOT NULL
union all
Select t2.request_session_id, t2.blocking_session_id, t2.SQLJobStartTime
from (
Select request_session_id, blocking_session_id, SQLJobStartTime
From @tTable
) t2 inner join hLocks on t2.request_session_id = hLocks.blocking_session_id
)
Insert into @tTable2
Select * from hLocks
Select @dTransactionStartTime = Min(TransactionStartTime) from @tTable2
/*
Select * from @tTable
Select @dTransactionStartTime
*/
/*Nun die Ergbnisse bewerten:*/
SET @iMinutenSeitSperre=coalesce(datediff(mi,@dTransactionStartTime,GetDate()),0);
DECLARE @cSubject varchar(128);
SET @cSubject = 'SPERRE SEIT MEHR ALS '+cast(@iMinutenSeitSperre as varchar(10))+' Minuten';
DECLARE @cBody varchar(512);
SET @cBody = 'Die Sperre fing an um '+convert(char(16), @dTransactionStartTime, 121)+' und somit vor '+cast(@iMinutenSeitSperre as varchar(10))+' Minuten.'
+CHAR(13)+'Es könnte in '+cast((60 - (@iMinutenSeitSperre % 60) ) as varchar(10))+' Minuten eine neue Email versendet werden,'
+CHAR(13)+'wenn das Problem bis dahin nicht gelöst ist!';
/*Die erste Email darf natürlich kommen und erst dann nur alle volle Stunden*/
/*es können derzeit (leider) bis 3 Email versendet werden */
If @iMinutenSeitSperre > 0
If (@iMinutenSeitSperre % 60) between @iMinuten and (@iMinuten * 2)
BEGIN
/*print 'SPERRE SEIT MEHR ALS '+cast(@iMinuten as varchar(10))+' Minuten'*/
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQLService'
,@recipients = 'admins@emailserver.local'
,@copy_recipients = 'user1@emailserver.local'
/*,@blind_copy_recipients = 'private1@emailserver.local'*/
,@subject = @cSubject
,@body = @cBody
,@query = @cQuery
,@query_result_header = 1
,@query_result_separator = @cQuery_result_separator
,@query_no_truncate = 1
,@append_query_error = 1
,@query_result_width = 32767
,@query_attachment_filename = @cQuery_attachment_filename /*'Sperren.csv'*/
,@attach_query_result_as_file = 1 ;
END
/*
ELSE
BEGIN
print @cBody;
END
Else
print 'Keine Sperren.'
*/
Voraussetzungen: Database Mail ist aktiviert und eingerichtet. Es muss das „Profil“ und natürlich die Ziel Emailadressen angepasst werden.
Ebenso kann es sein, dass im Database Mail die Beschränkung der Attachment-Größe etwas gelockert wird (Systemparameter in Database Mail).
Link: https://docs.microsoft.com/de-de/sql/relational-databases/database-mail/configure-database-mail#SystemParameters
