The DBCC commands in SQL Server are described in detail _mssql

Today's research and Development Center for the annual meeting, evening is the respective departments of dinner, I personally like to drink dry red, in the dry red you can appreciate the kind of ups and downs ... Life is not like this??? The CEO came back from the United States with a dry red, and then I was happy to go through the addiction .... A word ... Cool.... Drink a little bit more ... Now the head still hurts .... Back on the subway to think this weekend busy looking for a house, the book did not look at all, also not on the blog and then fooled, think of the last blog has a friend said Universal Universal DBCC, although a bit of a daze ... See if you can write a little bit of a twist .... No matter what language you study or database, you have to look deeper than others. The saddest thing is to stay in the surface, so each version of the upgrade, there are some tricks, and then you feel too damn magical, but the truth is really this? A real example of this is the syntactic sugars in each version of C # .... When you ildasm, you will find that there are some foundations that cannot be

The basic thing, C # has a benefit, is that you can see the source code, you can also ildasm it ...  Basically you can let it come to you naked, but SQL Server is a bit special, it is not a programming language, you certainly do not learn by decompile, then it is a deeper layer of things can only be the underlying data page. 。 So this is to learn the basic skills of SQL Server, pull a bit more ...

One: DBCC

1: What is DBCC

I'm not a teaching teacher, and I can't say there's no watertight definition, full name: Database Console Commands. As the name implies "Database Console command", speaking of "console", my first reaction is the chrome developer tool, I do not know what your first reaction will be? Developer tools, as long as JavaScript can recognize the syntax, you can type in the console ... The same thing that SQL Server can recognize.

2:DBCC, how many orders are there?

You should know that most consoles provide a help command, such as the Cmd interface, or Mongodbのconsole,

and see how the help in DBCC is going to be???

From the above, I count from top to bottom, there are 32 commands, but interestingly, Microsoft's offer of these 32 commands are actually open to the public, I mean that there are still some non-public orders only Microsoft to use .... I can make you see the truth.

From the above we probably also see that the open and unpublished DBCC commands add up to about 100, but you have to ask me how these DBCC commands work ... I can not tell you, after all, my brain is not so developed to remember these boring things, and also do not want to spend time dedicated to remember these things, just like English words ... But the solution is still some, most things can not help the stalker, the girl is also so, or entangled to her like you, or entangled to her alarm to catch you.

3: How to remember the DBCC command

<1> Books Online

Online books can be said to be the mother of SQL Server, in this world you will not find more authority than it, but also the full information, you can click here to see its divinity, and then you can

Type DBCC to find what you need to look for, and as you can see from the image below, DBCC is probably divided into 4 categories, and it is regrettable that the unpublished DBCC commands are not found in Books Online.

<2> help (' xxx ')

If you have a general idea of how to use commands, but forget how to assign them, you can use Help (' xxx ') to save you time, such as buffer and IND commands.

Two: The practice of the commonly used commands

1:DBCC Traceon

The online book says that this is to enable the specified tracking flag, since the designation, this is the meaning of ... I used to study only two of them:

<1> Traceon (2588)

As you know just now, if you specify a 2588 tag, you can see the unpublished DBCC commands, and you can also see the various command parameters prompts.

<2> Traceon (3604)

This specified tag is the result of the DBCC page can be displayed on the client, otherwise it will not appear, do not know if you have noticed in the previous chapters???

2:DBCC IND

This command is used very frequently in this series, because it is used to view the "heap table" or "Index" data page information, without it, I have no research, or the old rules, first look at

Its parameter information, as shown in the following figure:

Top two parameters I want you to understand, I also mentioned in the previous section, one is dbname, one is tablename or viewname or procname, and so on, the third parameter is the most

Interesting, what does this 1,0,-1,-2 mean???

<1> 1: Displays clustered index data page information and IAM trace data page information.

<2> 0: Display heap table data page information and IAM trace data page information.

<3>-1: Displays all data page information, such as (IAM, index data page, heap table data page).

<4>-2: Displays the IAM data page information.

<5>nonclustered indid: From the rank list of this parameter, you may also see a "positive infinity" to the 1,0,-1,-2 model, and think about the meaning I think you know, like

