How to cause data corruption - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Data Corruption Issues

How to cause data corruption

Author

Topic

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-06 : 18:04:19

Paul,

After reading your recent blog about CHECKDB, I need to modify my DBCC CHECKDB stored procedure to cause the SQL job to fail when data corruption is detected. In order to test my changes, I'd like to know how to cause data corruption in SQL Server 2005. In previous versions this was easy as we could modify the FirstIAM column in sysindexes. This was pretty destructive but got the job done. Obviously we did this only in test environments as a recovery exercise.

What is the most effective way to cause data corruption in SQL Server 2005?

Once we've checked the value of @@ERROR, how do we cause the job to fail? Do we simply RAISERROR?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2008-05-06 : 18:18:53

Hi Tara,

The easiest way is not to try to do it at all but to use one of the pre-corrupted example databases I provide on sqlskills.com - see [url]http://www.sqlskills.com/blogs/paul/2007/09/04/Example20002005CorruptDatabasesAndSomeMoreInfoOnBackupRestorePageChecksumsAndIOErrors.aspx[/url].

If you want to do it yourself, your best bet is to shutdown the database and use a hex editor. Go for an offset of 8192 x at least 100 to get past the critical system tables and then write a bunch of zeroes at that offset to cause page header corruption.

RAISERROR is the way to go.

Hope this helps.

Paul Randal
SQL Server MVP, Managing Director, SQLskills.com

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-06 : 18:48:16

Excellent, thank you!

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-12 : 17:51:15

Paul,

I've restored your corrupt database (2005) to a test system and then ran DBCC CHECKDB (looping through the databases) as a job. The job fails each time even though I have not revised it to RAISERROR. Before modifying my code, I'm trying to show that a SQL job can show success even if corruption exists. But I'm unable to prove this. Any ideas?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2008-05-13 : 13:46:22

ok - it works perfectly for me too. I must admit I hadn't tried it on 2005 SP2 so the behavior may have changed from previous versions. Do you have a 2000 system to test it on? (I don't have one readily accessible today).

I'll update the blog post if the problem was fixed in SP2 but not before.

Thanks Tara.

Paul Randal
SQL Server MVP, Managing Director, SQLskills.com

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-13 : 13:55:34

Unfortunately, I do have a SQL Server 2000 system.

I'll test it out shortly.

FYI: The 2005 system is on build 3054 (not sure why we haven't brought it up to 3200 like production). The 2000 system is on build 2148.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-13 : 14:25:17

Before I restored your 80 database to my 2000 system, my "Integrity Checks" job ran successfully. Once I restored the database, I ran the job. The job failed.

I then located a 2000 system with build 818. I did the same steps as above. The job failed there too.

I can't find a 2000 or 2005 system that will show a job as successful when data corruption exists and the only thing done in the job is DBCC CHECKDB.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2008-05-14 : 13:02:58

ok - that's good to know. All the anecdotal evidence I've heard must be from people with other stuff in their maintenance jobs. I'll fix up the blog post (with credit of course).

Thanks Tara.

Paul Randal
SQL Server MVP, Managing Director, SQLskills.com

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-14 : 13:19:32

No, thank you! Now I don't have to modify my code and deploy it to about a hundred servers.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources