0

I have a datafile with the following parameters:

enter image description here

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

enter image description here

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?

3
  • What recovery mode are you using on that database?
    – Ronaldo
    Aug 18 '20 at 21:26
  • Full recovery model, does it make sense? No transaction occurs during shrinkfile operation
    – RaufDBA
    Aug 18 '20 at 21:34
  • Sorry, at first I thought you were concerned about the log file, but now I see you only issued the DBCC SHRINKFILE on the DBAtools_data. the little space left might be related to the fill factor configured for indexes or space in each page that is not big enough to hold "one more row" of data.
    – Ronaldo
    Aug 18 '20 at 21:46
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).

3
  • 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 size of file to that value. 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. So, we can come to the conclusion that min value is 1 which is also pointed out in GUI windows of Shrink command.
    – RaufDBA
    Aug 19 '20 at 9:50
  • By the way, thank you for your time for testing and interest. I think this remark could be added into BOL. What do you think about it?
    – RaufDBA
    Aug 19 '20 at 9:51
  • 1
    Indeed! Yes, 0 means "inital" size as per the file header page. DBCC PAGE for page 0 with printoption 3 show us exactly what you describe above. I just tested it. So I was off mark with 0 means "dummy". I agree, this (the behaviour that 0 meand "inital" and something else means just that plus set "initial) should be documented. I suggest you add your comment above to Answer, as reference for the next reader of this thread. Aug 19 '20 at 11:18
1

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.