Monitoring and reporting for Veeam Data Platform
Post Reply
fmt1962
Influencer
Posts: 12
Liked: 3 times
Joined: Jul 31, 2012 9:04 am
Full Name: Daryl W
Contact:

Page Level Locking in database indexes

Post by fmt1962 »

I have recently moved my DB over to a full version of SQL server 2008 R2.

As standard practice on this DB server, all DB's are backed up and maintenance conducted for optimisation etc, this includes a periodic reindexing.

However since I moved VeeamOne database over to the SQL server, the indexing operation has failed due to 4 indexes in VeeamOne having page level locking disabled.

businessview.ObjectProperty|IDX_ObjType
businessview.ObjectProperty|IDX_ParentType
reporter.LatestObjectProperty|IDX_LatestObjectPropertyObjectType
reporter.LatestObjectProperty|IDX_LatestObjectPropertyParentType

The reindexing problem can be fixed by enabling page level locking, however I am concerned that this setting may have been deliberate, as it is very unusual to find this disabled in any database.

I can only suspect that maybe Veeam is aware of a deadlock possibility with this enabled??? I cannot think of any other good reason this would be done? Sometimes it is unintentional, however I would like to check if it is appropriate to enable page level locking so this DB can be included in my maintenance plan, or should these settings be kept disabled?

Any ideas?
Daryl
Vitaliy S.
VP, Product Management
Posts: 27055
Liked: 2710 times
Joined: Mar 30, 2009 9:13 am
Full Name: Vitaliy Safarov
Contact:

Re: Page Level Locking in database indexes

Post by Vitaliy S. »

Hello Daryl,

Thanks for the feedback, I have passed this info to our dev team. As for now, you can enable page level locking manually.

Thank you!
fmt1962
Influencer
Posts: 12
Liked: 3 times
Joined: Jul 31, 2012 9:04 am
Full Name: Daryl W
Contact:

Re: Page Level Locking in database indexes

Post by fmt1962 »

Hi Vitality S.

It is obviously a deliberate setting as I have found that the setting for the "IDX_LatestObjectPropertyObjType" (partition 1) on table "LatestObjectProperty" has been reset to disabled, so the application must have reset this.

So I will re-enable all of them and have to seek further advice, as obviously now the settings are disabled. However the question is now, should I exclude these databases entirely from standard optimisation maintenance?

I have opened ticket #00518438

Regards,

Daryl.
Vitaliy S.
VP, Product Management
Posts: 27055
Liked: 2710 times
Joined: Mar 30, 2009 9:13 am
Full Name: Vitaliy Safarov
Contact:

Re: Page Level Locking in database indexes

Post by Vitaliy S. »

Hi Daryl,

I have just consulted with our dev team and you exclude can this table from indexing "reporter.LatestObjectProperty". This table contains the latest data on the objects which is rebuilt every collection cycle.

Thank you!
mistafu
Novice
Posts: 3
Liked: 1 time
Joined: Apr 25, 2016 7:17 am
Contact:

Re: Page Level Locking in database indexes

Post by mistafu »

Hello,

since update to V9 i've got the same problem with periodic reindexing, veeam db doesn't allow page locks.
Can i still just change allow_page_lock 0 to allow_page_lock 1 for the reindexing?

Andreas
mistafu
Novice
Posts: 3
Liked: 1 time
Joined: Apr 25, 2016 7:17 am
Contact:

Re: Page Level Locking in database indexes

Post by mistafu »

I don't know how to edit my post...
it's about the VeeamONE database not the normal Veeam db.
Shestakov
Veteran
Posts: 7328
Liked: 781 times
Joined: May 21, 2014 11:03 am
Full Name: Nikita Shestakov
Location: Prague
Contact:

Re: Page Level Locking in database indexes

Post by Shestakov »

Hello Andreas,
Yes, you can use page locking. You may also enable it only to the failing indexes if you have the similar issue as OP.
Thanks!
Post Reply

Who is online

Users browsing this forum: No registered users and 4 guests