Skip to content

Query to Find and Kill Long Running Transactions in SQL Server 2008 R2 or 2012

by on September 22, 2014

If your DB is hanging or stuck in process of changes such as Read_only or Read_write, it may be hung on a long running transaction. Find and kill the evil things 🙂

Find open transactions in a database

use yourdbhere;

DBCC OPENTRAN;

Go

Get long running transactions in a database

select spid, db_name(dbid), * from master..sysprocesses

select spid, db_name(dbid) as dbname, program_name, loginame, * from master..sysprocesses where dbid = 9 –this would be your database id

select spid, db_name(dbid) as dbname, program_name, loginame, * from master..sysprocesses where dbid = 7 –this would be your database id

image

Example of how to kill processes

kill 70;

kill 127;

kill 130;

kill 131;

Advertisements

From → Microsoft, SQL Server

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: