SQL Server DBCC command details, serverdbcc

SQL Server DBCC command details, serverdbcc

Today, the R & D center will hold an annual meeting. In the evening, my departments will have dinner. I personally like to drink dry red. You can enjoy the cool and bitter taste in the dry red... Why isn't life like this ??? Just as the ceo came back from the United States with a dry red, then I was happy to enjoy it .... One word... Great .... Drinking too much... My head hurts now .... When I got back to the subway, I thought I was busy looking for a house this weekend. I didn't read a book at all, and I didn't go on my blog. I remembered that a buddy in my previous blog said that DBCC was popularized, although a little confused... See if you can write new tricks .... No matter what language or database you want to learn, you have to look deeper than others... The most unfortunate thing is to stay on the surface, so after each version upgrade, there are some new tricks, and then you think it's amazing, but is it true? A very practical example is the syntactic sugar in C # versions ..... After you use ILdasm, you will find that the Foundation is not enough.

C # has the advantage of reading the source code and ILdasm... Basically, you can let it see you naked, but sqlserver is a bit special. It is not a programming language and you will not learn it through decompilation, the deeper layer of the data page can only be the underlying data page .. . So this is the basic skill to learn sqlserver, and it is a little bit complicated...

I. DBCC

1: What is DBCC?

I am not a teaching teacher, and I cannot say that there is no impeccable definition. Full name: Database Console Commands. As the name implies, "Database Console Commands", when it comes to "console", my first response is chrome's developer tool. I don't know what your first response will be like? In Developer Tools, you can type any syntax that can be recognized by javascript on the console... The same is true for SQL Server.

2: How many commands does DBCC have?

You should know that most of the consoles provide a help Command, such as the cmd interface or the mongodb login console,

Then let's see what the help in DBCC will do ???

I counted 32 commands from top to bottom, but it is interesting that the 32 commands provided by Microsoft are actually made public, I mean that some undisclosed commands are only used by Microsoft .... I can make you see.

As we can see from the above, there are about 100 public and undisclosed dbcc commands, but you have to ask me how to use these dbcc commands... I cannot tell you that, after all, my mind is not so developed to remember these boring things. Besides, I don't want to take the time to remember these things, just like remembering words in English... But there are still some solutions. Most things can't help but be entangled. This is also true for girls. Either she is entangled in you, or she is entangled in an alarm to arrest you.

3: How to remember the DBCC command

<1> books online

Online books can be said to be the mother of sqlserver. In this world, you will not find more authoritative and comprehensive information than sqlserver. You can click here to see its power, and then you can

Type dbcc to find what you need. From this, we can see that dbcc is also divided into four categories. Unfortunately, unpublished dbcc commands cannot be found in books online.

<2> help ('xxx ')

If you have a general understanding of the command usage, but you forget how to assign parameters for a moment, you can use help ('xxx') to help you save development time, for example, the buffer and ind commands.

Ii. Common commands

1: DBCC TRACEON

In books online, This is to enable the specified tracking flag. Since it is specified, this is the meaning... I often study only two of them:

<1> TRACEON (2588)

Now you know that if the 2588 mark is specified, you can see the undisclosed dbcc commands. You can also see the prompts of various command parameters.

<2> TRACEON (3604)

The specified tag can display the result of DBCC Page on the client. Otherwise, it will not be displayed. Do you know whether you have noticed it in the previous chapters ???

2: DBCC IND

This command is frequently used in this series, because it is used to view the data page information of the "heap table" or "Index". Without it, I will not study it, let's look at the old rules first.

Its Parameter information, such:

I want to understand the first two parameters. I also mentioned in the previous section that one is dbname, the other is tablename or viewname or procname, and the third is the most

Interestingly, what does 1, 0,-1,-2 mean ???

 

<1> 1: displays the clustered index data page information and IAM tracking data page information.

<2> 0: displays the heap table data page information and IAM tracking data page information.

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

<4>-2: displays the IAM data page.

