A Fine Slice Of SQL Server
Am playing around with Always Encrypted for the first time. I was just following along the basic tutorial and encrypted some columns in my AutoDealershipDemo database. But then I decided to go crack open the data page using my friend DBCC PAGE.
Read on to see how you can get the results of DBCC PAGE into a table. My recollection is that there are some limits to what it can write into the table, but it’s pretty good on the whole.
Comments closedI had an inquiry about dbcc dumptrigger today and realized that state filtering was added to dbcc dumptrigger but not well known.
You can collect a process dump (SQL Dumper) when a specific error occurs using XEvent (error_reported/ex_raise2 events with dump capture action) or dbcc dumptrigger.
The common use of dbcc dumptrigger is: dbcc dumptrigger(‘set’, 208) to produce a dump when error 208 is encountered.
This is pretty useful, especially if you’re troubleshooting a bug in the database engine.
Comments closedPaul Randal takes us through another latch:
Continuing my series of articles on latches, this time I’m going to discuss the DBCC_OBJECT_METADATA latch and show how it can be a major bottleneck for consistency checks prior to SQL Server 2016 under certain circumstances. The issue affects DBCC CHECKDB, DBCC CHECKTABLE, and DBCC CHECKFILEGROUP, but for clarity I’ll just reference DBCC CHECKDB for the rest of this post.
You might wonder why I’m writing about an issue that affects older versions, but there are still a huge number of SQL Server 2014 and older instances out there, so it’s a valid topic for my series.
Read on to understand what DBCC_OBJECT_METADATA does and how it can become a bottleneck on those older versions of SQL Server.
Chad Callihan explains what that CHECKDB option which should sound really scary means:
Just because REPAIR_ALLOW_DATA_LOSS might get your database up and running quickly doesn’t make it a good option. It could wrongly be considered a shortcut or an easy button to get handle corruption. Some may run REPAIR_ALLOW_DATA_LOSS, see their database back online, and call it a day. Let’s look at why some things are too good to be true.
Read the whole thing.
Comments closedTaryn Pratt has a process for offloading maintenance operations onto another server:
Early on when I started working on the SQL Servers at Stack Overflow, we were taking daily backups. We had a handful of databases that were being restored for other processes, but the majority weren’t actively tested to ensure the backups were good. Since you never want to be in a situation where you need to restore a database and find it doesn’t work, my goal was to create a process to automatically restore our backups to a separate server, and then run
DBCC CHECKDBon it.
This is a T-SQL-driven process and I appreciate that. If you want a Powershell-driven process, Kevin Hill has you covered.
Comments closedAaron Bertrand shares some thoughts on CHECKDB:
We have a lot of data. Some of that data is stored in large databases (dozens of terabytes each). In some shops, this is an excuse to not run integrity checks. We are not one of those shops.
But we don’t run full
CHECKDBoperations in production; we have a set of servers dedicated to testing our restores and running checks. We follow a lot of the guidance in these articles:– CHECKDB From Every Angle: Consistency Checking Options for a VLDB
Read the whole thing, even if you aren’t dealing with 30+ TB databases.
Comments closedLonny Niederstadt has a public service announcement:
Trace flag 2549 is no longer required for checkdb or checktable in SQL Server 2016++.
Trace flag 2562 may still benefit checkdb on a system (depends on disk characteristics and tempdb) in SQL Server 2016++, but is not expected to benefit checktable due to the already-limited scope of checktable activity.
Click through for the full story.
Comments closedArun Sirpal shows us how to build an Azure Function:
The title is a mouthful and so is this post. In the past I have linked to blog posts from Microsoft that say consistency checks for Azure SQL Database is the responsibility of Microsoft. (https://azure.microsoft.com/en-gb/blog/data-integrity-in-azure-sql-database/)
However, Paul Randal got me thinking about his thoughts on it (via his insider email). Forming the core of this post. If you desire to run DBCC CHECKDB against Azure SQL Database (which I know people do) – how can you do this? There are many ways, but for this blog post – Enter Azure functions. There are many moving parts to this, but once setup and coded it is a very satisfying experience. Let’s dig in. I am NOT going to copy and paste every little element of the high-level guide from Microsoft, there is no point in that but I will show you the links that you need to setup the relevant function app project then the tailored bits around CHECKDB forms the bulk of this post.
This isn’t necessary to do, but if you want to learn how Azure Functions work, it’s a good example of working through the mechanics.
Comments closedPaul Randal explains the order of importance for maximizing DBCC CHECKDB performance:
There was a Twitter #sqlhelp question last week from someone who was provisioning a server for offloading DBCC CHECKDB from a production server, and he asked whether to prioritize CPU or memory when configuring the server. My answer was neither!
Read on to learn the answer.
Comments closedPaul Randal answers a reader question:
I had a blog comment question a few days ago that asked why emergency-mode repair requires the database to be in EMERGENCY mode as well as SINGLE_USER mode.
All repair operations that DBCC CHECKDB (and related commands) performs require the database to be in single-user mode so there’s a guarantee that nothing can be changing while the checks and repairs are done. But that doesn’t change the behavior of what repair does – that needs emergency mode too.
Read on for an explanation of what emergency mode is and why we need it to run CHECKDB repair operations.
Comments closed