Recreate Full Text Index Job fails (sometimes...)

Question

Recreate Full Text Index Job fails (sometimes...)

Moreno-84941

Hall of Fame

Points: 3258
More actions
July 25, 2007 at 1:06 am

#117957

Hi
On a cluster server I have W2K3 64-bit with SQL 64-bit installed. There is a Job running every day to recreate the full text index. Sometimes only (this is what is killing me :angry I got this error
"File 'sysft_OnlineShopCatalog8_FullTextCatalog' cannot be reused until after the next BACKUP LOG operation. [SQLSTATE 42000] (Error 1833). The step failed."
I wasn't able to find something useful on the internet . The jobs start a store procedure coded like this
DECLARE
@return_value int
EXEC
@return_value = [dbo].[ctlg_CreateFullTextCatalog]
@CatalogName = N'OnlineShopCatalog',
@NumFullTextCatalogs
= 8,
@UseExistingFTCatalog
= 0,
@NewCatalog
= 1,
@SqlServerVersion
= 9
SELECT
'Return Value' = @return_value
This is not the only index being recreated. On the same instance I have two DB Test and PreProd. The job fails just for the PreProd
I do not really understand where the problem can be. The DB is small. The transaction log has 2 Gb allocated but just 1% used (when I look at it during the day). Has anybody an idea ? Could be that at the time the job is running the log is almost full and it wait the backup to release space ?
Best regards Moreno

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

steve smith-401573 SSChampion Points: 10889 More actions · Answer 1

1. There are other threads on the forums here that discuss similar issues to what you have posted.

2. What kind of backups are you taking to protect your databases? Are the backups different between Test and PreProd? If you change PreProd to look like Test (as far as backups are concerned), does the problem go away? If so, then you need to learn why what PreProd had set differently didn't work. Multiple styles of backing up a database work, but they have to be configured correctly.

If you give us more details about the context the error occurs in, we can be of more help to you. If you want us to help you, or if you want us just to point you in the correct direction to find the online resources, either way we can, if we know more about what you are doing now, and then what you need to accomplish.

Moreno-84941 Hall of Fame Points: 3258 More actions · Answer 2

We backup through maintenance plans. It is quite easy. Once a day a full backup is taken (7 pm). A transaction log backup is started every 3 hours starting from 00.00 am. The job I mentionned is started at 5:10 am. We have other jobs running but at different times. The maintenance plans apply to all the DB's of the instance.

I am pretty sure that the plans are configured correctly as I do not receive errors.

The only detail I have about the error is the one I posted. In the Logs is everything fine.

What is puzzling me is the fact that the error is not coming up all the time. For instance it work for 2 days and the 3rd day it fails.

Any kind of advice, also internet links will be appricieted