locked
How to find SQL Lock esculsation settings? RRS feed

Answers

  • This will get you started:

    SELECT name, lock_escalation, lock_escalation_desc 
    FROM  sys.tables
    

    You can dig around in sys.tables to figure out what exactly it is that you need

     


    No great genius has ever existed without some touch of madness. - Aristotle
    • Proposed as answer by WeiLin Qiao Thursday, November 18, 2010 5:29 AM
    • Marked as answer by Alex Feng (SQL) Monday, November 29, 2010 4:20 AM
    Wednesday, November 17, 2010 12:37 AM

All replies

  • Try the forums under the SQL Server category... It does not look like you need help in designing your own software.

    The following is signature, not part of post
    Please mark the post answered your question as the answer, and mark other helpful posts as helpful, so they will appear differently to other users who are visiting your thread for the same problem.
    Visual C++ MVP
    Monday, November 15, 2010 7:17 PM
  • This will get you started:

    SELECT name, lock_escalation, lock_escalation_desc 
    FROM  sys.tables
    

    You can dig around in sys.tables to figure out what exactly it is that you need

     


    No great genius has ever existed without some touch of madness. - Aristotle
    • Proposed as answer by WeiLin Qiao Thursday, November 18, 2010 5:29 AM
    • Marked as answer by Alex Feng (SQL) Monday, November 29, 2010 4:20 AM
    Wednesday, November 17, 2010 12:37 AM
  • --By applying the filter in the where clause you get the answers to questions like:
    ---- What SQL Statement is causing the lock?
    --- Which user has executed the SQL statement that's holding the locks?
    --- What objects/tables are being locked?
    --- What kinds of locks are being held and on which pages, keys, RID's?

     

    SELECT  L.request_session_id AS SPID,
            DB_NAME(L.resource_database_id) AS DatabaseName,
            O.Name AS LockedObjectName,
            P.object_id AS LockedObjectId,
            L.resource_type AS LockedResource,
            L.request_mode AS LockType,
            ST.text AS SqlStatementText,       
            ES.login_name AS LoginName,
            ES.host_name AS HostName,
            TST.is_user_transaction as IsUserTransaction,
            AT.name as TransactionName,
            CN.auth_scheme as AuthenticationMethod
    FROM    sys.dm_tran_locks L
            JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id
            JOIN sys.objects O ON O.object_id = P.object_id
            JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
            JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id
            JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id
            JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id
            CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST
    WHERE   resource_database_id = db_id()
    ORDER BY L.request_session_id


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, November 17, 2010 8:15 AM