Reindex Task Failing Randomly [closed]

Question

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.

Answer 1

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.