Page Locking Disabled !

Posted on 2010-03-24
Medium Priority
Last Modified: 2012-05-09
Recieved this error on a maintenance job to re-origanise indexes.....

I have tried to find answers online but can't seem to get what I'm looking for.

I know Page Locking is set by default by SQL however I have run a query and discovered that a number of tables have this disabled on the Indexes.
It appears Row Level Locking is Enabled Page Level Disabled.

What is the best option for a table that is used heavily !
I do have locking happening on the DB when a task is being completed that is completed on one of the tables on this list.

I have seen people saying ENABLE page locking is better but then came across this article

We are having I/O issues which is seperate to this however just want to know

For a heavily accessed table should Page locking be enabled or disabled ???


Question by:EHardie
1 Comment
LVL 25

Accepted Solution

DBAduck - Ben Miller earned 2000 total points
ID: 28482240
Well, by default, Row and Page locks are allowed on indexes, and I would not change it from that.

So if you use INDEXPROPERTY(Table ObjectID, Index Name, 'IsPageLockDisallowed')
and you get a 1 then they are disabled and I would renable them with the query in the link you gave.

You should see that Row and Page locks are allowed on all your indexes.  The only reason to change this is if the tables are not Updated much at all, and I mean anything more than 1 time a month probably (you can determine the threshold).

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov‚Ķ
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question