This is obvious when you think about it, but it caught me out. We have a set minimum size for our databases of 50MB. It’s slightly abstract and 30MB would probably suit just as well. We also have a reporting system which lets us know if a file is getting full – so we could grow it manually rather than wait for AutoGrow.
I saw a report on a file to say it was full and that the file was only 1MB in size. The file was the TempDB log file. I logged in and grew it to 50MB. The next day I had the same report show up. Logged in again grew it to 50MB, certain that I’d done all this yesterday. So I right-clicked on the database and went to Reports >> Schema Changes – my changes were there but no others.
I checked that there were no jobs shrinking the file and that AutoShrink was turned off.
Today I grew the file to 50MB and then shrank it using DBCC Shrinkfile. Then I checked the report again. There was nothing to show that the file had been shrunk. I said at the beginning it was obvious. The report shows schema changes and so only operations which use Alter Database Modify File show in the report. DBCC commands are not schema changes and so won’t show.
In the meantime I’m going to run a trace and catch that DBCC shrink file command…
Seth Lynch's SQL Blog 