SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Data Corruption Issues
 How to cause data corruption
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tkizer
Almighty SQL Goddess

USA
36831 Posts

Posted - 05/06/2008 :  18:04:19  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

USA
899 Posts

Posted - 05/06/2008 :  18:18:53  Show Profile  Visit paulrandal's Homepage  Reply with Quote
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 http://www.sqlskills.com/blogs/paul/2007/09/04/Example20002005CorruptDatabasesAndSomeMoreInfoOnBackupRestorePageChecksumsAndIOErrors.aspx.

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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36831 Posts

Posted - 05/06/2008 :  18:48:16  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36831 Posts

Posted - 05/12/2008 :  17:51:15  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

paulrandal
Yak with Vast SQL Skills

USA
899 Posts

Posted - 05/13/2008 :  13:46:22  Show Profile  Visit paulrandal's Homepage  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36831 Posts

Posted - 05/13/2008 :  13:55:34  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36831 Posts

Posted - 05/13/2008 :  14:25:17  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

paulrandal
Yak with Vast SQL Skills

USA
899 Posts

Posted - 05/14/2008 :  13:02:58  Show Profile  Visit paulrandal's Homepage  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36831 Posts

Posted - 05/14/2008 :  13:19:32  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000