asked
I am trying to upgrade TFS 2010 SP1 to TFS 2013. I have 5 collections and 4 of them upgraded successfully except the 5th one which fails with an error message "Possible Schema Corruption".
I run dbcc checkdb on the problematic collection database and see the following error messages: Msg 0, Level 11, State 0, Line 0 A severe error occurred on the current command. The results, if any, should be discarded. Msg 0, Level 20, State 0, Line 0 A severe error occurred on the current command. The results, if any, should be discarded. The error reported in SQL Logs are: Error: 211, Severity: 23, State: 113. Possible schema corruption. Run DBCC CHECKCATALOG. When I run "DBCC CHECKCATALOG" it shows the results "DBCC execution completed. If DBCC printed error messages, contact your system administrator." I further investigated the problematic collection database and found that the problem is only with the stored procedures. I cannot see the list of stored procedures for the problematic collection database. All other collection databases are fine and I can see the list of their SPs without any issue. 1. There are 729 stored procedures in each and every collection databases and all the stored procedures are encrypted. 2. I can select all the stored procedures from all 4 out of 5 collections database with the help of below query. But when I run this query on the problematic collection database it returns 301 stored procedures and then give the error "Possible Schema corruption error". This query should return 729 rows. select * from information_schema.routines where routine_type = 'Procedure' Please note that the below query return 729 for all the 5 collection databases including the problematic collection database. select count(*) from information_schema.routines where routine_type = 'Procedure' 3. Is there any way to rebuild the schema of this particular collection database? 4. All the stored procedure are locked (Encrypted) and there is lock sign with all the SPs and I cannot get the definition of these stored procedures. Important is: 1. I can take the backup of the problematic collection database and restore it without any problem. 2. Team members are using the projects inside the problematic collection databases without any issues. Below is the detailed error message when I click on the Stored Procedures node in the Management studio. Possible schema corruption. Run DBCC CHECKCATALOG. A severe error occurred on the current command. The results, if any, should be discarded. (.Net SqlClient Data Provider) ------------------------------ For help, click: "
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=211&LinkId=20476" ------------------------------ Server Name: (local) Error Number: 211 Severity: 23 State: 113 Line Number: 9
With a suspect database and one where you're not able to get a specific, simple fix, such as a corrupt nonclustered index, the best solution is to go to the last good backup and restore the database. A good backup by the way is one that is not corrupted. That would assume that you're testing your database consistency as part of your backups. If you don't have any backups, you're going to have to dig further to see if you can identify specifically where the corruption may be and see if dropping any given object relieves the issue. It may not, in fact, I would assume it will not, get the backup in place. It's also possible you have a problem on the disk or the disk controller. You could try backing up the database and restoring it to a completely different location (at least a different disk if on the same server, or a different server). But your simple best bet is a restore from a good backup.
Hello Grant, The problem is only with the stored procedures. I do not have a backup to recover these SPs as in all backups they are not viewable at all. I tried restoring the database on a other server and the result is same means no issue with the disk. Tables, views, function and all data inside the tables are 100% fine. The problem is with the stored procedures And I am unable to view them.
Then I have bad news. That database is likely toast. If you can still access the data, time to export it all and then import it into a new database. Can you recreate the stored procedures from another source, reinstall the software, something along those lines? Might want to get DBCC added as part of your backup routine as well.
15 People are following this question.
