Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2004
    Montreal, Canada

    Question Unanswered: Interrogating lock information...

    I'm working on a PB / OLE DB / SQL2000 application architecture. Here is my requirement:

    I need to find out if/how I can interrogate a SQL2000 database for its locking information, with a fairly high degree of granularity. That is to say I'd want to extract the following info:

    ---> The name of the table thats locked. (this I can get from SYS tables...)

    ---> The table and the row or range of rows that are locked.

    ---> The table and the row (RId or physical key) that is locked.

    My understanding is that rows that are locked as part of a page lock, or a row that is rowlocked, is managed internally within the DBMS. Any one of the three lock types will register e.g. on the SYSLOCKS table, but only specifies the name of the locked object (i.e. table name, index, etc...).

    Is my only real solution to create my own custom locking info table(s)? I don't strongly favor that sort of solution, because the code to properly manage such a table would be pretty ambitious to write, and prone to corruption at some point no matter how well we build it.

    Are there any inventive solutions out there?

  2. #2
    Join Date
    Jul 2003
    San Antonio, TX
    All the tools to do that are at your finguretips, - PerfMon, DBCC, Profiler, and QA. Look into sp_lock stored procedure that will point you to master.dbo.syslocks and master.dbo.syslockinfo. Look into perfmon counters as well as various profiler templates.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts