October 3, 2012 at 12:36 am
Hi,
I am using Mr.ola.hallengren database Maintenance script last 8 months and there is no any issues for backup, rebuild index etc.,
Today Rebuild index jobs failed due to Lock request time out period exceeded, but REORGANIZE index Succeeded
Pl. suggestion me, how to fix it in features not failed outcome?
Log report
Command: ALTER INDEX [IX_TBL_CONTRACT_EMP_HISTORY_GUID] ON
[BRIBS].[dbo].[TBL_CONTRACT_EMP_HISTORY] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON)
Comment: ObjectType: Table, IndexType: NonClustered, ImageText: N/A, NewLOB: No,
FileStream: N/A, AllowPageLocks: Yes, PageCount: 18965, Fragmentation: 31.3525
Msg 50000, Level 16, State 1, Server Servername,
Procedure CommandExecute, Line 167 Msg 1222, Lock request time out period exceeded.
Outcome: Failed Duration: 01:00:00 DateTime: 2012-10-03 03:03:20DateTime: 2012-10-03 03:03:20
Command: ALTER INDEX [IX_TBL_CONTRACT_EMP_MASTER] ON
[BRIBS].[dbo].[TBL_CONTRACT_EMP_MASTER] REORGANIZE WITH (LOB_COMPACTION = ON)
Comment: ObjectType: Table, IndexType: NonClustered, ImageText: N/A, NewLOB: No, FileStream:
N/A, AllowPageLocks: Yes, PageCount: 5848, Fragmentation: 26.1628Outcome: Succeeded
Duration: 00:01:47 DateTime: 2012-10-03 03:05:07DateTime: 2012-10-03 03:05:07
October 3, 2012 at 12:47 pm
It looks like the table is locked and that the index rebuild command therefore is blocked. You have to find out what is blocking the index rebuild command.
Ola Hallengren
October 4, 2012 at 3:17 am
Great, I got reply from script author Mr. Ola Hallengren.
I have configured index rebuild on weekly basis at 3 AM..in production server,
I have checked at 9 AM there is no table locking and blocking that particular table which was made it rebuild the index.
As per your script rebuild index is online mode even though table is accessing through application even if locking that table.
Pl. confirms as blow parameters mention which is configured all production servers. also confirm updatestats for table index which is not rebuild the index.
DateTime: 2012-09-05 02:00:01
Server: servername
Version: 10.50.4000.0
Edition: Enterprise Edition (64-bit)
Procedure: [master].[dbo].[IndexOptimize]
Parameters:
@databases = 'USER_DATABASES', @FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5, @FragmentationLevel2 = 30, @PageCountLevel = 1000,
@SortInTempdb = 'N', @MaxDOP = NULL, @FillFactor = NULL, @PadIndex = NULL,
@LOBCompaction = 'Y', @UpdateStatistics = NULL, @OnlyModifiedStatistics = 'N',
@StatisticsSample = NULL, @StatisticsResample = 'N', @PartitionLevel = 'N',
@TimeLimit = NULL, @Indexes = NULL, @Delay = NULL, @LogToTable = 'Y',
@Execute = 'Y'
Source: http://ola.hallengren.com[/code%5D
thanks
October 4, 2012 at 3:47 am
ananda.murugesan (10/4/2012)
I have configured index rebuild on weekly basis at 3 AM..in production server,I have checked at 9 AM there is no table locking and blocking that particular table which was made it rebuild the index.
Think about it, you quite possibly won't find any issue when you're looking 6 hours later!!! 😉
ananda.murugesan (10/4/2012)
As per your script rebuild index is online mode even though table is accessing through application even if locking that table.
You're missing the point, the table is already locked when the rebuild command starts, online won't guarantee completion here, Books Online states
SQL Server Books Online
ONLINE = { ON | OFF }Specifies whether underlying tables and associated indexes are available for queries and data modification during the index operation. The default is OFF.
For an XML index or spatial index, only ONLINE = OFF is supported, and if ONLINE is set to ON an error is raised.
Note:
Online index operations are available only in SQL Server Enterprise, Developer, and Evaluation editions.
ON
Long-term table locks are not held for the duration of the index operation. During the main phase of the index operation, only an Intent Share (IS) lock is held on the source table. This allows queries or updates to the underlying table and indexes to continue. At the start of the operation, a Shared (S) lock is very briefly held on the source object. At the end of the operation, an S lock is very briefly held on the source if a nonclustered index is being created, or an SCH-M (Schema Modification) lock is acquired when a clustered index is created or dropped online, or when a clustered or nonclustered index is being rebuilt. ONLINE cannot be set to ON when an index is being created on a local temporary table.
OFF
Table locks are applied for the duration of the index operation. An offline index operation that creates, rebuilds, or drops a clustered, spatial, or XML index, or rebuilds or drops a nonclustered index, acquires a Schema modification (Sch-M) lock on the table. This prevents all user access to the underlying table for the duration of the operation. An offline index operation that creates a nonclustered index acquires a Shared (S) lock on the table. This prevents updates to the underlying table but allows read operations, such as SELECT statements.
This means that for the rebuild to start it will need some short term access which can be blocked if another operation is already running (an update for instance).
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply