November 8, 2010 at 4:30 am
I am bit confused with which job to be set first or the order of the jobs for Reorganize Index, Database Integrity check, Update Statistics
I feel the order should be as below
1.Reorganize Index,
2.Update Statistics
3.Database Integrity check.
And ' Regorganize Index job' keeps failing without any reason there is abosloutely no error in the error or evenlogs/job history for the failure the error looks like this
'Executed as user: abz\xyz. ...[AllDoc_IdLevelUnique] ON [dbo]".: 2% complete End Progress Progress: 2010-11-06 10:36:25.95 Source: Reorganize Index Executing query "USE [db_name] ".: 2% complete End Progress Progress: 2010-11-06 10:36:26.03 Source: Reorganize Index Executing query "ALTER INDEX [AllDoc_SiteIdId] ON [dbo].[All".: 2% complete End Progress Progress: 2010-11-06 10:36:26.03 Source: Reorganize Index Executing query "USE [db_name] ".: 2% complete End Progress Progress: 2010-11-06 10:36:26.20 Source: Reorganize Index Executing query "ALTER INDEX [AllDoc_PK] ON [dbo].[AllDocVe".: 2% complete End Progress Progress: 2010-11-06 10:36:26.20 Source: Reorganize Index Executing query "USE [db_name] ".: 3% complete End Progress Progress: 2010-11-06 10:36:28.47 Source: Reorganize Index Executing query "ALTER INDEX [Link... The package execution fa... The step failed.'
Any help will be much appericiated!
Thanks
November 8, 2010 at 4:44 am
Sqlsavy (11/8/2010)
I am bit confused with which job to be set first or the order of the jobs for Reorganize Index, Database Integrity check, Update StatisticsI feel the order should be as below
1.Reorganize Index,
2.Update Statistics
3.Database Integrity check.
And ' Regorganize Index job' keeps failing without any reason there is abosloutely no error in the error or evenlogs/job history for the failure the error looks like this
'Executed as user: abz\xyz. ...[AllDoc_IdLevelUnique] ON [dbo]".: 2% complete End Progress Progress: 2010-11-06 10:36:25.95 Source: Reorganize Index Executing query "USE [db_name] ".: 2% complete End Progress Progress: 2010-11-06 10:36:26.03 Source: Reorganize Index Executing query "ALTER INDEX [AllDoc_SiteIdId] ON [dbo].[All".: 2% complete End Progress Progress: 2010-11-06 10:36:26.03 Source: Reorganize Index Executing query "USE [db_name] ".: 2% complete End Progress Progress: 2010-11-06 10:36:26.20 Source: Reorganize Index Executing query "ALTER INDEX [AllDoc_PK] ON [dbo].[AllDocVe".: 2% complete End Progress Progress: 2010-11-06 10:36:26.20 Source: Reorganize Index Executing query "USE [db_name] ".: 3% complete End Progress Progress: 2010-11-06 10:36:28.47 Source: Reorganize Index Executing query "ALTER INDEX [Link... The package execution fa... The step failed.'
Any help will be much appericiated!
Thanks
Post me the maintenance plan history
By right click the maintenance plan -->view the history
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
November 8, 2010 at 4:48 am
I had already posted job history , posting it agin for your refrence...
Date06/11/2010 09:09:00
LogJob History (UserDB.ReorgIndexes)
Step ID1
ServerServername
Job NameUserDB.ReorgIndexes
Step NameReorgIndexes
Duration05:00:39
Sql Severity0
Sql Message ID0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
Message
'Executed as user: abz\xyz. ...[AllDoc_IdLevelUnique] ON [dbo]".: 2% complete End Progress Progress: 2010-11-06 10:36:25.95 Source: Reorganize Index Executing query "USE [db_name] ".: 2% complete End Progress Progress: 2010-11-06 10:36:26.03 Source: Reorganize Index Executing query "ALTER INDEX [AllDoc_SiteIdId] ON [dbo].[All".: 2% complete End Progress Progress: 2010-11-06 10:36:26.03 Source: Reorganize Index Executing query "USE [db_name] ".: 2% complete End Progress Progress: 2010-11-06 10:36:26.20 Source: Reorganize Index Executing query "ALTER INDEX [AllDoc_PK] ON [dbo].[AllDocVe".: 2% complete End Progress Progress: 2010-11-06 10:36:26.20 Source: Reorganize Index Executing query "USE [db_name] ".: 3% complete End Progress Progress: 2010-11-06 10:36:28.47 Source: Reorganize Index Executing query "ALTER INDEX [Link... The package execution fa... The step failed.'
November 8, 2010 at 5:00 am
Sqlsavy (11/8/2010)
I had already posted job history , posting it agin for your refrence...Date06/11/2010 09:09:00
LogJob History (UserDB.ReorgIndexes)
Step ID1
ServerServername
Job NameUserDB.ReorgIndexes
Step NameReorgIndexes
Duration05:00:39
Sql Severity0
Sql Message ID0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
Message
'Executed as user: abz\xyz. ...[AllDoc_IdLevelUnique] ON [dbo]".: 2% complete End Progress Progress: 2010-11-06 10:36:25.95 Source: Reorganize Index Executing query "USE [db_name] ".: 2% complete End Progress Progress: 2010-11-06 10:36:26.03 Source: Reorganize Index Executing query "ALTER INDEX [AllDoc_SiteIdId] ON [dbo].[All".: 2% complete End Progress Progress: 2010-11-06 10:36:26.03 Source: Reorganize Index Executing query "USE [db_name] ".: 2% complete End Progress Progress: 2010-11-06 10:36:26.20 Source: Reorganize Index Executing query "ALTER INDEX [AllDoc_PK] ON [dbo].[AllDocVe".: 2% complete End Progress Progress: 2010-11-06 10:36:26.20 Source: Reorganize Index Executing query "USE [db_name] ".: 3% complete End Progress Progress: 2010-11-06 10:36:28.47 Source: Reorganize Index Executing query "ALTER INDEX [Link... The package execution fa... The step failed.'
i am not asking the Job History (UserDB.ReorgIndexes)
Can you post the maintenance paln History.
management-->maintenance plan-->right click the M.P and check the history and finally post the error.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
November 8, 2010 at 5:05 am
Thanks Muthukkumara,
I could figure out the reason for the job failure from the maintenance plan history as you adviced .The error is..
Executing the query "ALTER INDEX [PK_Act] ON [dbo].[_Act] REORGANIZE WITH ( LOB_COMPACTION = OFF )
" failed with the following error: "The index "PK_Act" (partition 1) on table "_Act" cannot be reorganized because page level locking is disabled.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
November 8, 2010 at 5:15 am
Sqlsavy (11/8/2010)
Thanks Muthukkumara,I could figure out the reason for the job failure from the maintenance plan history as you adviced .The error is..
Executing the query "ALTER INDEX [PK_Act] ON [dbo].[_Act] REORGANIZE WITH ( LOB_COMPACTION = OFF )
" failed with the following error: "The index "PK_Act" (partition 1) on table "_Act" cannot be reorganized because page level locking is disabled.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I'm glad I could help you.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
November 8, 2010 at 6:33 am
Thanks for that but
I am bit confused with which job to be set first or the order of the jobs for Reorganize Index, Database Integrity check, Update Statistics
I feel the order should be as below
1.Reorganize Index,
2.Update Statistics
3.Database Integrity check.
November 8, 2010 at 6:45 am
Sqlsavy (11/8/2010)
Thanks for that butI am bit confused with which job to be set first or the order of the jobs for Reorganize Index, Database Integrity check, Update Statistics
I feel the order should be as below
1.Reorganize Index,
2.Update Statistics
3.Database Integrity check.
yep its correct order.
What is schedule for those jobs weekly or daily ?
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
November 8, 2010 at 7:03 am
weekly
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply