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 (https://blog.sqlauthority.com)
Is your SQL Server running slow and you want to speed it up without sharing server credentials? In my Comprehensive Database Performance Health Check, we can work together remotely and resolve your biggest performance troublemakers in less than 4 hours.
Once you learn my business secrets, you will fix the majority of problems in the future.
Have you ever opened any PowerPoint deck when you face SQL Server Performance Tuning emergencies? SQL Server Performance Tuning Practical Workshop is my MOST popular training with no PowerPoint presentations and 100% practical demonstrations.
Essentially I share my business secrets to optimize SQL Server performance.
30 Comments. Leave new
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
Hi Viraj,
It’s a SQL 2005 Command, you can’t use it without upgrading
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 ?
Nice Data Management View
Very useful. Thanks.
@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.
@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.
Dear Emeka,
Thanks 4 ur reply.
I wonder, is there any similiar type of DMV or other similiar concept in SQL 2000 !!!
Regards,
Viraj
@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.
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
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
Hi All,
I am writing stored procedure for transfering data from 5 staging tables to 5 master tables using sql server 2005 which are in same db and same server.
I have following requirements
–>I will first check the count in both staging and master tables
–>if staging table count is greater than master table count then
–>truncate master table records.
–>then insert query executes which fetches records from staging to master table.
Among 5 tables last two are dependent.
For each staging to master table i am writnig individual
Transaction.
But the problem is when i am executing this sql server getting very slow almost getting struck.
Is this the correct way which i am following if not please suggest me the right way.
its really urgent.
Thanks in advance for any help
hi pinal sir,
i am havng doubt regarding transactions……in oracle we need not give explicitly BEGIN TRAN………. In sqlserver 2000 also………we have to write BEGIN TRAN explicitly………
i heard that in sqlserver 2000 also we need not write explicitly…..by setting some values………..how can i?………tell me the solution
i got the answer……….if anybody dont know then it will helpful
for implicit-transactions
set implicit_transactions on
set implicit_transactions off
example is(no need of begin tran)
update authors
set phone=’9866530517′
where au_id=’756-30-7391′
update publishers
set city=’hyderabad’,country=’india’
where pub_id=’9952′
commit tran
hi dave
i got an idea about active transaction’s count in a database
with your query.but i have one query,how can i measure
the “hit count” second for a paticular transaction in table
like (it may be single updation,bulk copy of data’s insertion),
so how can i measure the second’s count.
Thanks in Advance
Revathy
Dear Dave Pinal, I would like to know if there a instruccion or a way to list all triggers stored in database and see the status “enabled or disabled” of each one.
thanks in advance,
Julio
Hi Julio,
You can find all triggers status by this query in 2005:
select name,
CASE WHEN OBJECTPROPERTY (id, ‘ExecIsTriggerDisabled’) = 0
THEN ‘Enabled’ ELSE ‘Disabled’ END
from sysobjects
where type = ‘tr’
Thanks,
Tejas
SQLYoga.com
Ty Tejas!!!
Hi Pinal sir i want to know the active count of the transaction in sql server 2000, moreover i want to know can it be possibel that transaction is taking 24hrs to get it commited
Hi,
Can you give detail explaination on active transactions or can you reffer any doucment
Hi Pinal,
only two questions please:
1- how can i now the transactions which are causing locks in the sqlserver database,
2- is it possible that an inner transaction and the main transaction generate a deadlock
thanks…….
1 exec sp_lock
2 Give more informations on what you want to know