September 30, 2012 at 8:34 am
Dear Experts,
We have some tables that we call them LKs.( look up tables)
It is obvious that we have no insert, update or delete on these tables.
Yesterday we've started a job which is doing some works on data but it randomly thrown timeout.
I've checked the db and I see that a transaction is running on the LK table!!!!
To be honest, until now I never thought about an open transaction on selects. why is this happened? What is it going on here?!:(
Please Help me!
Best Regards,
Ashkan
September 30, 2012 at 8:45 am
ashkan siroos (9/30/2012)
We have some tables that we call them LKs.( look up tables)It is obvious that we have no insert, update or delete on these tables.
Why is it obvious? Are those tables on a read only filegroup? If not, what's preventing changes?
I've checked the db and I see that a transaction is running on the LK table!!!!
How did you identify that?
To be honest, until now I never thought about an open transaction on selects. why is this happened? What is it going on here?!:(
No idea. Insufficient information. How did you determine there's an open transaction with locks held on that table? What type of locks? Did you identify what session holds those locks, where it is being run from and what command it's running?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 30, 2012 at 9:05 am
Dear GilaMonster,
Why is it obvious? Are those tables on a read only filegroup? If not, what's preventing changes?
I'm sorry , we only insert to our tables throw code, and we didn't write any code to insert into these tables, and nobody is allowed to execute any query on DB except me and my manager.
How did you identify that?
I ran the "dbcc opentran" command.
it returned an open transaction that didn't killed after 5 minutes, and it has the select statement on it
and please tell me how can I find type of lock?
Best Regards,
Ashkan
September 30, 2012 at 9:54 am
ashkan siroos (9/30/2012)
How did you identify that?
I ran the "dbcc opentran" command.
it returned an open transaction that didn't killed after 5 minutes, and it has the select statement on it
and please tell me how can I find type of lock?[/quote]
DBCC OpenTran doesn't give you any info on what tables or what statements are involved in a transaction, so how did you identify that the transaction affected your lookup tables and that it was a select?
For info on identifying causes of blocking, chapter 6 of http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 30, 2012 at 10:14 am
I use dbcc opentran to define what is the id of the open transaction and then I used this query to see the information of that transaction
SELECT *
FROM sys.dm_tran_session_transactions tst INNER JOIN sys.dm_exec_connections ec ON tst.session_id = ec.session_id
CROSS APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle)
Best Regards,
Ashkan
September 30, 2012 at 4:10 pm
All that gave you was the last command that the session in question ran, not all of them. If there were multiple commands run in the transaction, that query would have returned just the very last one, which may not have been responsible for the locks held.
The query would also have given you the host name and application, so you can start your investigation there, find out what is starting but not committing a transaction and what it was doing within that tran.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply