Maintenance plan fail after upgrade due to Page Level Locking

[GertjanA]

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.

[Liam_Finn1]

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_Finn1 · Accepted solution]

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.name, t.name, i.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.

[GertjanA]

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.

[JesusWept3]

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

[GertjanA]

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.

[Liam_Finn1]

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]

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.