A Fine Slice Of SQL Server
Arun Sirpal shows that the CHECKDB snapshot is no longer hidden:
I am in the middle of some research regarding CHECKDB and learnt something new. From SQL Server 2014 onwards the database snapshot that is used to get to a transactionally-consistent point-in-time is no longer hidden.
Arun includes a sample to prove it.
Comments closedErik Darling notes that his Connect item to replace DBCC SHOW_STATISTICS has been marked as resolved:
So what does it look like?
I have no idea. I don’t know if it’s a DMV or a function, I don’t know what it’s called, and I don’t know what information it exposes. I also don’t know how it will get joined to other DMVs. There were no details offered up when the status changed. And I’m fine with that! I’m pretty psyched that it got enough traction to get a fix to begin with. If anyone from MS feels like shooting me an email with details, I won’t complain.
But since we don’t know, we’re free to speculate. Like all those History Channel shows about aliens and fake animals and where the Templars secretly buried Jesus’ gold teeth in Arizona. It’ll be fun!
It’ll be interesting to see the results.
Comments closedSlava Murygin has a script to turn DBCC MEMORYSTATUS output into one result set:
When I wanted to research memory problem on a server and started to dig deeper into “DBCC MEMORYSTATUS” command.Very useful links to understand that command were from Microsoft:
https://support.microsoft.com/en-us/kb/271624
https://support.microsoft.com/en-us/kb/907877During the research I’ve faced two problems:
1. I had to wait several seconds to get the full result set.
2. I had to scroll down and shuffle 115 different data sets to find the counter I want.To eliminate both these problem all these different counters have to be in the same table/data set.
That will make research easier and data can be stored or compared with the base line.
Read on for the T-SQL script.
Comments closedArun Sirpal diagnoses a slower-than-usual DBCC CHECKDB run:
All the signs of CHECKDB Latch contention.
DBCC – OBJECT – METADATA this latch can be a major bottleneck for DBCC consistency checks when indexes on computed columns exist. As a side note DBCC_Multiobject scanner is used to get the next set of pages to process during a consistency check.
Read on for the details and Arun’s solution.
Comments closedBrent Ozar shows how to support running DBCC CHECKDB on a log shipping subscriber:
At first glance, if you set up log shipping the way most folks do, it looks like you can’t run CHECKDB on a subscriber:
On SQL Server Enterprise Edition, you can try working around it by creating a database snapshot – but that won’t work either:
Here’s the trick: your database needs to be in standby mode instead of norecovery. When you run restores with the standby option, you’re able to query the database in between restores.
This doesn’t obviate the need for running CHECKDB on a primary, but it can offload some of that work some of the time.
Comments closedKendra Little explains the NOINDEX option of DBCC CHECKDB and how it relates to PHYSICAL_ONLY:
PHYSICAL_ONLY looks at page structures and makes sure something isn’t obviously wrong. If you have the database’s page verification option set to CHECKSUM (the default since SQL Server 2005, and a very good thing), it runs a checksum on the page and compares it to a checksum recorded in the header when the page was last modified.
When you use the PHYSICAL_ONLY option, you’re telling SQL Server to skip logical checks. It won’t do things like make sure the data in a clustered and nonclustered index on a table is consistent with one another.
I like the analogy, as well as the how-to guide on messing up your database. But try not to do that to a production database…
Comments closedKenneth Fisher describes DBCC Help:
Many years ago during an interview I was asked to “Name the top 7 DBCC commands that you use.” I think I was able to name 3. Ever since then I’ve paid particular attention to DBCC commands in preparation of the day when I hear that question again. Well not to long ago I was watching Erin Stellato’s (b/t) PluralSight course “SQL Server: Understanding and Using DBCC Commands”. Note: It’s a great course and I highly recommend it. In it she goes over quite a few DBCC commands, some documented, some not. In noted two in particular.
The first one I noted gives you a list of the size and free space in the logs of all databases on the instance. Highly useful. I’ve used it repeatedly recently. Unfortunately my memory is not always the best and I can never quite remember the exact name. Which brings us to the other command.
I completely agree that Erin’s Pluralsight course is worth watching.
Comments closedSQLWayne has a follow-up on database corruption:
You see the CHECKSUM on the backup along with the RESTORE VERIFYONLY. The code was generated by right clicking on the database, selecting Tasks, then Backup, plug in the parameters, and select Script. I put it in a new query window as I may back up several databases in the same job. Sometimes I’ll just do a find/replace for the other databases since my backup. The Restore Verifyonly gives you some confidence that your backup is recoverable: NEVER assume that just because your backup ran that the database is restorable! The ONLY way to know is to actually restore it to another file! You don’t want to accidentally clobber your production that probably has newer data in it.
Corruption is a serious event when your entire job revolves around protecting data. Be prepared.
Comments closedBrent Ozar has a step-by-step guide explaining what to do when you CHECKDB reports corruption:
This sounds paranoid, but as an example, here’s a corruption case I had recently: shortly after detecting corruption, the team realized they’d have to revert to a backup of the database from a few days ago. Rather than telling users about that possibility, they let the users keep adding data into the already-corrupt database while the DBAs did troubleshooting. Several days later, as the corruption got worse, even Microsoft couldn’t repair the corruption – and the affected tables went completely offline, permanently. If the users would have been alerted earlier, they could have avoided even more data loss.
Good advice. If you have Pluralsight, I recommend Paul Randal’s course on database corruption. Watch that ideally before you have corruption…
Comments closedSean McCown has released a beta of Minion CheckDB:
We’ve had many of you asking to be part of the Minion CheckDB beta and now is the time. We’re putting the finishing touches on the 1st beta and it’s looking great with some fabulous features.
So this is the open call for beta users. If you’d like to meet Codex before anyone else then send me an email.
We have some requirements though. We don’t want dead beta users. This is your chance to shape the product and we want to hear from you. So if you’re serious about putting the product through its paces then we definitely want you. So you should be ready to provide real feedback, report bugs as you find them, and work with us to fix them.
Considering that I’ve bothered Sean about this at every SQL Saturday the two of us have been at this year, I’d better get moving and join the beta.
Comments closed