<5> nonclustered indid: From the ranking list of this parameter, you can see that it is a "positive infinitely large" to 1, 0,-1,-2 mode, I think you know what this means, for example

2 indicates the first non-clustered index, 3 indicates the second non-clustered index, and so on .....

 

I really don't want to give an example, because I can't say anything more... Let's take an example:

 

Through the above figure, I think you should understand what I am doing, right ??? We can see that the data of the current non-clustered index is distributed in (PageFID: PagePID ).... on the four data pages, we can see that () is only their data tracking page. Some may say, How do I see the IAM tracking page? You only need to check that IAMFID and IAMPID are null and can be considered as the IAM tracking page. When you see IndexID> 0, it is the index page.

 

3: DBCC PAGE

This command is also frequently involved in this series, because it is indeed very useful. When I use IND to export the data page, the next step is to see what information exists in this data page, people are greedy and tempting ~~~ Old rules: first look at the parameters.

As you can see, the second and third parameters are meaningless, because I have used IND to query which data pages (fileID: pageID) The index is on.

Take a look at the fourth parameter.

<1> 0: output the data in the readable form of data page header. The reason is that in a data page, there are 96 bytes to indicate a data page header, the content is rich ....

<2> 1: output the readable data page header data, and the hex content of the corresponding slot.

<2> 2: Output The hexadecimal data of the entire data page header, including (page header, content, and slot). This is my most commonly used command.

<3> 3: output the data page header data in the readable form, including the readable form of each field in the record.

 

The above command seems a bit mysterious. I will just give you one example. Let's try the other ~~~

Copy codeThe Code is as follows:
Dbcc traceon (3604)
Dbcc page (Ctrip, 1,110, 2)

DBCC execution is complete. If DBCC outputs an error message, contact the system administrator. PAGE: () BUFFER: BUF @ 0x0000000085F8ED00bpage = 0x0000000000008519a000 bhash = 0x0000000000000000 bpageno =) bdbid = 8 breferences = 0 bUse1 = 8576 bstat = 0x3c00009 blog = 0x32159 bnext = 0x00000000000000000000page HEADER: Page @ 0x000000008519A000m_pageId =) m_headerVersion = 1 m_type = 2m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x204m_objId (AllocUnitId. idObj) = 58 m_indexId (AllocUnitI D. idInd) = 256 Metadata: AllocUnitId = 72057594041729024 Metadata: PartitionId = 72057594040877056 Metadata: IndexId = 2 Metadata: ObjectId = 245575913 m_prevPage = (0: 0) m_nextPage =) pminlen = 909 m_slotCnt = 8 m_freeCnt = 784m_freeData = 7392 m_reservedCnt = 0 m_lsn = (141: 194: 170) m_xactReserved = 0 m_xdesId = (0: 0) m_ghostRecCnt = 0m_tornBits =-788728362 Allocation StatusGAM () = ALLO Cated sgam () = allocated pfs () = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF () = CHANGEDML () = NOT MIN_LOGGED DATA: Memory Dump @ login: 01020000 04020001 00000000 20178d03 †................ 20171000010cea010: 73000000 01000800 3a000000 1003e01c running s ....... :....... 20171000010cea020: 6e000000 01000000 8d000000 c2000000 0000n ............... 20171000010cea030: aa00000 0 00000000 00000000 d6f5fcd0 large †................ 20171000010cea040: 00000000 00000000 00000000 00000000 †........................... 20171000010cebfe0: 21212121 21212121 21212121 21212121 million †!!!!!!!!!!!!!!!! 20171000010cebff0: 5019c015 3012a00e 100b8007 f0036000 2017p... 0 ......... '. offset table: Row-Offset 7 (0x7)-6480 (0x1950) 6 (0x6)-5568 (0x15c0) 5 (0x5) -4656 (0x1230) 4 (0x4)-3744 (0xea0) 3 (0x3)-2832 (0xb10) 2 (0x2) -1920 (0x780) 1 (0x1)-1008 (0x3f0) 0 (0x0)-96 (0x60) DBCC execution is complete. If DBCC outputs an error message, contact the system administrator.