Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Memory Corruptions, or Why You Need DBCC CHECKDB


Memory Corruptions, or Why You Need DBCC CHECKDB

Author
Message
Paul Randal
Paul Randal
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2161 Visits: 1714
Comments posted to this topic are about the item Memory Corruptions, or Why You Need DBCC CHECKDB

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
dharmendra.keshari
dharmendra.keshari
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 375
Hi Paul,

Thank you so much for writing on CheckSum. it is really awesome blog.

Could you please provide any link which gives us information about the all the activities which happen internally when we perform backup with option "Perform checksum before writing to media" ?

Thanks!
Dharmendra Keshari
Dharmendra.Keshari@gmail.com
h.berg-884044
h.berg-884044
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 16
Good article that provides valuable insights on SQL Server internals. I have a small addition however.

An undetected memory corruption due to a faulty chip implies you're not using ECC memory in the server. ECC is able to detect multiple bit faults and correct single bit faults. That is why servers with large amounts of ECC memory take so long to initialize, the need to initialize all the memory addresses with the correct check bits.

On a production server I would spend the extra money, this allows your server to go down gracefully and error free when a single memory chip dies. The memory system as a whole will still provide the correct memory content. You then replace the faulty module and no harm done.

On a development or test server this is open to debate, but even there the extra money is well spent IMO.
Michael Meierruth
Michael Meierruth
SSChasing Mays
SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)

Group: General Forum Members
Points: 647 Visits: 2507
After reading your recent 'Importance of Validating Backups' and now this article on DBCC CHECKSUM, I'm very curious to know how much database corruption is really encountered in the real world. I deal with a few client production databases and in the course of 10 years or so I have never run into this corruption issue - thank heaven.
So to all the SQL Server Central community out there, to whom has this ever really happend!?

And again, after reading your recent 'Importance of Validating Backups' I decided to get my hands a little dirty and automate this DBCC CHECKSUM on a production database. It's not so simpe. But here is my solution. It consistes of check1.sql which generates check.log which is read by check2.sql and sends an email when something went wrong. Has anyone done anything to automate DBCC CHECKSUM that's a bit more clever?


-- does a dbcc checkdb on a database

-- this should be run via sqlcmd with -o parameter sending ouput to file check.log;
-- this file then gets read by check2.sql

declare @errnum int
declare @errdesc varchar(255)
set @errnum=0
set @errdesc=''

begin try
dbcc checkdb('MYDATABASE')
set @errnum=@@error
set @errdesc=error_message()
end try
begin catch
set @errnum=@@error
set @errdesc=error_message()
end catch

if @errnum <> 0 begin
print 'CHECKDB ERROR: ' + convert(varchar,@errnum) + ' ' + @errdesc
end




-- loads check.log generated by check1.sql into a table and
-- checks if there are errors

set nocount on

-- U = table V = view P = stored procedure FN = scalar function TF = table-valued function
if object_id('tempdb..#sqlcmdlog','U') is not null drop table #sqlcmdlog
create table #sqlcmdlog(line varchar(8000))

-- load log file into a one-column table
bulk insert #sqlcmdlog
from 'c:\temp\check.log'
with (rowterminator = '')
go

-- check if there were errors
declare @count1 int
declare @count2 int

select @count1=count(*)
from #sqlcmdlog
where line like 'CHECKDB found 0 allocation errors and 0 consistency errors%'

select @count2=count(*)
from #sqlcmdlog
where line like 'CHECKDB ERRORS%'

if @count1<>1 or @count2>0 begin

-- you should send an email here with file check.log as an attachment

select line
from #sqlcmdlog
where line like 'CHECKDB found % allocation errors and % consistency errors%'
or line like 'CHECKDB ERROR%'

end



The rownterminator value doesn't seem to appear. It should be a backslash followed by the letter 'n'.
dtipser
dtipser
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 431
Hi Paul,

Great article about CHECKSUM and DBCC CHECKDB. Regarding DBCC CHECKDB you said that it checks in-memory page before CHECKPOINT is done, so it could detect some in-memory page fault. But once the CHECKPOINT passed the page is written to disk, CHECKSUM is performed and probably the memory is free. My question is, if we are doing a DBCC CHECKDB once by week, or for a restored database, there are minimal chances yet to have pages in memory that are not written on disk or were modified in-memory during the DBCC CHECKDB statement. In this case we have only CHECKSUMM for a page that could be correct or corrupted and DBCC CHECKDB would not detect any corruption either. Am I right or I missed something in your explanation.

Thanks again,

Daniel Tipser



AZJim
AZJim
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
Points: 79 Visits: 293
Thank you for the fine post. I have been a DBA for a long time (more than 26 years covering mainframe, unix, and Windows). In my earlier years, hierarchical databases with direct pointers was the only database used for large companies. A similar utility was employed to validate the pointers. Management complained constantly about the cost of running this utility. At one division of a previous company I worked for, the DBA heeded the management's advice and stopped running the utility. You can probably guess what happened. A corrupted control block started slowly to corrupt the database pointers until they could not ignore it. But by that time, they no longer had logs to go back to the previous backup and roll forward. They asked me for assistance and I told them that it is like running your car without car insurance. Yes, car insurance is expensive, but that goes with the cost of owning a car. I said I couldn't help them. They ended up having to restore the database and go back to the outside customers and beg them to resubmit their transactions. It was an embarrassment to the company. Perhaps this story will encourage DBAs to never neglect required maintenance -- and DBCC is required maintenance.
Adam Seniuk
Adam Seniuk
Mr or Mrs. 500
Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)

Group: General Forum Members
Points: 556 Visits: 1039
Not that I want to say this script isn't great but...

Why the hell would you load this into a text file, then parse the text file?

When you could use the with TABLERESULTS option?



declare @databaseName
declare @DBCCCheckDBResults table
(
[DBCCCheckDBResultsID] [int] IDENTITY(1,1) NOT NULL,
[ServerName] [varchar](255) NULL,
[NodeName] [varchar](255) NULL,
[DatabaseName] [varchar](255) NULL,
[Error] [bigint] NULL,
[Level] [bigint] NULL,
[State] [bigint] NULL,
[MessageText] [varchar](8000) NULL,
[RepairLevel] [bigint] NULL,
[Status] [bigint] NULL,
[DbId] [bigint] NULL,
[ObjectId] [bigint] NULL,
[IndexId] [bigint] NULL,
[PartitionId] [bigint] NULL,
[AllocUnitId] [bigint] NULL,
[File] [bigint] NULL,
[Page] [bigint] NULL,
[Slot] [bigint] NULL,
[RefFile] [bigint] NULL,
[RefPage] [bigint] NULL,
[RefSlot] [bigint] NULL,
[Allocation] [bigint] NULL,
[RunDate] [datetime] NULL
)

insert @DBCCCheckDBResults
(
Error
,Level
,State
,MessageText
,RepairLevel
,Status
,DbId
,ObjectId
,IndexId
,PartitionId
,AllocUnitId
,[File]
,Page
,Slot
,RefFile
,RefPage
,RefSlot
,Allocation
)
exec (''dbcc checkdb (['' + @databaseName + '']) with TABLERESULTS'')





Over 12yrs in IT and 10yrs happily stuck with SQL.

http://aseniuk.wordpress.com
- SQL 2008/R2/2012/2014
- Oracle 8/9/10/11
- MySQL 4/5
Michael Meierruth
Michael Meierruth
SSChasing Mays
SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)

Group: General Forum Members
Points: 647 Visits: 2507
Adam,
There is something not right with your 'exec' statment with all those quotes and parenthesis. Can't quite grasp what it is.
But I definitely like this 'with TABLERESULTS' clause. This definitely makes it all a lot easier.
Thanks.
Adam Seniuk
Adam Seniuk
Mr or Mrs. 500
Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)

Group: General Forum Members
Points: 556 Visits: 1039
Michael Meierruth (11/8/2012)
Adam,
There is something not right with your 'exec' statment with all those quotes and parenthesis. Can't quite grasp what it is.
But I definitely like this 'with TABLERESULTS' clause. This definitely makes it all a lot easier.
Thanks.


oops, I must have copied it from a script that is running dynamic queries already. just swap out the '' to ' and it should work.


Over 12yrs in IT and 10yrs happily stuck with SQL.

http://aseniuk.wordpress.com
- SQL 2008/R2/2012/2014
- Oracle 8/9/10/11
- MySQL 4/5
SQLCereal
SQLCereal
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 759
Daniel,

From the article - "DBCC CHECKDB interprets the page contents (for example, validating row structures, validating column values, checking linkages in indexes), and so should always be able to detect a corrupt page, even if the corruption happened while in memory."

It validates the physical pages on disk, but goes beyond calculating a checksum to find corruption.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search