sqlmaint.exe failed for optimization

[colinbell]

I'm running a Database Maintenance Plan for integrity, optimization and backup of 3 databases. The optimization fails and I know it is the Training48 Database because when I remove that from the Plan it works !
I have tried EXEC sp_resetstatus 'Training48' but it set to zero anyway. I have tried taking it offline and back online - still no joy ?

any ideas much appreciated
richard

sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.

 

[Corran007]

Take it out and set it up in a new plan. open the actual job that runs the optimization part and open the job step. cut and paste the sql in the job step into QA. Run that. When it outputs the results, you will be able to see what actual error it is producing. This should give you a better chance to solve it. If you need help come back here, were always willing to give a hand.

 

[colinbell]

thanks Corran

I had a computed column on the membership table, what i've had to do is run a seperate TSQL job to replicate the optimization.

what other thing though is through the Database Maintenance Plan - when you set the reorganize data and index pages on the optimizations tab - the change free space per page percentage is set to 10%

when i run the separate job as below - is the 10 within here the same as above ?
DBCC DBREINDEX(@TableName,' ',10)


USE Training48 --Enter the name of the database you want to reindex

DECLARE @TableName varchar(255)

DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT "Reindexing " + @TableName
DBCC DBREINDEX(@TableName,' ',10)
FETCH NEXT FROM TableCursor INTO @TableName
END

CLOSE TableCursor

DEALLOCATE TableCursor