July 24, 2006 at 8:27 am
I have been running maintenance plans on several databases on our servers for a long time. One of the plans failed in its weekly run yesterday.
The optimization plan failed with the following error:
Executed as user: DOMAIN\SQLAdmin. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.
The integrity plan which runs after the optimization job completed successfully.
I checked the logs to see what type of error was responsible for the job failing. The logs show that the job was rebuilding indexes one table at a time. After quite a few tables were rebuilt, it suddenly generated this error:
*****
[Microsoft SQL-DMO (ODBC SQLState: HY000)] Error 5180: [Microsoft][ODBC SQL Server Driver][SQL Server]Could not open FCB for invalid file ID 3329 in database 'PROD'.
** Execution Time: 2 hrs, 55 mins, 55 secs **
Deleting old text reports... 1 file(s) deleted.
End of maintenance plan 'PROD new' on 7/23/2006 4:55:56 AM
*****
I checked the log file for the earlier week and that job had run successfully. After rebuilding all the indexes on the tables, it generated the following ending.
*****
** Execution Time: 3 hrs, 16 mins, 35 secs **
Deleting old text reports... 1 file(s) deleted.
End of maintenance plan 'PROD new' on 7/16/2006 5:16:37 AM
SQLMAINT.EXE Process Exit Code: 0 (Success)
*****
I checked to make sure that there was enough space on the servers.
I also checked to see that all the older logs were deleted as specified in the plan.
This database is in production, so I cannot run the DBCC CHECKDB as I would have to do so in single user mode. I also think that running CHECKDB would not give me any errors as the Integrity check was completed successfully in the maintenance plan.
I also checked to make sure that the directory does not have the –S as was reported by Microsoft about problems with the generated error number.
Anyone have any ideas or suggestions???
Thanks in advance...
July 24, 2006 at 9:56 am
What error did the Maintenance plan history show?
July 24, 2006 at 10:17 am
The error listed in the maintenance plan history under the activity of "rebuild indexes" properties, is as under:
[Microsoft SQL-DMO (ODBC SQLState: HY000)] Error 5180: [Microsoft][ODBC SQL Server Driver][SQL Server]Could not open FCB for invalid file ID 3329 in database 'PROD'.
July 24, 2006 at 10:30 am
Check the following link:
For SQL Server 2000
http://support.microsoft.com/kb/276043/en-us
For SQL Server 7.0
July 24, 2006 at 2:42 pm
One of the solutions to this was to upgrade to the latest patch. I checked to see and founds that we are at the latest in patches on our Server 2000.
The other talked about dirty reads and I could not understand what that entailed. I also checked and found that the table where the error occured has about 73 million records and I don't know if that may be one of the problems.
Thanks
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply