Kill Open Transaction in SQL

To check if any open transaction in SQL Server, you can use:

DBCC opentran

after that you will get this result if there is no open transaction:
No active open transactions. DBCC execution completed. If DBCC printed error messages, contact your system administrator.

or else if you have open/uncommited transaction that maybe cause by error in design, you will get this :
Transaction information for database 'db_name'.
Oldest active transaction: SPID (server process ID): 54 UID (user ID) : -1 Name : user_transaction LSN : (1702:160598:3) Start time : Sep 4 2011 12:27:32:713AM SID : 0x0105000000000005150000008cdebb00f310b4f0b1a5950ae8030000 DBCC execution completed. If DBCC printed error messages, contact your system administrator.

If you want to kill the process, remember the SPID, then open the Activity Monitor, then killed it by right clicking on it, then choose Kill Process.

in SQL Server 2000, you can find Activity Monitor in Enterprise Manager, then expand the tree like below picture, the activity monitor will be shown in right frame.

After you right clicked and killed the process, you have to refresh it manually by right clicking on Current Activity - node, then choose Refresh. then open again the Process Info node to see the latest activities.


in SQL Server 2005, Activity monitor can be find in SQL Server Management Studio, inside Management tree node, double clicked it, than a window of processes activity will be show, as picture below:


in SQL Server 2008, Activity monitor were located on the toolbar of SQL Server Management Studio, as shown in picture below:

After you killed the process, you don't have to refresh it, just wait until it disappear from the list in few seconds.

^^