September 10, 2013 at 4:44 am
Hi
I have 2 jobs from a maintenance plan and 1 backup job from sqlbackup (redgate). The job are scheduled as follows:
Sunday 23:00Hrs - The Rebuild indexs on all user databases
Sunday 23:15Hrs - The system databases Integrity check
Sunday 23:15Hrs - The sqlbackup job of all system db's
I have noticed that the 1st job to rebuild all user database indexes is running in less than 1 minute and I am thinking this should probably take longer. The job succeeds but when I look in the log file for the job history I see something like this:
Source: Rebuild Index Task Executing query "ALTER INDEX [PK_ABC_XXX...".: 8% complete End Progress Progress: 2013-09-08 23:00:14.91 Source: Rebuild Index Task Executing query "USE [ABC] ".: 8% complete End Progress Progress: 2013-09-08 23:00:14.92 Source: Rebuild Index -
Because I am not seeing 100% and only 8% complete does this mean the alter index statement is not completing the index rebuild task fully ? I was thinking this may be the case as either the the backup job somehow interferes with the 23:00hr job ?
Any ideas on as to why the index rebuild task runs so quickly ?
September 10, 2013 at 8:04 am
Any ideas on as to why the index rebuild task runs so quickly ?
Either have a few really small indexes, a blazing fast server or not all the indexes are really being rebuilt. I would double check your index rebuild task to make sure that all databases/indexes are selected. You could also manually do a rebuild on your biggest tables and see how long it takes.
I have found it better to create a script for index rebuild tasks and configure it to log to a table. It's also a good idea to first check the fragmentation level and perform a re-org, rebuild or do nothing based on the results (something you can't do through the maintenance plan GUI).
-- Itzik Ben-Gan 2001
September 10, 2013 at 8:57 am
Use htis: http://sqlfool.com/2011/06/index-defrag-script-v4-1/ in place of the maintenance plan index rebuild. It's intelligent and works better.
September 10, 2013 at 9:32 am
Thanks guys.
Yeah i have seen that script and i have a copy of it ready to go but i dont want to implement it just yet. This is something I will get around to in the next few weeks or sooner if needed.
Can anybody shed any light on my question about the message i see in the logs pertaining to the fact that only 8% shows as complete - does this mean it only needed to do 8% or should i be seeing 100% for all indexes ?
September 10, 2013 at 9:52 am
All you're seeing there is truncated output.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 10, 2013 at 9:58 am
what do you mean by 'truncated output' ? Where am i looking for that ?
Thanks
September 10, 2013 at 12:48 pm
I mean exactly that. The output that you are looking at (that you've been posting) is truncated (cut off)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 11, 2013 at 3:20 am
Gail
I didnt copy the whole of the error log as i thought it would look clutered. I just showed what I thought people might need to see. Below is an example though if you need to see more......
Date08/09/2013 23:00:00
LogJob History (AP - Weekly (Sun2300hrs) - User Databases - Rebuild Indexes.Rebuild Indexes (Multi-Server))
Step ID1
ServerSERVER01
Job NameBA - Weekly (Sun2300hrs) - User Databases - Rebuild Indexes.Rebuild Indexes (Multi-Server)
Step NameRebuild Indexes
Duration00:02:05
Sql Severity0
Sql Message ID0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
Message
Executed as user: AAA\Admin. ...8 23:00:10.11 Source: Rebuild Index Task Executing query "USE [Abc] ".: 7% complete End Progress Progress: 2013-09-08 23:00:10.13 Source: Rebuild Index Task Executing query "ALTER INDEX [PK_Abc_Lookup_Cont_FAQsr...".: 7% complete End Progress Progress: 2013-09-08 23:00:10.13 Source: Rebuild Index Task Executing query "USE [Abc] ".: 7% complete End Progress Progress: 2013-09-08 23:00:10.16 Source: Rebuild Index Task Executing query "ALTER INDEX [PK_Abc_Lookup_Cont_FAQsTi] O...".: 7% complete End Progress Progress: 2013-09-08 23:00:10.16 Source: Rebuild Index Task Executing query "USE [Abc] ".: 8% complete End Progress Progress: 2013-09-08 23:00:10.20 Source: Rebuild Index Task Executing query "ALTER INDEX [PK_Abc_LookUp_Content_Legislation] ...".: 8% complete End Progress Progress: 2013-09-08 23:00:10.20 Source: Rebuild Index Task Executing query "USE [Abc] ".: 8% complete End Progress Progress: 2013-09-08 23:00:10.21 Source: Rebuild Index Task Executing query "ALTER INDEX [PK_Abc_LookUp_Content_LegislationBy...".: 8% complete End Progress Progress: 2013-09-08 23:00:10.21 Source: Rebuild Index Task Executing query "USE [Abc] ".: 9% complete End Progress Progress: 2013-09-08 23:00:10.24 Source: Rebuild Index Task Executing query "ALTER INDEX [PK_Abc_LookUp_Content_Subjects2] ON...".: 9% complete End Progress Progress: 2013-09-08 23:00:10.24 Source: Rebuild Index Task Executing query "USE [Abc] ".: 9% complete End Progress Progress: 2013-09-08 23:00:10.25 Source: Rebuild Index Task Executing query "ALTER INDEX [PK_Abc_LookUp_Content_T] ON [dbo...".: 9% complete End Progress Progress: 2013-09-08 23:00:10.25 Source: Rebuild Index Task Executing query "USE [Abc] ".: 10% complete End Progress Progress: 2013-09-08 23:00:10.30 Source: Rebuild Index Task Executing query "ALTER INDEX [PK_Abc_LookUp_Content_Top2] ON [...".: 10% complete End Progress Progress: 2013-09-08 23:00:10.30 Source: Rebuild Index Task Executing query "USE [Abc] ".: 10% complete End Progress Progress: 2013-09-08 23:00:10.40 Source: Rebuild Index Task Executing query "ALTER INDEX [PK_Abc_Look_Cont_TopicSus2] O...".: 10% complete End Progress Progress: 2013-09-08 23:00:10.40 Source: Rebuild Index Task Executing query "USE [Abc] ".: 11% complete End Progress Progress: 2013-09-08 23:00:10.42 Source: Rebuild Index Task Executing query "ALTER INDEX [PK_Abc_Look_Default] ON [dbo]....".: 11% complete End Progress Progress: 2013-09-08 23:00:10.42 Source: Rebuild Index Task Executing query "USE [Abc] ".: 11% complete End Progress Progress: 2013-09-08 23:00:10.46 Source: Rebuild Index Task Executing query "ALTER INDEX [PK_ServiceGroupDocs] ON [dbo].[A...".: 12% complete End Progress Progress: 2013-09-08 23:00:10.46 Source: Rebuild Index Task Executing query "USE [Abc] ".: 12% complete End Progress Progress: 2013-09-08 23:00:10.48 Source: Rebuild Index Task Executing query "ALTER INDEX [PK_Abc_Look_ServiceGrs] ON [db...".: 12% complete End Progress Progress: 2013-09-08 23:00:10.49 Source: Rebuild Index Task Executing query "USE [Abc] ".: 12% complete End Progress Progress: 2013-09-08 23:00:10.50 Source: Rebuild Index Task Executing query "ALTER INDEX [PK_ServiceGroupSecs] ON [dbo].[AP...".: 13% complete End Progress Progress: 2013-09-08 23:00:10.50 Source: Rebuild Index Task Executing query "USE [Abc] ".: 13% complete End Progress Progress: 2013-09-08 23:00:10.50 Source: Rebuild Index Task Executing query "ALTER INDEX [PK_Abc_LookUp_Sers] ON [dbo].[A...".: 13% complete End Progress Progr... The package executed successf... The step succeeded.
So back to my question.....after looking at the above, does the fact that i'm not seeing 100% for the percentages mean that the index is only getting partially rebuilt - or is this by design because it only needs say a small percentage rebuilding ?
September 11, 2013 at 7:39 am
That output is truncated.
I wasn't saying you truncated it. I'm saying that the package output, as shown in the job step history, is truncated. It's cut off when it's logged. Hence you can't draw any conclusions from just that, it's not complete.
An index rebuild maintenance plan, if it succeeds, will rebuild every single index in the database.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 11, 2013 at 8:54 am
ah ok then - that answers my question. I was concerned the index was not getting rebuilt properly but obviously from what you say, because the job completes, the indexes will be getting rebuilt.
Thanks again for your help.
🙂
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply