Link to home
Start Free TrialLog in
Avatar of Support_38
Support_38

asked on

kill process lock Sql server

Good evening

Anyone would have some script that kills a lock process after a period of time and send email with headblock?

thank you
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Hi Alex,

A very good evening-

Please get the script from below-

1. It gets the required spids
2. then emails the list
3. then it kills the spids <everything is dynamic>

Ref - http://www.connectsql.com/2012/09/sql-server-automatically-detect-and.html 

/******************************************
Script By: Aasim Abdullah 
For : http://connectsql.blogspot.com
Purpose: To detect long running sessions,
			send complete information through mail about such sessions
			and killing session, which are acceding given limit of execution time.
******************************************/

---BusyProcess Detection
SET NOCOUNT ON

-- Table variable to hold InputBuffer data
DECLARE @Inputbuffer TABLE
    (
      EventType NVARCHAR(30) NULL,
      Parameters INT NULL,
      EventInfo NVARCHAR(4000) NULL
    )
-- Table variable to hold running processes information
DECLARE @BusyProcess TABLE
    (
      SPID INT,
      Status VARCHAR(100),
      Login VARCHAR(100),
      HostName VARCHAR(100),
      DBName VARCHAR(100),
      Command VARCHAR(200),
      CPUTime INT,
      DiskIO INT,
      LastBatch DATETIME,
      ProgramName VARCHAR(200),
      EventInfo NVARCHAR(4000), -- extra column to hold actual stored procedure or batch call text
      EventTime INT   -- time in minutes, a process is running
    )
-- Insert all running processes information to table variable
INSERT  @BusyProcess
        ( SPID, Status, Login, HostName,  DBName, Command, CPUTime,
          DiskIO, LastBatch, ProgramName )
    
        SELECT spid,status,loginame,hostname,DB_NAME(dbid),cmd,cpu,physical_io,last_batch,program_name 
        FROM SYS.SYSPROCESSES
        WHERE 
			1 = CASE WHEN Status IN ( 'RUNNABLE', 'SUSPENDED' ) THEN 1
			--Transactions that are open not yet committed or rolledback
						WHEN Status = 'SLEEPING' AND open_tran  > 0 THEN 1 
						ELSE 0 END
			AND cmd NOT LIKE 'BACKUP%'						
            
-- Cursor to add actuall Procedure or Batch statement for each process
DECLARE cur_BusyProcess Cursor
    FOR SELECT  SPID
        FROM    @BusyProcess

OPEN cur_BusyProcess 
DECLARE @SPID INT     

Fetch NEXT FROM cur_BusyProcess INTO @SPID
While ( @@FETCH_STATUS <> -1 )
    BEGIN

        INSERT  @Inputbuffer
                EXEC ( 'DBCC INPUTBUFFER(' + @SPID + ')'
                    ) 

        UPDATE  @BusyProcess
        SET     EventInfo = I.EventInfo,
                EventTime = DATEDIFF(MI,LastBatch,GETDATE())
        FROM    @BusyProcess b
                CROSS JOIN @Inputbuffer i
        WHERE   B.SPID = @SPID


        DELETE  FROM @Inputbuffer

        FETCH NEXT FROM cur_BusyProcess INTO @SPID
    END
CLOSE cur_BusyProcess
DEALLOCATE cur_BusyProcess

-- Create html mail 
IF EXISTS(SELECT 1
						FROM  @BusyProcess I
						WHERE   EventInfo NOT LIKE '---BusyProcess Detection%'
						AND EventTime >= 3
						)
BEGIN
	Declare @Body varchar(max), @TableHead varchar(1000), @TableTail varchar(1000)
	Set NoCount On;


	Set @TableTail = '</table></body></html>';
	Set @TableHead = '<html><head>' +
					  '<style>' +
					  'td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:11pt;} ' +
					  '</style>' +
					  '</head>' +
					  '<body><table cellpadding=0 cellspacing=0 border=0>' +
					  '<tr><td align=center bgcolor=#E6E6FA><b>ROW ID</b></td>' +
					  '<td align=center bgcolor=#E6E6FA><b>SPID</b></td>' +
					  '<td align=center bgcolor=#E6E6FA><b>Event Info</b></td>' +
					  '<td align=center bgcolor=#E6E6FA><b>Login</b></td>'+
					  '<td align=center bgcolor=#E6E6FA><b>DBName</b></td>'+
					  '<td align=center bgcolor=#E6E6FA><b>Command</b></td>'+
					  '<td align=center bgcolor=#E6E6FA><b>CPUTime</b></td>'+
					  '<td align=center bgcolor=#E6E6FA><b>DiskIO</b></td>'+
					  '<td align=center bgcolor=#E6E6FA><b>LastBatch</b></td>'+
					  '<td align=center bgcolor=#E6E6FA><b>EventTime</b></td></tr>';

	Select @Body = (SELECT td= row_number()over(order by I.SPID ),'',		
							td=I.SPID,'',
							td= I.EventInfo,'',		
							td= MAX(I.Login),'',
							td= I.DBName,'',
							td= I.Command,'',
							td= SUM(I.CpuTime),'',
							td= SUM(I.DiskIO),'',
							td= I.LastBatch,'',		
							td= I.EventTime,''
							FROM  @BusyProcess I
							WHERE   EventInfo NOT LIKE '---BusyProcess Detection%'
							--AND EventTime >= 3
							GROUP BY SPID, EventInfo, DBName, Command, LastBatch, EventTime
							--HAVING MAX(Login) = 'CureMD'
							For XML raw('tr'), Elements
							)

	-- Replace the entity codes and row numbers
	Set @Body = Replace(@Body, '_x0020_', space(1))
	Set @Body = Replace(@Body, '_x003D_', '=')
	Set @Body = Replace(@Body, '<tr><TRRow>1</TRRow>', '<tr bgcolor=#C6CFFF>')
	Set @Body = Replace(@Body, '<TRRow>0</TRRow>', '')


	Select @Body = @TableHead + @Body + @TableTail
	-- Send mail to DBA Team
	EXEC msdb.dbo.sp_send_dbmail @recipients='aasim.rokhri@gmail.com', -- change mail address accordingly
		@subject = 'Blocking Session Detected', 
		@profile_name = 'DBA Team', -- Change profile name accordingly
		@body = @Body,
		@body_format = 'HTML' ;

	DECLARE @QKILLsp VARCHAR(1000)

	SET @QKILLsp= (SELECT DISTINCT '  KILL '+ CONVERT(VARCHAR,SPID)
							FROM  @BusyProcess I
							WHERE   EventInfo NOT LIKE '---BusyProcess Detection%'
							AND EventTime >= 3 -- Transactions Running for 3 minutes or more
							for XML path('')
							)
								
	EXEC(@QKILLsp) 

END

Open in new window


Hope it helps!
Avatar of Vitor Montalvão
Anyone would have some script that kills a lock process after a period of time and send email with headblock?
Do you really want to kill a process automatically? This can have a very bad impact in your data (can origin orphan records, for example).

I understand the part of the notification but not the part of the killing action. What's usually is to receive the notification and go immediately check why the lock is happening so you can solve the issue. If you don't solve it, for sure the lock problem will keep occuring.
Avatar of Support_38
Support_38

ASKER

Hi, Pawan Kumar

Good Morning

The script is very good, but I need it to kill only the process that is causing blocking, as it is, it kills any process that exceeds the time.

Thank you.
Good Morning Vitor

Yes, I understand what you say, but this solution is temporary, what happens is that we are doing this process of killing manually.

The application is closed and we can not change much.

As I said, this is the temporary solution that is not ideal, but at least it does not leave the system unusable.

Thank you.
When applying a temporary solution it should be something fast to do. If you're taking days then it means days that should be used to work on a properly and definitive solution.
Imagine that you can apply the temporary solution that you pretend. What if at same time there are backups or reindex occurring? They can also originate locks and you'll kill those processes as well. I really do not recommend you to go through this path. It may provoke collateral issues.

Something is originating those locks. If you already found what you can share with us so we'll try to provide a solution for the lock cause.
Hi,

As for the locks, I will take care of killing only the process that has a certain instruction, this avoids eliminating processes such as maintenance.

When the origin of the blockade, as I said, is a closed product, the manufacturer is already analyzing a possible correction.

Blocking always occurs by a cursor that remains open, however the status of the process is sleeping.

As the process status is sleeping, the sys.dm_exec_cursors DMV does not bring any information and I have to delete the process manually.

Attached is an example.
Lock_Pivotal_1801.PNG
Hi Alex,

I couldn't understand by your last comment if you got this sorted out or not.
Please let us know if this has been solved.

Cheers
ASKER CERTIFIED SOLUTION
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Good Morning Pawan,

I was able to implement the script.

Thank you.