2

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?

  • Is the mirroring working properly? If you are deleting rows on the Mirror server, then the answer to my question is 'No'. – RLF Feb 2 '15 at 17:01
  • I do not have access to either the production server or its mirror. I can only work on the test server. When I issue the delete from the application and then run a query to see if the records are deleted they are not. If I delete the records currently in the table in SSMS, the application is then able to delete records that are added after I cleared out the table. – Jim Feb 2 '15 at 18:46
  • Does your test server have both a Primary database and a Mirrored database? When you "delete the records" is it in the Mirror database? (I don't understand your test setup.) – RLF Feb 2 '15 at 18:54
  • No, the test server is not mirrored. It's set up for me to promote changes from my development PC so that users can beat up on any changes before they are moved to production. – Jim Feb 2 '15 at 18:59
  • The test server is not mirrored, so the mirror is not being tested? Or else your testers are testing on a separate pair of QA and Mirror system? I am inclined to believe that there are configuration problems that are complicating things for you. SSMS queries have no more power than Application queries (assuming the same code and rights) so I still do not understand what is going on. If you reread the responses, perhaps you could edit the original question to make it clearer to responders. – RLF Feb 2 '15 at 19:08
2

It is highly unlikely what you are seeing is corruption.

The far more likely cause of this issue is the "app" is not issuing the DELETE FROM command correctly, or in the way that you think it is. Perhaps the DELETE is being rolled back for some reason, or perhaps the WHERE statement is preventing rows being deleted that you think should be deleted. If you can view the code in the app, add the code that is performing (or not performing) the DELETE to your question via the edit link. If you don't have access to the code, you could use SQL Server Profiler to run a trace so you can "see" the statements your app is issuing to the server to determine for certain what the app is doing.

It's possible there are some application-level data problems preventing the application from performing the delete. For instance, if there are no primary/foreign key relationships enforcing referential integrity, it's possible a DELETE FROM statement is using a join in the delete that is effectively excluding rows from being deleted as a result of those rows being orphaned. By way of an example, take a look at this:

USE tempdb;

IF OBJECT_ID(N'dbo.Child', N'U') IS NOT NULL
DROP TABLE dbo.Child;
IF OBJECT_ID(N'dbo.Parent', N'U') IS NOT NULL
DROP TABLE dbo.Parent;

CREATE TABLE dbo.Parent
(
    parent_id int NOT NULL
        CONSTRAINT Parent_pk
        PRIMARY KEY CLUSTERED
    , parent_data varchar(50) NOT NULL
);

CREATE TABLE dbo.Child
(
    child_id int NOT NULL
        CONSTRAINT Child_pk
        PRIMARY KEY CLUSTERED
    , parent_id int NOT NULL
    , child_data varchar(10) NOT NULL
);

INSERT INTO dbo.Parent (parent_id, parent_data)
VALUES (1, 'A')
    , (2, 'B');

INSERT INTO dbo.Child (child_id, parent_id, child_data)
VALUES (1, 1, 'C')
    , (2, 1, 'D')
    , (3, 2, 'E')
    , (4, 2, 'F')
    , (5, 3, 'G')
    , (6, 3, 'H');

SELECT *
FROM dbo.Child;
╔══════════╦═══════════╦════════════╗
║ child_id ║ parent_id ║ child_data ║
╠══════════╬═══════════╬════════════╣
║        1 ║         1 ║ C          ║
║        2 ║         1 ║ D          ║
║        3 ║         2 ║ E          ║
║        4 ║         2 ║ F          ║
║        5 ║         3 ║ G          ║
║        6 ║         3 ║ H          ║
╚══════════╩═══════════╩════════════╝

Now, if we want to delete all rows from dbo.Child, we might use this delete statement that deletes all child rows that have a parent in the parent table:

DELETE FROM dbo.Child
FROM dbo.Child c
INNER JOIN dbo.Parent p ON c.parent_id = p.parent_id;

If we had a properly defined foreign-key in the dbo.Child table, the 5th and 6th rows would never be able to exist, however since we don't have good relational integrity, rows can exist in the child table that have no parent, as you can see from this SELECT statement ran after the above delete runs:

SELECT *
FROM dbo.Child;
╔══════════╦═══════════╦════════════╗
║ child_id ║ parent_id ║ child_data ║
╠══════════╬═══════════╬════════════╣
║        5 ║         3 ║ G          ║
║        6 ║         3 ║ H          ║
╚══════════╩═══════════╩════════════╝

When SQL Server detects corruption in the primary database (the one that is currently on-line and serving clients), it will transfer the page that is corrupted from the mirror database, via Automatic Page Repair, in an attempt to fix the database automatically. Automatic Page Repair has been available since SQL Server 2005 Service Pack 1, when mirroring officially became a supported option.

Since you've ran DBCC CHECKDB with no corruption reported, I'd say with near 100% certainty that you're not experiencing corruption.

  • Thanks Max, but as I noted above, after deleting the records while in SQL Server Management Studio (SSMS) the delete command worked from the application. It wasn’t deleting the records from the application call before I deleted the records in SSMS. I was also not getting an error response from the delete command in the application. Since don’t know how or when the mirror was created, is it possible to have the same corruption on both sides if say the mirror was created with the database just being copied over? Does this automatic correction capability extend to SQL Server 2005? – Jim Feb 2 '15 at 18:37
0

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.

If this makes you think database is corrupt, then you are thinking incorrect. How are you so sure database is corrupt. I am just going by what you posted

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?

The most important thing about corruption is why is occured and what is cause. If backup is not corrupt you are very lucky in that case you would have to restore the database on same server with different name. There is also chance that if underlying subsystem is highly corrupted the newly restored database might get corrupted as well. Its also likely that mirror database has also corrupted, otherwise you can failover to mirror database (while you are parallely doing restore) and see if it works.

Finally if I do find corruption in testing how would I determine the contents of the page that could show the corruption?

This depends on level of corruption and result produced why dbcc checkdb command. If you are lucky to run it it would produce result showing which pages(page ID) and index (CI,NCI) is corrupt actually its bit tricky and you might need expert to get around with this.

Reading SQL Server errorlog using sp_readerrorlog and event viewer would give you lot more information about corruption. Mostly a corrupt disk subsystem is cause of corrupted SQL Server database

Moral: Every thing depends on level of corruption. Few corruption can be removed by just running repair_rebuild command.

  • Hi Shanky. I’m not sure the database is corrupt. It is behaving strangely. Users are getting errors on the production side that I can’t replicate in test. The test database was created from a database backup and not a direct copy of the MDF (and LDF) files. On the test side I’ve discovered that Temp tables are not responding to simple delete commands from applications. After I clear the data out of the tables by issuing delete commands directly in SSMS, data entered subsequently into the tables will be deleted by the applications delete calls. – Jim Feb 2 '15 at 18:56
  • >> Users are getting errors on the production side that I can’t replicate in test....And what is the error ? If you see yours question heading it talks about corruption so we all thought its DB corruption – Shanky Feb 2 '15 at 19:01
  • The errors in the applications are varied. I believe many of them are coming from database issue - many from timeouts. I have no doubt that there are issues with mirroring in that I was told it took 6 hours (before I was hired) to get the database up after a failure a while back (it was NOT hardware related). I’m leaning towards corruption due to prior experiences in Foxpro and Access (observed over 10 years ago) the inconsistencies in the database functionality, and tables blowing up with data where simple DELETE commands from an application only work after I've cleared out the data in SSMS. – Jim Feb 2 '15 at 19:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.