SQL Server DBCC SHRINKFILE does not work

Method 1, rebuild the clustered index.

Method 2, rebuild the heap table.

--------------------------------------------------------------------------------------------------------------- --------------------------------------------------

Principle description. The operating unit of DBCC SHRINKFILE is the area (extent also some books are said to be extended), data exists in the data page, and the collection of 8 data pages is called a zone (extent).

The area has a uniform area and a mixed area. If an object occupies more than 8 pages (a page in size is 8KB, so the size of a region is 64KB) the space allocation of the object is

To be in a district (that is, allocate at least 64KB each time), if the object is less than 64KB, each time the space allocation is in page units (that is, it gives it 8KB space at a time).

Example

CREATE TABLE T (X int, String nvarchar (4000));
Go-the first line of the table is close to 8KB so a page can only be saved on one line.

DECLARE @i as int = 1;
While @i <=1000
Begin
Insert into T (x,string) values
(1,replicate (N ' A ', 4000)),
(2,replicate (N ' A ', 4000)),
(3,replicate (N ' A ', 4000)),
(4,replicate (N ' A ', 4000)),
(5,replicate (N ' A ', 4000)),
(6,replicate (N ' A ', 4000)),
(7,replicate (N ' A ', 4000)),
(8,replicate (N ' A ', 4000));
Set @i = @i +1;
End
Go-Inserts 8000 rows of data into the table.

The data pages are as follows,

DBCC SHOWCONTIG (' T ');

You can see that there are 8000 sides to the T table.

Then we do one thing, is to use the Delete command to delete the data in the table 7/8, the code is as follows.

Delete from T

where t.x! = 1;

This leaves only the line of x = 1. It takes up 1000 pages of space, but is the fact really the main sample? Let's take a look at the DBCC SHOWCONTIG (' T ')

DBCC SHOWCONTIG (' T ');

You can see that it still occupies 4,124 pages than the 1000 pages we want. Because table T is a heap table, its internal data is unordered, which means it is not 1, 2, 3, 4, 5, 6, 7, 8

This way is saved. The inner zone could be like this.

Then again, what if Table T is orderly? The first 1000 pages will be 1, which means that 7,000 pages will not appear 1. Heap tables are unordered and each page may

Appears 1.

Focus:

The point of the problem is that the DBCC SHRINKFILE object is the area, that is, only the empty area can be reclaimed by Shrinkfile, the return of just the DBCC SHRINKFILE (' T ') can be seen with 4,124 pages

exists, stating that 500 (4123/8) of the data is stored in more than 1 areas, so it is not recycled. If the 7,000 pages in the back of a clustered table are in the same area, they will be reclaimed. Because these areas are empty.

--------------------------------------------------------------------------------------------------------------- -----------------------------------------------

Method 1,:

Rebuild the heap table.

ALTER TABLE T rebuild;

Go

You can see that the page's occupancy is changed from 4123 pages back to 1001 pages.

Method 2,

Add Clustered Index

Create clustered Index idx_x
On T (X);
Go

You can see that the clustered index can also achieve the same effect.

Summary: The DBA periodically manages the table to reduce its fragmentation, reduce the IO amount at select, and increase the speed of select. In fact the clustered index pair insert can be said to be basically not too

Big impact. The benefits to select, update, delete are significant. This example can see that it is also advantageous for space occupancy.

So, it's pretty good to add a clustered index to the table.

SQL Server DBCC SHRINKFILE does not work