October 31, 2008 at 8:18 am
Hello!
I have a database which many users access, which includes a table (which i'll refer to as tablex) which we implement row locking on to determine if someone is accessing that record.
There is an identity (i'll call identx) for each record.
Basically, i'm interested in some SQL which will allow me to pull back a list of records on this table which are locked, and who holds the lock on it (i.e. SQL/Windows user/hostname of source).
I've tried sp_lock and sp_who, but this doesn't give me the desired information.
Dealing with sql 2000 and sql 2005.
Any ideas?
Thanks in advance 🙂
pld
October 31, 2008 at 10:35 am
sp_lock will give you information about the locked row (if indeed a row is locked).
If there's a row lock the Type column will display RID and the Resource column will display something like: 1:385:0
Where 1 is the file number, 385 is the page number and 0 is the row id.
You can then use dbcc page to take a look at the specific page:
DBCC PAGE (db_name, 1, 385, 3);
Looking some way down the output you'll see a DATA section. Slot 0 indicates row zero, slot 1 indicates slot 1 and so on. In the above example, where the RID is 1:385:0 I'd be looking for slot 0.
It's not pretty but it's possible to find the row in question.
You'll need to turn on the trace flag 3604 to use dbcc page.
Hope that helps.
October 31, 2008 at 11:10 am
Hi,
Even though I fail to see why you would need to do this 🙂 ....
The slot array contains the offsets for the rows on the page so you would have to use that to find the row in the page.
http://www.sqlskills.com/blogs/paul/category/Undocumented-commands.aspx has got all the details.
🙂
/Elisabeth
elisabeth@sqlserverland.com
MCITP | MCT
http://sqlblog.com/blogs/elisabeth_redei/
http://linkedin.com/in/elisabethredei
October 31, 2008 at 11:59 am
The reason I want to do this is that our software locks a record to show someone is in a case, so I want a list of cases which are currently 'in use' 🙂
Thanks for the replys guys, i'll look into it and post back soon.
October 31, 2008 at 8:29 pm
I dont know how you do this, but once you get the spid which is creating a row lock on that table, then using sysprocesses you can get all the information you need, like username, Windows login name, host name and all other good information.
If you are using SQL Server 2000,
Same information is also available from Current Activity ( This is under management in Object explorer in Enterprise Manager ). Check for processes... for a particular SPID, drag page towards to right and check for all information.
If you are using SQL Server 2005,
Same information is also available from Activity Monitor, once SPID is known then you can check for all related information.
SQL command,
Select * from sysprocesses ( works in SQL Server 2000 and 2005).
Hope this helps.
Imran Mohammed.
November 1, 2008 at 7:35 am
Hi again,
I don't mean to poke into what's obviously your business but I was thinking.. You mentioned it is your SW and then I thought there must be a plethora of other things you could do to find which rows are currently being processed?
If you want to make sure that you get information about ALL operations, I'd say you need to run your "auditing" code together with the code that is manipulating the data. If you are polling, you are likely to miss quite a bit.
Obviously for UPDATE/INSERT/DELETE you can use triggers but if you want to get SELECTs as well, it will be much much easier and reliable to add code that grabs whatever value is sent in for your identx column (whether that code is added in a stored procedure or in component) then to identify this by using lock information and DBCC PAGE. Even parsing data from a trace that is filtered on that particular table seems closer at thand.
As you now DBCC PAGE is undocumented, undsupported and uneverything so to rely on that in anything that resembles production code is just not a very good idea.
Ah.. anyway... there is a new sys.fn_PhysLocFormatter in SQL 2K8 that does exactly what you are looking for.. Uneverything I should mention 🙂
/Elisabeth
elisabeth@sqlserverland.com
MCITP | MCT
http://sqlblog.com/blogs/elisabeth_redei/
http://linkedin.com/in/elisabethredei
November 4, 2008 at 9:35 am
Is there a way to see in the sp_lock - which actually event it is on.
I have about 6 different locks.
So i was wondering at what stage is the code at - in the SQL Profiler just sitting there.
131 10 1364745846 2 PAG 1:2068 IS GRANT
131 10 339042378 1 PAG 3:2795356 IS GRANT
131 10 1237579447 0 TAB IS GRANT
131 10 1364745846 0 TAB IS GRANT
131 10 339042378 1 KEY (bc00d98f992e) S GRANT
131 10 339042378 0 TAB IS GRANT
131 10 0 0 DB S GRANT
November 4, 2008 at 10:07 am
Hi,
Has the defintition of your dilemma changed or are you still looking for a way to audit accesses to particular rows in a table?
By event I trust you mean statement (that shows up as an event in Profiler) but what do you mean that it is just "sitting there". That's what it will look like once it has completed (SP/Stmt:Completed event).
🙂
/Elisabeth
elisabeth@sqlserverland.com
MCITP | MCT
http://sqlblog.com/blogs/elisabeth_redei/
http://linkedin.com/in/elisabethredei
November 4, 2008 at 10:15 am
Ok i needed to put on lock release and acquired also to see it .
Then i could see what page of data it was on.
Thanks.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply