Tag Archives: SQL server

Do YOU Checksum?

Posted on by
Reply

I am going to show you why you should be using checksum options on your backups (and restores).
I AM ACTUALLY NOT GOING TO WRITE THE CODE TO FORCE THIS CORRUPTION. I am starting to feel bad spreading/writing about the commands involved.

Anyways I did what was necessary for this demo and running a SELECT we now have:

SELECT * FROM testtable

SQL Server detected a logical consistency-based I/O error:
incorrect checksum (expected: 0xe41c795b; actual: 0xe41c3c5b).
It occurred during a read of page (1:55) in database ID 10 at offset 0x0000000006e000 in file ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\DBMaint2008.mdf’. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

Let’s create some backups without checksums.

BACKUP DATABASE [DBMaint2008] TO DISK = 'C:\sqlserver\DBMaint2008pre.bak'

It works- BACKUP DATABASE successfully processed 186 pages in 0.140 seconds (10.351 MB/sec).

--The backup set on file 1 is valid
RESTORE VERIFYONLY FROM DISK =  'C:\sqlserver\DBMaint2008pre.bak'

We did a RESTORE of the DATABASE successfully – processed 186 pages in 0.232 seconds (6.246 MB/sec).

RESTORE DATABASE [DBMaint2008DR]
FROM  DISK = N'C:\sqlserver\DBMaint2008pre.bak' WITH  FILE = 1,
MOVE N'DBMaint2008'
TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\DBMaint2008DR.mdf',
MOVE N'DBMaint2008_log'
TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\DBMaint2008DR_log.LDF',
NOUNLOAD,  STATS = 5
GO

Are things really ok on this newly recovered database?

DBCC CHECKDB ('DBMaint2008DR')

I dont think so – CHECKDB found 0 allocation errors and 2 consistency errors in database ‘DBMaint2008DR’.

At least let’s have a checksum in place for protection from this kind of thing.

BACKUP DATABASE [DBMaint2008] TO DISK = 'C:\sqlserver\DBMaint2008POST.bak'
WITH CHECKSUM, STATS = 10
GO

12 percent processed.
21 percent processed.
Msg 3043, Level 16, State 1, Line 14
BACKUP ‘DBMaint2008’ detected an error on page (1:55) in file ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\DBMaint2008.mdf’.
Msg 3013, Level 16, State 1, Line 14
BACKUP DATABASE is terminating abnormally.

Can you imagine if you did neither Checksum or consistency checks? I don’t think I want to imagine such an environment.

Carving up CHECKDB

Posted on by
Reply

This is a technique that I am using from Paul Randal – concept of splitting CHECKDB: CHECKALLOC, CHECKCATALOG and CHECKTABLE – here we will use “buckets” of tables split across the week.

http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/10/20/consistency-checking-options-for-a-vldb.aspx

Lets assume Adventureworks is a VLDB 🙂
Day 1 would be the below

 


USE AdventureWorks2012
GO
DBCC CHECKALLOC
GO

DBCC CHECKCATALOG
GO

Now lets take 1 bucket of tables for day 1 for CHECKTABLE.

 


SELECT ''+SCHEMA_NAME(schema_id)+'.'+name+''
AS TableName,	NTILE(7)
				OVER (ORDER BY Name)
				AS [BUCKET] INTO ##CHECKTABLE
FROM sys.tables  WHERE type = 'U'

DECLARE @TABLE  NVARCHAR(100)
DECLARE @SQL  NVARCHAR(MAX)

		DECLARE CHECKTABLE CURSOR FAST_FORWARD
		FOR
			SELECT TableName FROM ##CHECKTABLE
			WHERE BUCKET  = 1

			OPEN CHECKTABLE
			FETCH NEXT FROM CHECKTABLE INTO @TABLE
			WHILE @@FETCH_STATUS=0
				BEGIN
					BEGIN TRY
					SET @SQL = 	'DBCC CHECKTABLE(''' + @TABLE + ''') '
					PRINT @SQL
					EXEC SP_EXECUTESQL @SQL

					END TRY

					BEGIN CATCH

					DECLARE @ErrorMessage NVARCHAR(4000);
					DECLARE @ErrorSeverity INT;
					DECLARE @ErrorState INT;
					SELECT
						@ErrorMessage = ERROR_MESSAGE(),
						@ErrorSeverity = ERROR_SEVERITY(),
						@ErrorState = ERROR_STATE();

						 RAISERROR (
						 		@ErrorMessage, -- Message text.
						 		@ErrorSeverity, -- Severity.
								@ErrorState -- State.

								) WITH LOG;
					END CATCH

					FETCH NEXT FROM CHECKTABLE INTO @TABLE
				end

			CLOSE CHECKTABLE
	DEALLOCATE CHECKTABLE

DBCC CHECKTABLE('Person.Address')
DBCC CHECKTABLE('Person.AddressType')
DBCC CHECKTABLE('dbo.AWBuildVersion')
DBCC CHECKTABLE('Production.BillOfMaterials')
DBCC CHECKTABLE('Person.BusinessEntity')
DBCC CHECKTABLE('Person.BusinessEntityAddress')
DBCC CHECKTABLE('Person.BusinessEntityContact')
DBCC CHECKTABLE('Person.ContactType')
DBCC CHECKTABLE('Person.CountryRegion')
DBCC CHECKTABLE('Sales.CountryRegionCurrency')
DBCC CHECKTABLE('Sales.CreditCard')

Then the 2nd day you could do the 2nd bucket. etc…