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.
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.
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.
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.
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.
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 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.
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.
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.
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:
During 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.