Video Screencast Help

Maintenance plan fail after upgrade due to Page Level Locking

Created: 27 Aug 2010 • Updated: 30 Sep 2010 | 7 comments
GertjanA's picture
This issue has been solved. See solution.

Hello all,

We have maintenance plans failing because of Page Level Locking being unticked at some indexes.
Scanner001 (Liam right?) has posted an idea: https://www-secure.symantec.com/connect/idea/page-level-locking

I am no SQL man, but is there a query to check if PLL is being set at indexes?
If it is not set (i checked, and it is indeed not on an index mentioned), can it be turned on without problems? Are there indexes that should not have this ticked?

Thanks.

Comments 7 CommentsJump to latest comment

Liam Finn's picture

Let me do some digging I think I have a script to correct that

The PLL should be on. For some reason it is disabled when you upgrade the EV databases and as I said in the Idea they should have a script that goes back and turns it on again

Liam Finn's picture

I knew i had a script for that

set quoted_identifier off

go

SELECT "alter index [" + i.name + "] ON [" + s.name + "].[" + t.name + "]

SET (ALLOW_PAGE_LOCKS = ON)

"

--    s.namet.namei.name

FROM sys.schemas s

JOIN sys.tables t ON

t.schema_id  = s.schema_id

JOIN sys.indexes i ON

i.object_id = t.object_id

WHERE

i.index_id > 0

AND INDEXPROPERTY(i.object_id, i.name, 'IsPageLockDisallowed') = 1

Option 1: No row returns --- No action needed

Option 2. Rows returned. --- Copy the result and run in the same database, it will fix the indexes in question.

SOLUTION
GertjanA's picture

Thanks Liam,

According to our DBA's, if we run a REBUILD indexes, this should set the PLL on again.
Any experience with that?

Thx.

Thank you, Gertjan, MCSE, MCITP,MCTS, SCS, STS
Company: www.t2.nl

www.quadrotech-it.com

www.symantec.com/vision

JesusWept3's picture

rebuilding indexes should be part of the maintenance plan anyway, right?

GertjanA's picture

but if rebuilding also fails (due to another issue we're working on), than you have a problem.
We've corrected things, and hope that by monday all is well again.

Thank you, Gertjan, MCSE, MCITP,MCTS, SCS, STS
Company: www.t2.nl

www.quadrotech-it.com

www.symantec.com/vision

Liam Finn's picture

Whats up with you DB indexes?

Sounds like you have a few issues there.

Never heard of an index rebuild fixing the issue i always use the script to correct it for me

GertjanA's picture

Hi Liam,

There are no issues, only the maintenance plan failed. Recently this environment was upgraded to 7.5 (to go to 8/9/) and since the issue occurred.

DBA states that a succesfull rebuild automatically adds the check to 'allow page locking'

Will report monday if ok.

Thank you, Gertjan, MCSE, MCITP,MCTS, SCS, STS
Company: www.t2.nl

www.quadrotech-it.com

www.symantec.com/vision