Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
Robowski
Posting Yak Master
101 Posts |
Posted - 2013-04-26 : 05:58:07
|
Is there any way of getting the SQL that is being run that is causing a locking at any one given time?I'm aware of sp_who2, sys.dm_tran_locks and sys.sysprocesses to find out who is being locked and by what. But is there anything in SQL that stores the query so some sort of trigger can be build to record it?I know the obvious answer is the SQL profiler but that will only work during the locking not after its clearedCheersRob |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-26 : 06:02:02
|
what you could do is to note the process that causes blocking using sp_who2 and then use DBCC INPUTBUFFER (< the blocking process_id here >) to get details on statement it was executing------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
Robowski
Posting Yak Master
101 Posts |
Posted - 2013-04-26 : 06:04:44
|
thanks,But i'mm looking to automate really, so a trigger will put any locking instances into a table along with what was being locked and by what if that make sense? Could this be done using the DBCC inputbuffer/outputbuffer?quote: Originally posted by visakh16 what you could do is to note the process that causes blocking using sp_who2 and then use DBCC INPUTBUFFER (< the blocking process_id here >) to get details on statement it was executing------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-26 : 06:25:55
|
you can put same logic inside trigger. put blocking ids in a table and then use dbcc inputbuffer over them to get statements------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
Robowski
Posting Yak Master
101 Posts |
Posted - 2013-04-26 : 06:33:29
|
Are you aware of any non system tables that hold locking? Can;t put a trigger on sys.sysprocesses quote: Originally posted by visakh16 you can put same logic inside trigger. put blocking ids in a table and then use dbcc inputbuffer over them to get statements------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-26 : 06:37:40
|
nope...no user defined tables unless you set oneand data should still come from sysprocesses only.Why not add a automated jobs which checks the presence of locking according to a schedule and then logs it. You can schedule job to run only at times where you fear locking isues------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
Robowski
Posting Yak Master
101 Posts |
Posted - 2013-04-26 : 06:39:11
|
There is an issue with the software locking in sql and it is at random timesthe below might help em though, im going to read through this.. thanks for the advice thoughhttps://www.simple-talk.com/sql/database-administration/the-dba-as-detective-troubleshooting-locking-and-blocking/quote: Originally posted by visakh16 nope...no user defined tables unless you set oneand data should still come from sysprocesses only.Why not add a automated jobs which checks the presence of locking according to a schedule and then logs it. You can schedule job to run only at times where you fear locking isues------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-26 : 06:42:24
|
Thats still only speaks of making it a proc. you still have to schedule it as a job for automating the run as i suggested.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|