DBCC Checks For Large Databases

David Alcock gives a couple methods for performing consistency checks against gigantic databases:

One way to achieve this is to  split up the consistency checks covering smaller objects and native functionality allows us to do just that, we can perform the checks at the table level or indeed if they are implemented at the filegroup level too using the DBCC CHECKFILEGROUP command.

How to go about this is pretty straightforward; take the list of tables, split them into equal(ish) groups. The groups now form a pool of objects and within a nightly (or daily) window perform the check on each object in the pool. This effectively spreads a database consistency check over multiple days, you avoid the impact on production activities but also ensure all objects are checked over time.

Read on for the solution.  I’m also a big fan of Minion CheckDB, which is designed to handle this type of scenario as well.

Understanding DBCC PAGE

Kevin Feasel

2017-02-03

DBCC

David Alcock looks at the undocumented DBCC PAGE command:

This is an error that has been picked up on one of my test systems and indicates that SQL Server has detected a torn page, that is a page that has been incorrectly written by SQL Server and possibly indicates a problem in the IO subsystem.

The problem here is that whilst we know the database and the page where the error has occurred we don’t know the specific table the page belongs and importantly what type of page is in error. The reason why the page type is important is because this will drastically impact our recovery process but the first thing we will do is check a system table to see if any other page errors have been reported:

DBCC PAGE is just about the most-documented undocumented command around.  Worth a read.

Cloned Database Size

Joey D’Antoni tests how large database files are after running DBCC CLONEDATABASE:

One of the recent feature introductions to SQL Server is dbcc clonedatabase, a feature that lets you create a “data-less” clone of you database. All of the statistics and objects come into your cloned database, however none of the data does. This is perfect for development or performance tuning exercises, where you want all the metadata, but do not want the security risk of dealing with production data.

Recently I had the opportunity to use clonedatabase on a very large database. I was concerned about the size of the data files and how this would impact space on my volumes. Books Online is fairly clear, but I wanted to see for myself.

Click through for the answer.

Finding Suspect Objects

Kevin Feasel

2017-01-20

DBCC

Wayne Sheffield shows how to find which specific objects are suspect without running a full CHECKDB:

You’d really like to know what tables are affected without having to wait. Luckily(?), this corruption was recorded in msdb.dbo.suspect_pages, and having just recently read Paul Randal’s post here, we know we can use DBCC PAGE to determine this information. And, after having read my prior blog post, you know that we can automate DBCC PAGE, so we can use our new friend “WITH TABLERESULTS” to find out what objects have been corrupted.

The suspect_pages table, documented here, has three particular columns of interest: database_id, file_id and page_id. These correspond nicely to the first three parameters needed for DBCC PAGE. To automate this, we need to know what information we need to return off of the page – and from Paul’s post, we know that this is the field “METADATA: ObjectId”. For this code example, let’s assume that this corruption is on page 11 of the master database (just change “master” to the name of your 2TB database).

Read on for a script, including a script which checks all such suspect pages, and the possibly-better solution as of SQL Server 2012.

DBCC DBINFO For Finding CHECKDB Executions

Kevin Feasel

2017-01-04

DBCC

Arun Sirpal uses the DBCC DBINFO command to check the last time DBCC CHECKDB ran:

What about separate checks?

Again I dropped and re-created the database to get back to level playing field.

Let’s work through the DBCC check commands, after a command I will check dbi_dbccLastKnownGood to see if it gets updated.

Click through for details on when the dbccLastKnownGood value gets updated.

Checking Last CHECKDB Date Using DBCC PAGE

Kevin Feasel

2016-12-22

DBCC

Wayne Sheffield shows how to get the last time DBCC CHECKDB ran on each database:

The “trick” to making this work is to encapsulate the DBCC command as a string, and to call it with the EXECUTE () function. This is used as part of an INSERT INTO / EXECUTE statement, so that the results from DBCC PAGE are inserted into a table (in this case a temporary table is used, although a table variable or permanent table can also be used). There are three simple steps to this process:

  1. Create a table (permanent / temporary) or table variable to hold the output.

  2. Insert into this table the results of the DBCC PAGE statement by using INSERT INTO / EXECUTE.

  3. Select the data that you are looking for from the table.

Read on for his code as well as important caveats.

Preventing DBCC DBREINDEX?

Kevin Feasel

2016-11-23

DBCC

Dave Mason is looking for a way to prevent a user from running DBCC REINDEX:

After a little digging, I discovered the related database user is a member of the db_ddladmin fixed database role. Members of that role are permitted to run DBCC REINDEX. Since I have existing (more sensible) code in place for index maintenance, I don’t want the DBCC REINDEX operations to continue. Here’s the problem: I can’t find a direct way to DENY a database user from running DBCC commands. T-SQL syntax doesn’t support something like DENY DBCC TO <user> or DENY DBCC REINDEX TO <user>. MSDN documentation tells me the equivalent ALTER INDEX command requires at minimum ALTER permission on the table or view. I guessed that revoking or denying ALTER TABLE privileges might prevent a user from executing DBCC DBREINDEX, but that does not appear to be the case.

That’s painful.

Saving DBCC Results

Kevin Feasel

2016-11-17

DBCC

Wayne Sheffield shows how to save CHECKDB results to table:

Okay, let’s try this out. At the above BOL links, there are two DBCC commands that are documented to use the TABLERESULTS option: OPENTRAN and SHOWCONTIG. Testing all of the other DBCC commands shows that this option can also be used on the CHECKALLOC, CHECKDB, CHECKFILEGROUP and CHECKTABLE commands. I’m going to continue this post with using DBCC CHECKDB. If you check BOL, it does not mention the TABLERESULTS option.

As Wayne notes, you can do this for other DBCC commands as well.

DBCC OPTIMIZER_WHATIF

Derik Hammer shows how to use DBCC OPTIMIZER_WHATIF to get an idea of how your query would run with different hardware:

DBCC OPTIMIZER_WHATIF can be used to pull down your resources or augment them. Often the differences in the execution plans have to do with parallelism and memory grants. This is an example of an execution plan running on an under powered development machine.

This is a good tool to help figure out what an execution plan probably would look like in production when your test environment is much smaller.

CHECKDB Snapshots

Kevin Feasel

2016-11-11

DBCC

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.

Categories

May 2017
MTWTFSS
« Apr  
1234567
891011121314
15161718192021
22232425262728
293031