Say 2 represents the first nonclustered index, 3 represents the second nonclustered index, and so on ...

I really do not want to give an example, because further down the words, can not say ... Well, let's just give you an example:

Through the above diagram, I think you should understand what I am doing??? You can see that the data for the current nonclustered index is distributed in (Pagefid:pagepid) (1:110), (1:115) .... On 4 pages of data, you can see (1:114) just their data tracking page. One might say, how do I see the IAM tracking page? All you have to do is look at Iamfid and iampid as null to be the IAM tracking page, and when you see INDEXID>0, it's the index page.

3:DBCC PAGE

This command is also frequently involved in this series, because it is really useful, when I use IND to export the data page, the next step is to look at this data page in the end what information, people are so greedy, temptation ah ~ ~ ~ The old rules, first look at the parameters.

As you can see from the diagram, the second and third of these two parameters have no meaning, because I have used IND to query out which data pages (Fileid:pageid) The index is above, and below we carefully

Take a look at the fourth argument.

<1> 0: Output readable form of data page header data, because of this, in a data page, there are 96 bytes of space to represent a data page header, the contents of which can be rich ....

<2> 1: Output readable form of data page header data, and also has slot corresponding record hexadecimal content.

<2> 2: Hexadecimal data that outputs the entire data page header, including (header, content, and slot), which is my most common command.

<3> 3: Outputs the readable form of the data page header data, and includes the readable form of each field in the record.

The above command looks a bit iffy, I just give one example, others to let you try it ~ ~ ~

Copy Code code as follows:

DBCC Traceon (3604)
DBCC PAGE (ctrip,1,110,2)

DBCC execution completed.

If DBCC prints an error message, contact your system administrator.  PAGE: (1:110) buffer:buf @0x0000000085f8ed00 bpage = 0x000000008519a000 Bhash = 0x0000000000000000 Bpageno            = (1:110) bdbid = 8 Breferences = 0 BUse1 = 8576 bstat = 0x3c00009 Blog = 0x32159         bnext = 0x0000000000000000 PAGE header:page @0x000000008519a000 M_pageid = (1:110) m_headerversion = 1    M_type = 2 M_typeflagbits = 0x0 M_level = 0 m_flagbits = 0x204 M_objid (allocunitid.idobj) = 58  M_indexid (allocunitid.idind) = 256 Metadata:allocunitid = 72057594041729024 Metadata:partitionid = 72057594040877056 Metadata:indexid = 2 Metadata:objectid = 245575913 M_prevpage = (0:0) m_n Extpage = (1:115) Pminlen = 909 M_slotcnt = 8 m_freecnt = 784 M_freedata = 7392 M_reservedc NT = 0 M_lsn = (141:194:170) m_xactreserved = 0 M_xdesid = (0:0) m_ghostreccnt= 0 M_tornbits = -788728362 allocation Status GAM (1:2) = Allocated SGAM (1:3) = Allocated PFS (1:  1 = 0x60 Mixed_ext Allocated 0_pct_full DIFF (1:6) = CHANGED ML (1:7) = Not min_logged data:memory 
Dump @0x0000000010cea000 0000000010cea000:01020000 04020001 00000000 00008d03† ......... 
0000000010cea010:73000000 01000800 3a000000 1003e01c†s ......... 
0000000010cea020:6e000000 01000000 8d000000 c2000000†n ..... ...... 
0000000010cea030:aa000000 00000000 00000000 d6f5fcd0† ........ ...

0000000010cea040:00000000 00000000 00000000 00000000† .................. ... 
0000000010cebfe0:21212121 21212121 21212121 21212121†!!!!!!!!!!!!!!!! 0000000010cebff0:5019c015 3012a00e 100b8007 f0036000†p ... 

0. ... '. OFFSET Table:row-offset 7 (0x7)-6480 (0x1950) 6 (0x6)-5568 (0X15C0) 5 (0x5)-4656 (0x1        
230) 4 (0x4)-3744 (0XEA0) 3 (0x3)-2832 (0XB10)2 (0x2)-1920 (0x780) 1 (0x1)-1008 (0x3f0) 0 (0x0)-(0x60) DBCC execution completed. If DBCC prints an error message, contact your system administrator.