November 7, 2012 at 9:24 pm
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
November 8, 2012 at 1:08 am
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
November 8, 2012 at 1:59 am
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.
November 8, 2012 at 2:05 am
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'.
November 8, 2012 at 6:49 am
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
November 8, 2012 at 9:03 am
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.
November 8, 2012 at 9:16 am
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'')
November 8, 2012 at 9:40 am
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.
November 8, 2012 at 9:54 am
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.
November 8, 2012 at 10:08 am
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.
November 8, 2012 at 10:37 am
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
November 8, 2012 at 10:38 am
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
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
November 8, 2012 at 10:40 am
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
November 8, 2012 at 10:41 am
@jim - exactly!
@adam-2 - 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
November 8, 2012 at 10:56 am
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
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply