DBCC CLONEDATABASE is now working with SQL Server 2016

You might have used this command before or you might be unaware of this command. This was introduced with SQL Server 2014 SP2 and it allowed as to clone a database easily without getting data but with statistics. This is very useful when you have to troubleshoot a production database but you cannot do much with it because;

1. You should not use it for troubleshooting during peak hours. 
2. You cannot do all testing as you want because of some policies and security implementations.

Since this command allows you to take a copy of database without taking data, it is fast and useful for checking various things.

Here is the way of creating a clone:

DBCC CLONEDATABASE(AdventureWorks2014, CloneAdventureWorks2014);

This is the result you get when you execute against SQL Server 2016 (Note that this requires SP1, this does not run in RTM)

This what you see if you execute in RTM

Since this does not have data, you cannot perform all activities performed for troubleshooting.

For example, this is possible and it produces the required result:

USE CloneAdventureWorks2014;
GO

DBCC SHOW_STATISTICS ('Sales.SalesOrderHeader', 'PK_SalesOrderHeader_SalesOrderID')  

But this is does not provide the output as we need:

USE CloneAdventureWorks2014;
GO

SET STATISTICS IO ON
SELECT * FROM Sales.SalesOrderHeader;

SQL Server Brain Basher of the Week #011 - DBCC

An interview question.

What does DBCC stand for?

When classes related to Administration are being conducted, I always ask this question and as expected, I always get the same answer which is wrong. Most think that this is as Database Consistency Checker but that is the oldest way of describing this.

DBCC Command family was originally supporting Consistency related commands. Version upgrades added more and more commands to this family and they were not only for checking the consistency of our databases. Therefore, since SQL Server 2000, this was started referring as Database Console Commands. 

Read more on DBCC at: https://msdn.microsoft.com/en-us/library/ms188796.aspx.

DBCC IND

DBCC IND command is one of the important commands used when indexes are analyzed. Specifically this can be used for seeing the linkage between pages associated with a given table. It requires three parameters;

DBCC IND ( {‘db_name’ | ‘db_id’}, {‘table_name’ | ‘table_id’}, {‘index_name’ | ‘index_id’ | –1}

• db_name | ‘db_id – requires database name or id. If 0 or ‘’ passed, current database will be used.
• table_name | table_id – requires table name or object id of the table.
• index_name | index_id | –1 – requires index id of the table. If –1 is used, result is generated for all the indexes.

Here is brief on output of IND command.

PageFID	File number where the page is located
PagePID	Page number for the page
IAMFID	File ID where the IAM page is located
IAMPID	Page ID for the page in the data file
ObjectID	Object ID for the associated table
IndexID	Index ID associated with the heap or index
PartitionNumber	Partition number for the heap or index
PartitionID	Partition ID for the heap or index
iam_chain_type	he type of IAM chain the extent is used for. Values can be in-row data, LOB data, and overflow data.
PageType	Number identifying the page type; 1 - Data page 2 - Index page 3 - Large object page 4 - Large object page 8 - Global Allocation Map page 9 - Share Global Allocation Map page 10 - Index Allocation Map page 11 - Page Free Space page 13 - Boot page 15 - File header page 16 - Differential Changed Map page 17 - Bulk Changed Map page
IndexLevel	Level at which the page exists in the page organizational structure. The levels are organized from 0 to N, where 0 is the lowest level of the index and N is the index root
NextPageFID	File number where the next page at the index level is located
NextPagePID	Page number for the next page at the index level
PrevPageFID	File number where the previous page at the index level is located
PrevPagePID	Page number for the previous page at the index level

Here is an example for running the command;

DBCC IND (tempdb, 'TestTable', 2)