Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Rebuild Index failed? Expand / Collapse
Author
Message
Posted Wednesday, October 3, 2012 12:36 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, September 12, 2014 2:40 PM
Points: 1,058, Visits: 3,008
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

Post #1367412
Posted Wednesday, October 3, 2012 12:47 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 6:40 AM
Points: 170, Visits: 1,875
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
http://ola.hallengren.com
Post #1367969
Posted Thursday, October 4, 2012 3:17 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, September 12, 2014 2:40 PM
Points: 1,058, Visits: 3,008
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

thanks


Post #1368250
Posted Thursday, October 4, 2012 3:47 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:38 PM
Points: 6,350, Visits: 13,675
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"
Post #1368276
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse