Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to use DBCC command in Sql Server. Many people may not know much about it. In order to let everyone know more, Xiaobian summarizes the following contents for everyone. I hope everyone can gain something according to this article.

1: DBCC

1: What is DBCC?

I'm not a teacher, and I can't say there isn't any impeccable definition, full name: Database Console Commands. As the name suggests,"database console commands," when it comes to "console," my first reaction is chrome developer tools. I wonder what your first reaction will be? In developer tools, you can type in any syntax javascript recognizes on the console. The same is true for sqlserver.

2: How many DBCC commands are there?

You should know that most consoles will provide a help command, such as cmd interface, or mongodb console,

See what help looks like in DBCC?

From the top, I counted from top to bottom, there are 32 commands, but what's interesting is that these 32 commands provided by Microsoft are actually public, and by that I mean there are some unpublished commands that Microsoft uses only... I can make you believe it.

From the above we can probably see that there are about 100 dbcc commands, both public and unpublic, but you have to ask me how to use these dbcc commands. I also can't tell you, after all, my brain is not so developed to remember these boring things, and I don't want to spend time specifically to remember these things, just like English words. But there are ways, most things can't help but pestering, the same for girls, either pestering her to like you, or pestering her to call the police to arrest you.

3: How to remember DBCC commands

books online

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

Type dbcc to find what you are looking for. As you can see from the figure below, dbcc is also roughly divided into four categories. Unfortunately, the unpublished dbcc command is not found in Books Online.

help('xxx')

If you have a general idea of how to use a command, but forget how to assign parameters, you can use help ('xxx ') to help you save development time, such as buffer and ind commands.

II: Practice of Common Orders

1: DBCCTRACEON

Books Online says that this is to enable the specified tracking mark, since it says specified, this implies... There are only two that I often study:

TRACEON(2588)

You know this just now. If you specify the 2588 tag, you can see the unpublished dbcc command, and you can also see the prompts for various command parameters.

TRACEON(3604)

This specified tag is that you can display the results of DBCC Page on the client side, otherwise it will not be displayed. I don't know if you noticed it in the previous chapters.

2：DBCC IND

This command will be used very frequently in this series, because it is used to view the "heap table" or "index" data page information, without it, I have nothing to study, or the old rules, first look at

Its parameter information is as follows:

The first two parameters I think you understand well, I also mentioned in the previous section, one is dbname, one is tablename or viewname or procname, etc., the third parameter is the most

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

1: Displays aggregated index data page information and IAM trace data page information.

0: Displays heap table data page information and IAM trace data page information.

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

-2: Display IAM data page information.

nonclustered ind.: From this ranking list of parameters, you can probably see that there is a pattern of "positive infinity" to 1,0,-1,-2. Think about it carefully. I think you can understand what this means. For example,

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

I really don't want to give examples, because if I continue, I won't be able to say more... Forget it, let me give you an example:

From the above picture, I think you should understand what I am doing, right? You can see that the data for the current non-clustered index is distributed in (PageFID:PagePID)(1:110) ,(1:115)... Wait 4 data pages and you can see (1:114) just their data tracking pages. One might say, how do I know it's an IAM tracking page? You just need to see that IAMFID and IAMPID are null to think of it as an IAM tracking page, and when you see IndexID>0, it is an 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 see what information is in this data page, people are so greedy, temptation ah ~~ old rules, first look at the parameters.

As you can see from the figure, the second and third parameters have no meaning, because I have already used IND to find out which data pages the index is on (fileID:pageID). Let's take a closer look.

Look at the fourth parameter.

0: Output readable form of data page header data, the reason is that in a data page, there are 96 bytes of space to represent a data page header, which can be rich in content.

1: Output page header data in readable form, and there are hexadecimal contents recorded corresponding to slots.

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

3: Output header data in readable form and include readable form for each field in the record.

The above command seems a bit mysterious, I will only give you an example, the rest will be left to you to try ~~~

The replication code is as follows: DBCC TRACEON(3604)DBCC PAGE(Ctrip,1,110,2)

DBCC execution completed. If DBCC outputs error messages, contact your system administrator. PAGE: (1:110)BUFFER:BUF @0x0000000085F8ED00bpage = 0x000000008519A000 bhash = 0x0000000000000000 bpageno = (1:110)bdbid = 8 breferences = 0 bUse1 = 8576bstat = 0x3c00009 blog = 0x32159 bnext = 0x0000000000000000PAGE HEADER:Page @0x000000008519A000m_pageId = (1:110) m_headerVersion = 1 m_type = 2m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x204m_objId (AllocUnitId.idObj) = 58 m_indexId (AllocUnitId.idInd) = 256 Metadata: AllocUnitId = 72057594041729024 Metadata: PartitionId = 72057594040877056 Metadata: IndexId = 2Metadata: ObjectId = 245575913 m_prevPage = (0:0) m_nextPage = (1:115)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 (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED PFS (1:1) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGEDML (1:7) = NOT MIN_LOGGED DATA:Memory Dump @0x0000000010CEA0000000000010CEA000: 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 (0x1230) 4 (0x4) - 3744 (0xea0) 3 (0x3) - 2832 (0xb10) 2 (0x2) - 1920 (0x780) 1 (0x1) - 1008 (0x3f0) 0 (0x0) - 96 (0x60) DBCC execution completed. If DBCC outputs error messages, contact your system administrator. After reading the above, do you know more about how to use DBCC in SQL Server? If you still want to know more knowledge or related content, please pay attention to the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.