I recently ran into an issue when migrating a database from DataDude to SSDT in Visual Studio 2012. No matter how hard I tried I could not get the deployment, actually I should say Publish now, to honor the sizing that I defined. For example: I defined a data file that I assigned to PRIMARY as the only data file for this database. I made sure to add the File Size and File Growth properties like a good DB guy should, yet when I generated the create script they were just not there. Here is what I included in the project
ALTER DATABASE [$(DatabaseName)]
ADD FILE
(
NAME = [MyApplication],
FILENAME = '$(DefaultDataPath)$(DefaultFilePrefix).mdf', SIZE = 50MB , FILEGROWTH = 100MB
) TO FILEGROUP [PRIMARY]
However here is what the publish output looked like:
CREATE DATABASE [$(DatabaseName)]
ON
PRIMARY(NAME = [MyApplication], FILENAME = '$(DefaultDataPath)$(DefaultFilePrefix).mdf')
Where are my file settings? How is SSDT ignoring my commands and why would it be doing this? As it turns out this is expected behavior and it is controlled by a couple of settings.
Open the publish file within SSDT and click on the Advanced settings button. Scroll down to find “Script File Size” option which is OFF by default. This setting controls what SSDT does with the 3 properties exposed when creating a data or log file; Size, Maxsize, and Filegrowth. Checking this value will cause SSDT to script those properties on publish.
Now there are a few reasons why this is set to be off by default and you can see the explanation here.
The main reason is that by default you would not normally want a production sized database on your Sandbox/ local development machine, makes perfect sense and this is a great option.
Of course during an automated build process you should pass in this Script File size property set to TRUE so SSDT and your build process will honor your file size settings – better still make the sizes properties so you dynamically set them for each build and target!
