Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Is it possible to run a SQL Server Query in order to view all SQL Server Indexes which have page locks enabled?

share|improve this question
Be warned that the normal state of an index is to have page locks enabled. Mess with this setting at your own peril. – Remus Rusanu Jan 30 '13 at 10:44
We had two specific Non-Clustered Indexes with the setting "use page locks when accessing the index", which caused (according to trace 1222) a deadlock. I removed the 2 page locks today, am I at risk for removing them? The problem was that they were deadlocking on unrelated data. Thanks SQL Kiwi - this is important to know what you said because we have index rebuilding happening - if i have it set as to Reorganize then it will cause an error - I need to know of this. – Peter PitLock Jan 30 '13 at 11:32
@PeterPitLock - RE: The deadlocks please supply the requested information to your other question (full table definition and full deadlock trace output) – Martin Smith Jan 30 '13 at 11:45
@MartinSmith, gonna try and export an example version of it – Peter PitLock Jan 30 '13 at 12:27
up vote 3 down vote accepted
SELECT OBJECT_SCHEMA_NAME(object_id) AS object_schema,
       OBJECT_NAME(object_id)        AS object_name,
       name                          AS index_name
FROM   sys.indexes
WHERE  allow_page_locks = 1 
share|improve this answer
excellent,thanks!! – Peter PitLock Jan 30 '13 at 10:16

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.