August 31, 2018 at 9:22 am
Hi All,
I'm working on an issue related to reorg jobs failing because page level locking is not enabled for several of the indexes. It looks like these jobs were created using the SSMS wizard, and I'm looking at implementing Ola's solution.
https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
...I would probably use something like this:EXECUTE dbo.IndexOptimize
@databases = 'USER_DATABASES',@databases = 'USER_DATABASES',
@FragmentationLow = NULL,@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,@FragmentationLevel2 = 30,
@UpdateStatistics = 'ALL',
However, that has me wondering if I will run into the same issue if the allow_page_locks isn't on. Does anyone have any thoughts or guidance on this?
September 6, 2018 at 8:20 am
You will probably have the same problem. Restore the DB to a DBA safe area and test. But I would imagine you need to turn it on for it to work.
I had this issue once years ago and I think I just turn it on and that was the end of that.
September 6, 2018 at 9:00 am
I think you are probably right. I've been getting around it with scripts that turn page locking on before the job and then off again. Its an app we purchased, and the release notes indicated they were running into deadlocks around that table. So I'm guess the fix was just to disable it altogether. I'll play around in a sandbox.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply