Tell me more ×
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. It's 100% free, no registration required.
up vote 1 down vote favorite

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 at 10:44
2  
One example: ALTER INDEX REORGANIZE will fail if page-level locks are disabled. – Paul White Jan 30 at 11:03
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 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 at 11:45
@MartinSmith, gonna try and export an example version of it – Peter PitLock Jan 30 at 12:27

1 Answer

up vote 2 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 at 10:16

Your Answer

 
discard

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.