I’m new to my current company and we have a System Administrator but no DBA. In the past I’ve taken care of DBA related issues at other companies. I’ve run into and corrected database corruption in other databases (Foxpro and Access) and was able to at least document the data corrupted so it could be reentered. I’ve never encountered or suspected corruption with a SQL Server database. I suspect that I’m running into this with a SQL Server 2005 database that is being mirrored.
The situation is that simple delete commands passed to the database are not deleting data in certain tables. I literally have to go in through SQL Server Management Studio and issue the delete command to clear out these tables. There are anywhere from 144 thousand to 32 Million records in temp tables and this data is taking up more than 90% of the database (as determined after deleting the records and shrinking the database). After I do this clean up, then the delete commands passed to the database on the test server work.
My first line of attack would be to run DBCC CHECKDB against copies of the database. I ran it against a backup and there was no corruption found. We can’t get exact copies of the database as my System administrator has had issues in the past where the database server has taken up to 6 hours to get online and he does not want to disable both the live and mirrored SQL servers.
My first question is if I should trust a database backup to include corrupted data that would be found by DBCC CHECKDB. If the backup does not store the corruption, what is the best way to stop and then bring back both the live and mirrored database to get copies of the LDF and MDF files?
Finally if I do find corruption in testing how would I determine the contents of the page that could show the corruption?