Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Memory Corruptions, or Why You Need DBCC CHECKDB Expand / Collapse
Author
Message
Posted Wednesday, November 7, 2012 9:24 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, October 27, 2014 11:15 AM
Points: 2,041, Visits: 1,671
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
Post #1382251
Posted Thursday, November 8, 2012 1:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 4:00 PM
Points: 2, Visits: 229
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
Post #1382312
Posted Thursday, November 8, 2012 1:59 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 17, 2013 3:41 AM
Points: 17, Visits: 13
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.
Post #1382330
Posted Thursday, November 8, 2012 2:05 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 2:19 AM
Points: 542, Visits: 2,138
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'.
Post #1382333
Posted Thursday, November 8, 2012 6:49 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, November 17, 2014 12:26 PM
Points: 24, Visits: 325
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



Post #1382468
Posted Thursday, November 8, 2012 9:03 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, November 12, 2014 7:50 AM
Points: 56, Visits: 184
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.
Post #1382553
Posted Thursday, November 8, 2012 9:16 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 8:33 AM
Points: 535, Visits: 888
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
Post #1382565
Posted Thursday, November 8, 2012 9:40 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 2:19 AM
Points: 542, Visits: 2,138
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.
Post #1382587
Posted Thursday, November 8, 2012 9:54 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 8:33 AM
Points: 535, Visits: 888
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
Post #1382597
Posted Thursday, November 8, 2012 10:08 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 2:24 PM
Points: 41, Visits: 603
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.
Post #1382608
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse