Memory Corruptions, or Why You Need DBCC CHECKDB

  • Paul Randal

    One Orange Chip

    Points: 29438

    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

    SSC Enthusiast

    Points: 134

    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

    SSC Veteran

    Points: 205

    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

    SSChampion

    Points: 10051

    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

    SSC-Addicted

    Points: 416

    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

    Default port

    Points: 1432

    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

    SSCrazy

    Points: 2221

    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.
    - SQL 2008/R2/2012/2014/2016/2017
    - Oracle 8/9/10/11
    - MySQL 4/5 and MariaDB

  • Michael Meierruth

    SSChampion

    Points: 10051

    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

    SSCrazy

    Points: 2221

    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.
    - SQL 2008/R2/2012/2014/2016/2017
    - Oracle 8/9/10/11
    - MySQL 4/5 and MariaDB

  • SQLCereal

    SSC-Addicted

    Points: 451

    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.

  • Paul Randal

    One Orange Chip

    Points: 29438

    Michael Meierruth (11/8/2012)


    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!?

    Hundreds to thousands of times every week across the world, in the tens of millions of SQL Server databases out there. Just look at the corruption forums here. I get random emails from people at least 5 times a week asking for help with interpreting DBCC CHECKDB results, and I've been directly involved (at Microsoft, on forums, with clients, emails etc) with several thousand cases of corruption over the last 12 years.

    Thanks

    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

  • Paul Randal

    One Orange Chip

    Points: 29438

    dharmendra.keshari (11/8/2012)


    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

    Thanks!

    There isn't one that goes into internal details. The pertinent facts are that the checksums of allocated pages in the database are validated before being written to the backup - that's all we care about.

    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

  • Paul Randal

    One Orange Chip

    Points: 29438

    Hey Daniel - no, your thinking isn't right. Remember that DBCC CHECKDB isn't a checksum-checker, it happens to check checksums as part of what it does, but it goes a lot deeper and so will discover the corruption even if the checksum isn't incorrect (because of a timing issue). Thanks

    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

  • Paul Randal

    One Orange Chip

    Points: 29438

    @jim - exactly!

    @adam - indeed, using WITH TABELRESULTS is the way to go.

    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

  • Jeff Moden

    SSC Guru

    Points: 994858

    As usual, a great article, Paul.

    Do you have a link to a follow up somewhere that tells you what to do when corruption is found using these methods?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 1 through 15 (of 25 total)

You must be logged in to reply to this topic. Login to reply