I'm trying to set the initial database file size and its auto growth parameter in a Database Project in Visual Studio to be used with the Publish option. I've added a Filegroup file (SqlFile1.sql) to the project with the following contents:
ALTER DATABASE [$(DatabaseName)]
ADD FILE
(
NAME = [SqlFile1],
FILENAME = '$(DefaultDataPath)$(DefaultFilePrefix)_SqlFile1.ndf',
SIZE = 5000MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 50%
) TO FILEGROUP [PRIMARY]
(documentation).
When I click Publish with the option Always re-create database checked, the file SqlFile1
is created within the PRIMARY filegroup, but has the Initial size set to 3 MB and Autogrowth is By 1 MB, unrestricted growth. There are no error or warning messages.
What's interesting, the following SQL script:
ALTER DATABASE DatabaseTest
MODIFY FILE (NAME = [SqlFile1], SIZE = 5000MB, FILEGROWTH = 50%);
GO
ran from SQL Management Studio correctly modifies file's properties.
Why are my settings ignored and how to make them work?
The scripts created in the bin directory suggest that properties other than logical file name, physical file name and target file group are indeed ignored. Eventually I solved this issue by adding a post-deployment script. To do this right click the database project and choose Add -> New item -> User scripts -> Post-Deployment Script. In this script you can modify the database anyway you want, in this case alter file's properties with:
ALTER DATABASE [$(DatabaseName)]
MODIFY FILE
(
NAME = [SqlFile1],
SIZE = 5000MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 50%
)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With