Locking SQL - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Locking SQL

Author

Topic

ConorS79
Starting Member

5 Posts

Posted - 2008-01-23 : 06:08:45

Hi

On SQL i have created a query. Here is the code:

use DB1;
select * from [Jobs]
select resource_type, request_mode, request_status, request_session_id
from sys.dm_tran_locks

It produces the following results when run:

|resource_type | request_mode | request_status | request_session_id
|Database | S | Grant | 51
|Database | S | Grant | 54

What is "S"? what are the other possibilities and their meaning for this field.
And.. 51 and 54...what are they exactly? Are they individual people or user ids?
For example, could 51 be "Advanced users" and 54 be "Generic Users" under SQL security?

My next question is...
I suspect i have too many Indexes on my table "Jobs". I suspect it is causing page locks. Especially when someone is updating the records.
I will run this query when users complain to me about not being able to edit records.

Ok..Question is...if i have a PageLocking entrant..
Through SQL manager..is it possible to boot a user off temporarily..?
How do you do it?

Thanks
In Advance
Conor

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-23 : 07:00:55

I looked in Books Online and found this information about dm_tran_locks here
http://msdn2.microsoft.com/en-us/library/ms190345.aspx

quote:
Mode of the request. For granted requests, this is the granted mode; for waiting requests, this is the mode being requested.

E 12°55'05.25"
N 56°04'39.16"

ConorS79
Starting Member

5 Posts

Posted - 2008-01-23 : 07:01:06

request_session_id is a system user ID.

I just ran that query on the live system, (we have around 30 users), it listed around 20-30 unique request_session_id's.
So, i'm guessing User, 51 links to a user in active directory.
Question is...how do i find out what actual user that is?
How do i match them?

ConorS79
Starting Member

5 Posts

Posted - 2008-01-23 : 07:01:57

Thanks Peso.
I'll have a look now.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources