SQL 2008 Database Lock

rrowe68
rrowe68 used Ask the Experts™
on
I am the 'default' database admin for our 2008 SQL Server (not an expert).   I am trying to figure out the clearest way to identify a database lock that is preventing some users from accessing the database.

I have vb application (windows form) that hits my SQL 2008 Database.   Every once in a while, a request times out b/c of the unidentified database lock.   Restarting the SQL Server obviously fixes the issue, but I am trying to identify the specific lock, so I can go back into the VB code and modify it.

I can get into the Activity Monitor and view it.   What am I looking for?  Any tips? Any direction?

Thanks!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
>> I can get into the Activity Monitor and view it.   What am I looking for?  Any tips? Any direction?

You can look it in the following things:

1. Activity monitor
2. Profiler to capture Deadlocks along with Deadlock graphs.
3. DMV's
http://www.dalun.com/blogs/10.13.2006.htm

Hope this helps
If you mean dead locks then it is hard to find out the VB code which causes it. You just may execute sp_who2 to look at dead-locked processes.

If you mean some database object lock then you could query sys.dm_tran_locks view to find this information. Older SQL versions have it in sys.syslockinfo.

Both sys.syslockinfo and sys.dm_tran_locks are described in SQL Server Help file.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial