| Author |
 Topic  |
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
 Posted - 05/06/2008 : 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
USA
899 Posts |
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 05/12/2008 : 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
USA
899 Posts |
Posted - 05/13/2008 : 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
USA
35007 Posts |
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 05/13/2008 : 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
USA
899 Posts |
Posted - 05/14/2008 : 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
USA
35007 Posts |
|
| |
Topic |
|
How to cause data corruption
