Shoudn't DBCC SHIRNKFILE(Data_File, 0) command shrink data file to the last allocated extent?

Question

I have a datafile with the following parameters:

When I execute DBCC SHRINKFILE (DBAtools_data,1), it shows the following resuls:

I am OK with that. My complaint is about DBCC SHRINKFILE (DBAtools_data,0) command. When I run it nothing changes. I thought that it will shrink the datafile to the last allocated extent, in order words the same result as above. Could somebody clarify it for me?

Answer 1

Without having access to the source code for SQL Server, we can only guess. So,

Edit: 0 means shrink to initial size as per page header. My speculation that it means dummy, below, is incorrect.

SET SPECULATION MODE ON

0 makes no sense. If you want to get rid of the file, you have the EMPTYFILE option. I.e., why would you want to have a file which is 0 MB in size.

I just did a test, and indeed 0 means it doesn't do anything. We could argue that an error messages would be better: "0 MB is not valid file size.", or something like that. But apparently MS decided to leave 0 as a valid valid for the command and implementing it as a dummy.

SET SPECULATION MODE OFF

I suggest you file a request with MS to either document that 0 means "dummy", or behavior change so we'll get an error message (less likely to happen).

Answer 2

Well, I guess I found out the reason although I couldn't find anything about it from BOL. It is obvious that anything different than 0 (in my case it is 1) will try to shrink file till that value and as BOL states, it will also try to reset the size of file to that value (1 MB). In case of 0, it only shrinks and resizes data file to INITIAL size of the file at the time of creation. That value (minSize) is stored in header page of the file, I found it with undocumented DBCC Page command:

DBCC TRACEON(3604)
GO
DBCC PAGE('DBAtools', 1, 0, 3) WITH TABLERESULTS

We can come to the conclusion that DBCC SHRINKFILE (Data_File, 0) command will shrink datafile till the value which was indicated at the time of creation of data file. In order words DBCC SHRINKFILE (Data_File, 0) is the same as DBCC SHRINKFILE (Data_File)