SQL Server Performance Forum – Threads Archive

Reindex Failed

I have scheduled a reindex which is failed on last sunday. I didn’t find any reason for the failure. I have checked the event viewer and sql server error log also. What could be the reason for this failure? Note: There is a job which takes database log backup for every one hour, does it affect my reindex process which also ran simultaneously? Thanks,
Bala

Right click on that job and view job history, then click on details to see why it is failed. Is it in SQL 2000 or 2005? Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

It is SQL 2005

Backup of Transactional log may be the cause of job failure but it is not hard and fast rule. I also facing such situation where some time the IndexDefreg job fails and many time completed successfully. I think there is some type of deadlock between dbcc thread and backup log thread that happens occasionally. Thanks,
Subhash

The transaction log backup job will not clash with reindex in this case, fyihttp://sqlserver-qa.net/blogs/tools…server-index-optimization-best-practices.aspx in this csae. Fo the job failure, have you looked at that specified job history? Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Do you change your recovery model from ‘Full’ to ‘Simple’ in your reindex job because you are trying to save log space? I’m thinking if the tran log is running when the reindex job kicks in and it tries to change the recovery model then it may cause the reindex job to fail. Jeff

]]>