A Fine Slice Of SQL Server
My ZoraDB database clearly stuck in an encryption in progress state.
With the database being small the encryption process should not take long at all, I was confused.
I then decided to try and turn off the encryption.
Read on for the solution.
Comments closedDavid 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.
Comments closedDavid 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.
Comments closedJoey 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.
Comments closedWayne 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.
Comments closedArun 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.
Comments closedWayne 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:
Create a table (permanent / temporary) or table variable to hold the output.
Insert into this table the results of the DBCC PAGE statement by using INSERT INTO / EXECUTE.
Select the data that you are looking for from the table.
Read on for his code as well as important caveats.
Comments closedDave 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.
Comments closedWayne 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.
Comments closedDBCC 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.
Comments closed