Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We use the built-in maintenance plan to reindex (and backup, etc.) our production SQL Server. The problem we are currently having is the reindex task started failing randomly this week after months of smooth operation. It has failed three out of the last six nights (it runs nightly), and it always fails on the ASPNETDB database. This database is Microsoft's ASP.Net user/role database (which I hate BTW, but that's another story).

If I remove the failing database from the list of reindexes database, the task succeeds. If I remove all other databases except the failing database, the task still succeeds. If I follow the above two steps, then immediately run the task as normal, it succeeds. I see no rhyme or reason to this.

When the task fails, the log message looks like what I have pasted below (no errors that I can see). The message always ends with the last statement being "ALTER INDEX [PK__aspnet_P__32...".

Any thoughts what might be going on here?

Message Executed as user: mydbname\i2SQLclstsvc. ...0.2500.0 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 8:46:39 PM Progress: 2012-03-15 20:46:40.33 Source: {3D0CB67E-0B04-419A-BA8F-CDD8F0519612} Executing query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp...".: 100% complete End Progress Progress: 2012-03-15 20:46:54.74 Source: Rebuild Index Task Executing query "USE [ASPNETDB] ".: 2% complete End Progress Progress: 2012-03-15 20:46:54.76 Source: Rebuild Index Task Executing query "ALTER INDEX [aspnet_Applications_Index] ON [dbo].[...".: 4% complete End Progress Progress: 2012-03-15 20:46:54.76 Source: Rebuild Index Task Executing query "USE [ASPNETDB] ".: 6% complete End Progress Progress: 2012-03-15 20:46:54.76 Source: Rebuild Index Task Executing query "ALTER INDEX [PK__aspnet_A_C93A4C98014935CB] ON [d...".: 9% complete End Progress Progress: 2012-03-15 20:46:54.76 Source: Rebuild Index Task Executing query "USE [ASPNETDB] ".: 11% complete End Progress Progress: 2012-03-15 20:46:54.76 Source: Rebuild Index Task Executing query "ALTER INDEX [UQ_aspnet_A_17477DE40425A276] ON [d...".: 13% complete End Progress Progress: 2012-03-15 20:46:54.76 Source: Rebuild Index Task Executing query "USE [ASPNETDB] ".: 15% complete End Progress Progress: 2012-03-15 20:46:54.77 Source: Rebuild Index Task Executing query "ALTER INDEX [UQ_aspnet_A__3091033107020F21] ON [d...".: 18% complete End Progress Progress: 2012-03-15 20:46:54.77 Source: Rebuild Index Task Executing query "USE [ASPNETDB] ".: 20% complete End Progress Progress: 2012-03-15 20:46:54.83 Source: Rebuild Index Task Executing query "ALTER INDEX [aspnet_Membership_index] ON [dbo].[as...".: 22% complete End Progress Progress: 2012-03-15 20:46:54.83 Source: Rebuild Index Task Executing query "USE [ASPNETDB] ".: 25% complete End Progress Progress: 2012-03-15 20:46:54.87 Source: Rebuild Index Task Executing query "ALTER INDEX [PK__aspnet_M__1788CC4D1FCDBCEB] ON [d...".: 27% complete End Progress Progress: 2012-03-15 20:46:54.87 Source: Rebuild Index Task Executing query "USE [ASPNETDB] ".: 29% complete End Progress Progress: 2012-03-15 20:46:54.87 Source: Rebuild Index Task Executing query "ALTER INDEX [aspnet_Paths_index] ON [dbo].[aspnet_...".: 31% complete End Progress Progress: 2012-03-15 20:46:54.87 Source: Rebuild Index Task Executing query "USE [ASPNETDB] ".: 34% complete End Progress Progress: 2012-03-15 20:46:54.87 Source: Rebuild Index Task Executing query "ALTER INDEX [PK__aspnet_P_CD67DC5859063A47] ON [d...".: 36% complete End Progress Progress: 2012-03-15 20:46:54.87 Source: Rebuild Index Task Executing query "USE [ASPNETDB] ".: 38% complete End Progress Progress: 2012-03-15 20:46:54.87 Source: Rebuild Index Task Executing query "ALTER INDEX [PK_aspnet_P__CD67DC5960A75C0F] ON [d...".: 40% complete End Progress Progress: 2012-03-15 20:46:54.87 Source: Rebuild Index Task Executing query "USE [ASPNETDB] ".: 43% complete End Progress Progress: 2012-03-15 20:46:54.87 Source: Rebuild Index Task Executing query "ALTER INDEX [aspnet_PersonalizationPerUser_index1]...".: 45% complete End Progress Progress: 2012-03-15 20:46:54.87 Source: Rebuild Index Task Executing query "USE [ASPNETDB] ".: 47% complete End Progress Progress: 2012-03-15 20:46:54.87 Source: Rebuild Index Task Executing query "ALTER INDEX [aspnet_PersonalizationPerUser_ncindex...".: 50% complete End Progress Progress: 2012-03-15 20:46:54.87 Source: Rebuild Index Task Executing query "USE [ASPNETDB] ".: 52% complete End Progress Progress: 2012-03-15 20:46:54.87 Source: Rebuild Index Task Executing query "ALTER INDEX [PK__aspnet_P__32... The package execution fa... The step failed.

share|improve this question

closed as off-topic by Max Vernon, Paul White, Mark Storey-Smith, bluefeet, RolandoMySQLDBA Oct 31 '13 at 13:33

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Max Vernon, Paul White, Mark Storey-Smith, bluefeet, RolandoMySQLDBA
If this question can be reworded to fit the rules in the help center, please edit the question.

1  
What could be happening is that you are droping the index PK__aspnet_P__3214EC06778AC167 in your maintenance plan. I believe the list of indexes to get rebuilt is created prior to the start, so if that table or index (the table is actually aspnet_PersonalizationPerUser) is getting dropped, executing the ALTER INDEX PK__aspnet_P__3214EC06778AC167 on aspnet_PersonalizationPerUser rebuild ... will fail. Check to see what is happening with that index/table through the generated plan. I bet you'll find something odd like that. –  Thomas Stringer Mar 16 '12 at 2:53
    
Have you looked to see if there is anything else that's running around the time the maintenance plan fails? –  DForck42 Mar 16 '12 at 20:36
    
Are you seeing deadlocks while index maintenance runs? (I've seen this happen before.) Is the rebuild task set to rebuild online, and are you running an edition of SQL Server that supports online rebuilds? Have you considered doing an index reorganization on this database instead of a full rebuild (this is always an online operation)? –  Jon Seigel Jul 15 '12 at 15:58
    
Try to manually re-index that database and see if that come up with the same error. –  Zane Jul 26 '12 at 18:32
    
run a checkDB on each database manually. DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS –  user9399 Jul 31 '12 at 18:41
add comment

2 Answers 2

A known reason for reindex failure is a full log. Depending on your recovery model, the frequency at which you backup your log and the activity on your database, you might have "random" crashes. If you monitor the size of your log or have access to you server ERRORLOG you should be able to validate this hypothesis and take appropriate measures if it's validated.

share|improve this answer
add comment

Instead of using the built in Maintenance Plan to rebuild indexes you might want to consider using a stored proc to accomplish the same thing. The advantage is you can control which indexes are rebuilt, and which are simply re-organized, according to logic that makes sense for your particular database.

You may want to check out Ola Hallengren's excellent database maintenance tools available for free at: http://ola.hallengren.com/

Brent Ozar also has an excellent article on why you may not want to even bother with reindexing: http://www.brentozar.com/archive/2012/08/sql-server-index-fragmentation/

share|improve this answer
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.