March 1, 2012 at 8:22 am
I have an index maintenance job which runs once every week.
It has been failing for some reason for the last 2 weeks , although it had succeeded the week before .
Heres the script .
EXECUTE EDMDefault.dbo.IndexOptimize @databases = 'APEXProduction',
@FragmentationHigh = 'INDEX_REBUILD_OFFLINE',
@FragmentationMedium = 'INDEX_REORGANIZE',
@FragmentationLow = NULL,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y',
@LogToTable = 'Y'
Thanks in advance 🙂
March 1, 2012 at 8:30 am
Since none of us are looking over your shoulder, please will you post the stored procedure definition and the error message?
Thanks
John
March 1, 2012 at 8:35 am
😀 sure sure ... although i wish u were actually here looking over my shoulder 🙂
Heres the message i got from the jobhistory ...
Message
Executed as user: NAPA\ENTDB_SVC_NOITAR. ...20 Version: 10.0.4000.0 Edition: Standard Edition (64-bit) Procedure: [EDMDefault].[dbo].[IndexOptimize] Parameters: @databases = 'APEXProduction', @FragmentationLow = NULL, @FragmentationMedium = 'INDEX_REORGANIZE', @FragmentationHigh = 'INDEX_REBUILD_OFFLINE', @FragmentationLevel1 = 5, @FragmentationLevel2 = 30, @PageCountLevel = 1000, @SortInTempdb = 'N', @MaxDOP = NULL, @FillFactor = NULL, @PadIndex = NULL, @LOBCompaction = 'Y', @UpdateStatistics = 'ALL', @OnlyModifiedStatistics = 'Y', @StatisticsSample = NULL, @StatisticsResample = 'N', @PartitionLevel = 'N', @TimeLimit = NULL, @Indexes = NULL, @Delay = NULL, @LogToTable = 'Y', @Execute = 'Y' Source: http://ola.hallengren.com [SQLSTATE 01000] (Message 50000) DateTime: 2012-02-25 22:00:03 Database: [APEXProduction] Status: ONLINE Mirroring role: N/A Standby: No Updateability: READ_WRITE User access: MULTI_USER Is accessible: Yes Recovery model: FULL [SQLSTATE 01000] (Message 50000) DateTime: 2012-02-25 22:00:04 Command: UPDATE STATISTICS [APEXProduction].[dbo].[ACL_GROUP_MEMBER] [ACL_GROUP_MEMBER_PK] [SQLSTATE 01000] (Message 50000) Outcome: Succeeded Duration: 00:00:01 DateTime: 2012-02-25 22:00:05 [SQLSTATE 01000] (Message 50000) DateTime: 2012-02-25 22:00:05 Command: ALTER INDEX [A_G_MEMBER_ACL_PRINCIPAL_ID_IX] ON [APEXProduction].[dbo].[ACL_GROUP_MEMBER] REORGANIZE WITH (LOB_COMPACTION = ON) Comment: ObjectType: Table, IndexType: NonClustered, LOB: No, AllowPageLocks: Yes, PageCount: 10297, Fragmentation: 16.8593 [SQLSTATE 01000] (Message 50000) Outcome: Succeeded Duration: 00:00:21 DateTime: 2012-02-25 22:00:26 [SQLSTATE 01000] (Message 50000) DateTime: 2012-02-25 22:00:26 Command: UPDATE STATISTICS [APEXProduction].[dbo].[ACL_GROUP_MEMBER] [A_G_MEMBER_ACL_PRINCIPAL_ID_IX] [SQLSTATE 01000] (Message 50000) Outcome: Succeeded Duration: 00:00:01 DateTime: 2012-02-25 22:00:27 [SQLSTATE 01000] (Message 50000) DateTime: 2012-02-25 22:00:27 Command: ALTER INDEX [ACL_GROUP_MEMBER_POLICY_ID] ON [APEXProduction].[dbo].[ACL_GROUP_MEMBER] REORGANIZE WITH (LOB_COMPACTION = ON) Comment: ObjectType: Table, IndexType: NonClustered, LOB: No, AllowPageLocks: Yes, PageCount: 12107, Fragmentation: 5.30272 [SQLSTATE 01000] (Message 50000) Outcome: Succeeded Duration: 00:00:19 DateTime: 2012-02-25 22:00:46 [SQLSTATE 01000] (Message 50000) DateTime: 2012-02-25 22:00:46 Command: UPDATE STATISTICS [APEXProduction].[dbo].[ACL_GROUP_MEMBER] [ACL_GROUP_MEMBER_POLICY_ID] [SQLSTATE 01000] (Message 50000) Outcome: Succeeded Duration: 00:00:01 DateTime: 2012-02-25 22:00:47 [SQLSTATE 01000] (Message 50000) DateTime: 2012-02-25 22:00:47 Command: UPDATE STATISTICS [APEXProduction].[dbo].[ACL_GROUP_MEMBER] [_WA_Sys_00000002_61716316] [SQLSTATE 01000] (Message 50000) Outcome: Succeeded Duration: 00:00:00 DateTime: 2012-02-25 22:00:47 [SQLSTATE 01000] (Message 50000) DateTime: 2012-02-25 22:00:47 Command: UPDATE STATISTICS [APEXProduction].[dbo].[ACL_GROUP_MEMBER] [_WA_Sys_00000004_61716316] [SQLSTATE 01000] (Message 50000) Outcome: Succeeded Duration: 00:00:01 DateTime: 2012-02-25 22:00:48 [SQLSTATE 01000] (Message 50000) DateTime: 2012-02-25 22:00:48 Command: UPDATE STATISTICS [APEXProduction].[dbo].[ACL_GROUP_MEMBER] [_WA_Sys_00000008_61716316] [SQLSTATE 01000] (Message 50000) Outcome: Succeeded Duration: 00:00:00 DateTime: 2012-02-25 22:00:48 [SQLSTATE 01000] (Message 50000) DateTime: 2012-02-25 22:00:48 Command: UPDATE STATISTICS [APEXProduction].[dbo].[ACL_PRINCIPAL] [ACL_PRINCIPAL_PK] [SQLSTATE 01000] (Message 50000) Outcome: Succeeded Duration: 00:00:01 DateTime: 2012-02-25 22:00:49 [SQLSTATE 01000] (Message 50000) DateTime: 2012-02-25 22:00:49 Command: UPDATE STATISTICS [APEXProduction].[dbo].[ACL_PRINCIPAL] [_WA_Sys_00000004_778AC167] [SQLSTATE 01000] (Message 50000) Outcome: Succeeded Duration: 00:00:00 DateTime: 2012-02-25 22:00:49 [SQLSTATE 01000] (Message 50000... The step failed.
Really not able to find the definiton of the sp ... any help where to look for it ?
March 1, 2012 at 8:43 am
Mmmm... that's not too helpful. Try running the proc from the SSMS so that the messages don't get truncated.
You'll find the proc in the EDMDefault database. Expand the database, then Programmability, then Stored Procedures, then right-click on IndexOptimize and choose Modify. Either that or run USE EDMDefault, then EXEC sp_helptext IndexOptimize.
John
March 2, 2012 at 9:30 am
This looks like my index maintenance stored procedure.
http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
Could you check if you have output - files configured in the job?
http://msdn.microsoft.com/en-us/library/ms188952.aspx
Ola Hallengren
March 5, 2012 at 3:11 am
Yup , i guess so 🙂
Ill check it out and let you noe .
Thanks in advance !
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply