SQL SERVER - 2005 -Track Down Active Transactions Using T-SQL « Journey to SQL Authority with Pinal Dave

Feed on: Posts; Comments

SQL SERVER - 2005 -Track Down Active Transactions Using T-SQL

July 24, 2008 by pinaldave

Just a day ago, I was wondering how many active transaction are currently in my database. I found following DMV very useful - very simple and to the point.

Following SQL will return currently active transaction.

SELECT * FROM sys.dm_tran_session_transactions

Reference : Pinal Dave (http://www.SQLAuthority.com)

Posted in Author Pinal, SQL, SQL Authority, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology | Tagged SQL Transactions | 10 Comments

10 Responses to “SQL SERVER - 2005 -Track Down Active Transactions Using T-SQL”

on July 24, 2008 at 9:39 am Viraj

Dear Pinal,

I am using SQL 2000. I tried executing the command.

It gave following error :
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name ’sys.dm_tran_session_transactions’.

I tried using Master and TempDB databases, but same error persisted.

Kindly suggest.

Regards,
Viraj
on July 24, 2008 at 1:55 pm Sandeep

can i attach a read only restored database. I restored a
database Test in Stand by Mode. After that i detached that database and while restoring i m getting msg “Cannot attach a database that was
being restored.”.
Is it that i can not attach a readonly restored
detached database ?
on July 24, 2008 at 4:59 pm Kamran Shahid

Nice Data Management View
on July 25, 2008 at 4:29 am Diablo

Very useful. Thanks.
on July 25, 2008 at 5:34 am Emeka

@Viraj

That piece of sql doesn’t work in sql 2000. The Dynamic management view is one of the new features in SQL Server 2005 you can use to query the health of sql server 2005.

For it to work, you have to run it in SQL server 2005 environment.
on July 25, 2008 at 5:53 am Emeka

@Sandeep

Because it is on standby mode, the restore is not finished yet. sql server is expecting you to
apply more backups. when when you are about to apply the last backup,
choose the RECOVERY option. This tells SQL server that the restore is finished.

IN a situation where you have only one backup file to restore, you should always choose the RECOVERY option
except when you are log shipping or mirroring database.

To solve your problem, restore the DB with the RECOVERY option.
You can then detach it and re-attach it.
on July 25, 2008 at 9:50 am Viraj

Dear Emeka,

Thanks 4 ur reply.
I wonder, is there any similiar type of DMV or other similiar concept in SQL 2000 !!!

Regards,
Viraj
on July 25, 2008 at 11:41 am Emeka

@Virag,

With SQL Server 2000, Unfortunately no. However you can run a dbcc opentran(’databasename’) on 2000/05.

This will only give you the longest running transaction in that database.
on September 19, 2008 at 5:33 pm pankaj

hi pinal,
i preparing for interview in sql server plz mail me on p.adhikari1981@gmail.com

some query(technical) which is asked in interview
on October 1, 2008 at 5:36 pm Goms

Hi pinal,

I want to trace the users activity in database.I able to trace ddl transcation using trigger.But when i try to track the dml
transaction using event_data() it will not work.
so how to track the users dml activity or shall i run the sql profiler?.If yes pls give me the clear picture abt sql profiler

pls mail me on gomsbox@yahoo.com

Thanks
Goms