Recently had need of a SQL Server script that can tell me if there are open transactions on the database. The script below worked for me:
DB_NAME(s.dbid) AS DatabaseName,
CR.TEXT AS Query
FROM sysprocesses s
CROSS apply sys.Dm_exec_sql_text(sql_handle) CR
WHERE open_tran = 1
On my dev blog, I shared some tips for working with Production databases. Especially helpful if you haven't had your morning coffee yet. You don't want to accidentally delete Production data that early in the morning.