Schlagwort: Email

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