DBCC CHECKDB has the ability to perform parallel checking of objects. However, it absolutely depends on the edition of SQL Server, it only happens when using enterprise edition.
Let’s see this in action. I propose the following tests for this blog post:
If you know about DBCC CHECKDB then most likely you will know about DBCC CHECKTABLE. Quite simply this command performs primitive system-catalog consistency checks, per-table consistency checks on the single table specified, and cross-table consistency checks on indexed views that reference the specified table. (Page 899 Microsoft SQL Server Internals 2012, Chapter 14, Page 899, Paul Randal)
So I had a corruption issue and I was thinking about running repair but I wanted to know what would potentially get deleted.
Did you know that SQL Server tracks suspect pages and puts the details into the msdb database in a table called suspect_pages? Well it does, but the question I want answering is what happens if the page within the suspect pages table is fixed? Does it automatically get removed/updated or do we as the administrators have to do anything manually?
I have a SQL Server that is constantly producing “dump” files (with a MDMP File type), these are named SQLDumpxxxx (xxx = numerical value). They are located in the following directory: C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Log\ – sound familiar?
Have a look at this screen shot – it’s messy!
I got bored (really bored) one weekend I decided to challenge myself. I had corruption within a specific table (localised within a page) with no backups handy only an old image of the data files. The challenge being salvage data without attaching the data files.
A quick video clip where I show two things.
Firstly I show how since SQL Server 2014 SP1+ the snapshot is no longer hidden then finally how I check that DBCC CHECKDB has ran successfully.
My database is in the recovery pending state and I want to get in and extract the data out into a new database, I have NO BACKUPS available so some data (even if it is dodgy) is better than nothing.
The title is adapted from a child-hood movie of mine and is my daily (database-related) WTF moment and it is my entry for this month’s T-SQL Tuesday found: http://www.pontop.dk/single-post/2017/03/07/Announcing-T-SQL-Tuesday-88-%E2%80%93-The-daily-database-related-WTF
Did you know that you can run DBCC CHECKDB (WITH PHYSICAL_ONLY) and issue page restores from SSMS (SQL Server Management Studio). I never, and I probably will never because I rather use TSQL – however I have only just seen this ( yes it’s been around for a while ) and felt like blogging about it.
I clicked on restore > page.
I corrupted page 126 and look it knows about it!
Yes it’s in bad shape – Level 24 severity.
SELECT * FROM [ZoraDB].[dbo].[people]
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0xa154e798; actual: 0xa144e788). It occurred during a read of page (1:126) in database ID 11 at offset 0x000000000fc000 in file ‘C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\ZoraDB.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.
So SSMS is so good it will restore for you – and build the restore chain.
Hitting the SCRIPT button gives us:
USE [master] RESTORE DATABASE [ZoraDB] PAGE='1:126' FROM DISK = N'C:\SQLSERVER\ZoraDB.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5 -- Tail Log BACKUP LOG [ZoraDB] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\ZoraDB_LogBackup_2016-12-15_09-51-17.bak' WITH NOFORMAT, NOINIT, NAME = N'ZoraDB_LogBackup_2016-12-15_09-51-17', NOSKIP, NOREWIND, NOUNLOAD, STATS = 5 RESTORE LOG [ZoraDB] FROM DISK = N'C:\SQLSERVER\ZoraLog.trn' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5 RESTORE LOG [ZoraDB] FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\ZoraDB_LogBackup_2016-12-15_09-51-17.bak' WITH NOUNLOAD, STATS = 5 GO
SELECT * FROM [ZoraDB].[dbo].[people] (21 row(s) affected)
How cool is that?
All About Data 