How To Fix A Corrupt Resource 32000 SQL Database
These notes explain how to deal with corrupt SQL Server databases. They are intended to be followed “parrot fashion? by a competent engineer or support person. If you require help then contact DSR Resource Support (assuming that the database in question is a Resource 32000 database).
- Make sure no-one is in Resource 32000
- Login to the NT server as administrator
- Go into the event log and check for any unusual occurrences around the time that the problem was reported. Examples include shutdown or restart of SQL Server at this time, or indeed NT Server shutdown or restart, or reports of network packet loss or failure. (Clearly start-ups without immediate prior shutdowns are of particular interest as this signifies server power loss or system crash)
- Stop and Restart SQL Server in the “traffic light? app, this on its own may correct certain forms of SQL Server corruption
- Go into enterprise manager and select the database, put it into single user mode which you can do by right-clicking on its name, then go to “Tools?, “SQL Query Tool?.
- Check the database that you are giving SQL Queries to is the correct one (in the dropdown at the top of the window). Various SQL-Server specific SQL queries are now relevant. Microsoft Knowledgebase Article Q140569 explains much of the following information:
- DBCC NEWALLOC checks the internal consistency of the database structures
- DBCC CHECKDB checks each table in the database for structural errors
- DBCC CHECKCATALOG checks the consistency of the information in the SQL Server system tables
- DBCC TEXTALL checks all memo field linkages in every table
- DBCC CHECKIDENT (tablename) checks the identity (serial number) column value is correct for the named table. SQL Server does not supply a “whole database? version of this comment unfortunately. Do it for the following tables at least: z_sloggedin, z_suselog, z_slocks, r32transaction, detail, matching, jcdraftheader, jcdraftinv, jcinvoice, siinvoicehead, siinvoiceline, soheader, soline, soprogress
- put the database back into multi-user mode
- Go to the user screens.
- Log in as someone senior enough to clear out the “Who Is Logged In?, “What’s Running?, and “What’s Locked? screens, and clear them.
- Check that any process that was failing (such as saving a transaction) now works correctly. If this still fails or if any of the above processes failed then you may need to go back to a backup of your database.
- Be aware (point out to client) that while the database was in a corrupt state saving new entries may have been working incorrectly in the database, and therefore there may now be (hopefully) minor inconsistencies in accounting data that we can help them to deal with if found. If this is not acceptable to the client then the only recourse would be to backup.