Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to set initial database file size and filegrowth in a database project?

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?

like image 556
BartoszKP Avatar asked Oct 31 '22 23:10

BartoszKP


1 Answers

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%
    )
like image 157
BartoszKP Avatar answered Nov 14 '22 15:11

BartoszKP