1 / 28

A Whirlwind tour of DBCC CHECKDB

A Whirlwind tour of DBCC CHECKDB. Arun Sirpal. Who I am?. Objectives. What is CHECKDB?. Consistency / Integrity checks for your SQL Server databases. If we have corruption we need to know about it sooner rather than later! It will manifest into your on-disk backups. Causes of corruption?.

ajamie
Download Presentation

A Whirlwind tour of DBCC CHECKDB

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. A Whirlwind tour of DBCC CHECKDB Arun Sirpal

  2. Who I am?

  3. Objectives

  4. What is CHECKDB? Consistency / Integrity checks for your SQL Server databases. If we have corruption we need to know about it sooner rather than later! It will manifest into your on-disk backups. Causes of corruption? 99% of the time its down to your I/O Subsystem – lots of moving parts. If it does happen the key is being prepared for it. What doesn’t cause corruption? Interrupting Index Rebuilds. Shutting down SQL Server ( including NOWAIT option ). Stopping Shrinking operations.

  5. History of CHECKDB Firstly, CHECKDB needs to get a transactionally consistent view of your database - how this has happened has changed over time. SQL 7.0: Locking was the strategy – similar to that of the WITH TABLOCK option in SQL 2000 and 2005. Concurrency issues. SQL 2000: Transaction Log Analysis – after reading the databases pages CHECKDB would then read the transaction log. Single-threaded and prevented log truncation SQL 2005+: Written by Paul Randal where an Internal database snapshot is used. The database snapshot is created in the same location as the existing database then is removed when DBCC CHECKDB finishes. (Hidden Snapshot until SQL 2014) The best method - Only works on NTFS not FAT or ReFS (until 2014).

  6. The Command

  7. How often should we run CHECKDB?

  8. Bad practices Some Advice

  9. Disappearing corruption? Story: CHECKDB complained about corruption overnight. In the morning user ran CHECKDB but found no corruption! The maintenance job phenomenon SQL maintenance took place overnight after CHECKDB that rebuilt indexes – hence no corruption – Pages no longer allocated.

  10. What CHECKDB cannot fix We need VALID backups! Do not want to do manual extraction.

  11. Going Parallel Standard Edition – checks always single threaded. For Enterprise Edition, CHECKDB respects the maximum degree of parallelism setting configured for the instance (or the MAXDOP hint) You can confirm this via querying DMVs – Scheduler Ids.

  12. I usually execute: DBCC CHECKDB (' AdventureWorks2014 ') WITH NO_INFOMSGS I personally do not want to see benign output that is just informational. If you run it without the NO_INFOMSGS you will get the following: DBCC results for 'AdventureWorks2014'. Service Broker Msg 9675, State 1: Message Types analyzed: 14. Service Broker Msg 9676, State 1: Service Contracts analyzed: 6. Service Broker Msg 9667, State 1: Services analyzed: 3. Service Broker Msg 9668, State 1: Service Queues analyzed: 3. Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0. Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0. Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0. Service Broker Msg 9605, State 1: Conversation Priorities analyzed: 0. DBCC results for 'sys.sysrscols'. <SNIPPET> CHECKDB found 0 allocation errors and 0 consistency errors in database 'AdventureWorks2014'. DBCC execution completed. If DBCC printed error messages, contact your system administrator.

  13. DEMO – Running CHECKDB and the no longer hidden snapshot.

  14. When did it last run? Couple of options here Check the BOOT page: TF 3604, you need this to get the DBCC PAGE output to your console DBCC TRACEON (3604); GO — page 9 is the boot page DBCC PAGE (yourDB, 1, 9, 3); GO

  15. 2) My personal favourite is to run this code DBCC DBINFO ('AdventureWorks2014') WITH TABLERESULTS If you break CHECKDB into separate checks – Value does not get updated.

  16. DEMO – checking CHECKDB ran successfully.

  17. How long will it take? IT DEPENDS – more specifically on the following factors. • Size and complexity of the database – bigger database means more pages to read. • CPU and I/O activity on the server, CHECKDB is resource intensive so if we are running it on a server that is already I/O and CPU bound it could take a while. • Capability of your I/O subsystem – do you have SSDs? • CHECKDB consumes a lot of memory. Consider putting CHECKDB in a Resource Group to reduce the memory which increases the speed. • Do you have Enterprise edition? If so you could go parallel meaning LOWER run times. *If parallel checking is an issue you can force it to be single threaded via TF2528*

  18. Do not cancel CHECKDB Is CHECKDB taking its time? Could be doing a “deep-dive” . This happens if it finds corruption – DO NOT CANCEL it. Let it finish! Do you use Computed columns? Prior to SQL 2016 when running DBCC CHECKDB against indexes with computed columns you would suffer from DBCC – OBJECT – METADATA latch contention. SQL 2016 addressed this by changing the behaviour – checks on this type of index no longer the default. User needs to issue EXTENDED_LOGICAL_CHECKS. The Workround? Disable those indexes and rebuild them after the checks.

  19. VLDB Techniques? More for the “larger” databases. Option (1): DBCC CHECKDB essentially runs: DBCC CHECKCATALOG, DBCC CHECKALLOC and DBCC CHECKTABLE. – You could split these commands over couple of days – see my link on some code: https://blobeater.blog/2015/05/16/carving-up-checkdb/ What I have done here is query sys.tables for all the tables within my database and split them into buckets and execute DBCC CHECKTABLE against a bucket and then DBCC CHECKALLOC and CHECKCATALOG independently.

  20. Option (2) - DBCC CHECKFILEGROUP Again some code to read: https://blobeater.blog/2016/09/13/dbcc-checkfilegroup/ If you are using File groups then you are ready to run this BUT you will need to execute DBCC CHECKCATALOG independently. Option (3) – Offload the check to a different server (via backups) This frees up resource on your production server BUT if you get a corruption error on the other server it should probe you to check production. Option (4) – Consider using PHYSICAL_ONLY. This does reduce the time to take but it is not as thorough. Recommended that a full run of DBCC CHECKDB to be performed periodically

  21. Option (5) – Consider using your own database snapshot and run CHECKDB against it. Enterprise edition only (Pre SQL Server 2016 SP1) My facts: 880GB took 6 hours – used the “Buckets” technique to spread the load over 2 days.

  22. DEMO - Code for separate CHECKS

  23. Repairing Repair Fast option – Does nothing, here for backward compatibility. If you use REPAIR_ALLOW_DATA_LOSS you will lose data (as the name suggests). REPAIR_REBUILD will not fix everything, REPAIR_REBUILD only fixes problem in nonclustered indexes. Repairs are always offline, in that the database must be in single-user mode.

  24. Repairing(2) STORY: I accidentally ran REPAIR_ALLOW_DATA_LOSS ON msdb. Go to backup instead because it could delete a key record which causes an issue with an agent job. Depending on your context REPAIR could be the option to use over a backup recovery i.e. 2 NCIs to be REPAIR_REBUILD VS 200GB backup restore? Quote from Paul “Repair's a dangerous beast and should only be used as a last resort”......

  25. What does CHECKDB not do? It does NOT do: DBCC CHECKIDENT – A command to check and re-seed values for an identity column. DBCC CHECKCONSTRAINTS – Checks the integrity of a specified constraint or all constraints on a specified table (or all tables) – something that SHOULD BE executed after a REPAIR option.

  26. Questions?

More Related