September 19, 2011 at 8:10 am
Hi there,
We have inherited the responsibility of administering a SQL Server 2008 Enterprise edition system, and
this is set up with a Maintenance plan which includes a weekly run of Index Maintenance.DBCC REINDEX.
However, this is currently failing. When I check the job activity monitor I can see that it fails after running for a couple of hours. I can also see that in the notifcations section, the job is set to write to the Windows Applications Log on failure.
When I look at the Windows Application Log at the time of the failure I can see the following entry:
Package "Index Maintenance" failed.
Log Name: Application
Source: SQLISPackage100
Date: 17/09/2011 04:06:13
Event ID: 12291
Task Category: None
Level: Error
Keywords: Classic
User: NETWORK SERVICE
Computer: <our server name>
Description:
Package "Index Maintenance" failed.
Event Xml:
<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
<System>
<Provider Name="SQLISPackage100" />
<EventID Qualifiers="16385">12291</EventID>
<Level>2</Level>
<Task>0</Task>
<Keywords>0x80000000000000</Keywords>
<TimeCreated SystemTime="2011-09-17T03:06:13.000Z" />
<EventRecordID>444167</EventRecordID>
<Channel>Application</Channel>
<Computer><our server name></Computer>
<Security UserID="S-1-5-20" />
</System>
<EventData>
<Data>Index Maintenance</Data>
</EventData>
</Event>
The problem is that this doesn't give me any details of why the job failed, so my question is, how can I find out more about the reason for the failure ? Also, would best practice be to schedule a bespoke index rebuild script rather than just using a generic maintenance plan as we have here ?
Apologies in advance if this is a basic question, but I have spent some time googling the issue without much success.
Cheers,
Dave
September 19, 2011 at 8:31 am
Have you looked at the job history?
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 19, 2011 at 8:32 am
dave_shipway (9/19/2011)
Hi there,We have inherited the responsibility of administering a SQL Server 2008 Enterprise edition system, and
this is set up with a Maintenance plan which includes a weekly run of Index Maintenance.DBCC REINDEX.
However, this is currently failing. When I check the job activity monitor I can see that it fails after running for a couple of hours. I can also see that in the notifcations section, the job is set to write to the Windows Applications Log on failure.
When I look at the Windows Application Log at the time of the failure I can see the following entry:
Package "Index Maintenance" failed.
Log Name: Application
Source: SQLISPackage100
Date: 17/09/2011 04:06:13
Event ID: 12291
Task Category: None
Level: Error
Keywords: Classic
User: NETWORK SERVICE
Computer: <our server name>
Description:
Package "Index Maintenance" failed.
Event Xml:
<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
<System>
<Provider Name="SQLISPackage100" />
<EventID Qualifiers="16385">12291</EventID>
<Level>2</Level>
<Task>0</Task>
<Keywords>0x80000000000000</Keywords>
<TimeCreated SystemTime="2011-09-17T03:06:13.000Z" />
<EventRecordID>444167</EventRecordID>
<Channel>Application</Channel>
<Computer><our server name></Computer>
<Security UserID="S-1-5-20" />
</System>
<EventData>
<Data>Index Maintenance</Data>
</EventData>
</Event>
The problem is that this doesn't give me any details of why the job failed, so my question is, how can I find out more about the reason for the failure ? Also, would best practice be to schedule a bespoke index rebuild script rather than just using a generic maintenance plan as we have here ?
Apologies in advance if this is a basic question, but I have spent some time googling the issue without much success.
Cheers,
Dave
Hi Dave,
There are a few places you can look for more detailed information. First, You can look at the "Job History". Connect to your sql server using SQL Server management Studio and expand the section called "SQL SErver Agent", then "Jobs". I bet you will find a job with Index Maintenance in the name. Right Click it and select View History. You can expand the failed jobs in history (they have a red X) and get some more detailed info.
This may not give you all the details you need. My guess is that your job is part of a "maintenance plan"... if so, you can find even more detailed information there. Again in SQL Server Managment Studio expand "Managment", then Maintenance Plans.... again, right click, view history. This will have similar, but probably a bit more detailed information.
Finally, there is a thrid source of information. You can configure each maintenance plan to "log to a file". If you open a maitnenance plan (right click, modify) at the very top near where it says "add subplan" you will see a series of icons. the 7th one from the left looks like two pages... click it to open the "reporting and logging" dialog. This wll show you the option to "generate a textfile report" and the location. If its already clicked, check out that location for a report. If not, click it, run the job, and the go digging into the log.
Once you have more info, if you still cant figure out what the problem is, post the error message here and we should be able to help nail it down.
September 19, 2011 at 8:44 am
Hi,
Well first of all thanks for the swift responses and the very useful information !
Having found further information in both the job history and the text report file that is generated by this job, I found the error:
[font="Courier New"]Executing the query "ALTER INDEX [load_gl_balances_delta_idx_A] ON [dbo..." failed with the following error: "Cannot find index 'load_gl_balances_delta_idx_A'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.[/font]
One thing to note is that there is a Update All Index Statistics job that is scheduled to run at the same time as the Index Maintenance.DBCC REINDEX and I am wondering if these are conflicting with each other.
Regards,
Dave
September 19, 2011 at 8:54 am
dave_shipway (9/19/2011)
Hi,Well first of all thanks for the swift responses and the very useful information !
Having found further information in both the job history and the text report file that is generated by this job, I found the error:
[font="Courier New"]Executing the query "ALTER INDEX [load_gl_balances_delta_idx_A] ON [dbo..." failed with the following error: "Cannot find index 'load_gl_balances_delta_idx_A'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.[/font]
One thing to note is that there is a Update All Index Statistics job that is scheduled to run at the same time as the Index Maintenance.DBCC REINDEX and I am wondering if these are conflicting with each other.
Regards,
Dave
Hmm, well, that error basically is saying "asked me to recreate an index that doesnt exist"
it may be that someone just manually created a list of indexes, and now some index no longer exists and needs to be removed from the job. Or it could be that some table or index is dropped and recreated - something like that.
what kind of job is this? Is this a maintenance plan, or some custom script with a series of statments like "alter index XYZ"...
I would say that if this is a custom TSQL script that hasnt been updated in a while you might do better to recreate the list of indexes that you are reindexing- or just remove this particular index 'load_gl_balances_delta_idx_A' from the list- or if that's beyond your level of expertise it might be easier to just replace the job with a maintenance plan.
September 19, 2011 at 9:21 am
Hello again,
Yes it is a Maintenance plan - its called "Index Maintenance".
If I click on the "Rebuild Index Task" in the Maintenance Plan flowchart the databases specified are "all user databases" so it looks like its doing everything.
I queried the sys.indexes system table and it appears that these indexes are rebuild every night by a some kind of scheduled job (which I haven't quite tracked down yet !) so I suspect that this is where the conflict is.
Anyway, I will investigate further, but thank you very much for youi help - I now know where to look !
Cheers,
Dave
September 19, 2011 at 9:27 am
dave_shipway (9/19/2011)
I queried the sys.indexes system table and it appears that these indexes are rebuild every night by a some kind of scheduled job (which I haven't quite tracked down yet !) so I suspect that this is where the conflict is.
The index rebuild job that you're investigating is probably the one that's rebuilding the indexes.
It shouldn't fail like that, as it will check what indexes exist before trying to rebuild.
Just to be safe, please run the following on the DB in question
DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS
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 19, 2011 at 9:38 am
GilaMonster (9/19/2011)
dave_shipway (9/19/2011)
I queried the sys.indexes system table and it appears that these indexes are rebuild every night by a some kind of scheduled job (which I haven't quite tracked down yet !) so I suspect that this is where the conflict is.The index rebuild job that you're investigating is probably the one that's rebuilding the indexes.
It shouldn't fail like that, as it will check what indexes exist before trying to rebuild.
Just to be safe, please run the following on the DB in question
DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS
My bet, based on the name of the table "...load..." is that this is what is happening:
your index maint job kicks off at say midnight, it creates a list of indexes which exist... some time later, say 1am, your "general ledger load" job kicks off, which then drops last night's load table. The index that existed when the index maint job started, now doesnt exist anymore. Your index job then gets to that part of the script it genereated and fails... your gl load job then proceeds and recreates that table and indexes just in time to fail again the following night.... admittedly lots of educated guesses here, but that's my theory.
September 19, 2011 at 10:36 am
I see something "similar" with our database maintenance plans when we remove a database. For some reason, it doesn't automatically get omitted from the backup plan (which has all user databases selected), and it fails the plan saying that the database does not exist.
Simply re-saving the plan without changing it works for us - it seems to force SQL Server into updating its list of databases to be backed up. Maybe the same thing will work for you...
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply