SQL Server missing tables and stored procedures

Question

I have an application on a client's site that processes data each night, last night SQL Server 2005 gave the error "Could not find stored procedure 'xxxx'". The stored procedure does exist in the database, has the right permission as far as I can tell, the application runs fine in other nights as well.

In previous occasions, the SQL Server has also gave error saying 'database object not found', and refers to a table in the database that does exists.

So, on rare occasions, the server thinks certain stored procedures and tables does not exist in the database. The objects it refers to are often ones that are frequently used.

Is the database somehow corrupted, is there some sort of repair/health check I can do?

Answer 1

I would try using SQL Database Recovery tool (you can download a trial for free) at http://www.mssqldatabaserecovery.com/. It uses high-end scanning mechanisms to ensure in-depth scanning of damaged database and complete data retrieval and it's really easy to use I think. That may be able to tell you what is causing the issues. I know messed up stored procedures have the potential to corrupt your whole database when they go missing or seeminly dissappear like in your case and then it gets ugly.

Good luck!