Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
laddu
Constraint Violating Yak Guru
332 Posts |
 Posted - 2007-12-04 : 21:27:16
|
| SQL Server 2005 version: 2153I created a maintplan for system and user databases includes rebuild index, maint cleanup tasks.Job is failing for user databases It includes rebuild index task( online index enabled) and maintenance cleanup task, scheduled at every sunday 1 AM.I receive following errors:In eventvwr logsql server scheduled job 'DBMP_RebuildIndex_User'status: failed-Invoked on 2007-12-02 -1:00 Message: The job failed. The job was invoked by schedule 8 ('DBMP_RebuildIndex_User-Schedule).The last step to run was step1 ('DBMP_RebuildIndex_User')[/red]In log report:Failed:(-1073548784) Excuting the query "ALTER INDEX [XPKact_log] ON[dbo].[act log] REBUILD WITH (PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON,SORT_IN_TEMPDB=OFF,ONLINE=ON)"failed with the following error "Online index operation cannot be performed for index 'XPKact_log' because the index contains column 'action_desc' of data type text, ntext.image.varchar(max),varbinary(max) or xml. For non clusterd index the column could be an include column of the index. for clusterd index it could be any column of the table .Incase of drop_existing the cloumn could be part of new or old index. The operation must be performed offline". Possible failure reasons : Problems with the querey .'" Resultset" property not set correctly, parameters not set correctly, or connection not established correctly.Please anyone help me on this?I really appriciateThnks |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-12-04 : 22:44:01
|
| you can't perform an online index rebuild on a cluster index that has a text, xml, blob, etc. column. pretty straightforward.Instead of using maintenance plans - which were really bad in SQL2K, but are still worthless in 2K5 - use Tara's rebuild script instead.go here for more info: http://weblogs.sqlteam.com/tarad/look for isp_ALTER_INDEX. she also has a helper script posted that you can use in a SQLAgent job that will run the rebuild script against all databases. Best part is that it is smart and won't try an online rebuild of a cluster index with a text column.While you are there you should also check out the other scripts she has posted. This will save you a bunch of time.-ec |
 |
|
|
|
|
|
|
|
SQL 2005 JOB contains Rebuild Index failing