December 7, 2018 at 8:27 am
Hi,
I am tesing a Visual Studio (2015) SQL Server project, with a Post Deployment stored proc that will modify filesize and filegrowth values for a set of files belonging to an existing target database. When the change is applied during the Publish process this message is generated ->
The file "ref_01" has been modified in the system catalog. The new path will be used the next time the database is started.
This the statement executedALTER DATABASE mydatabase MODIFY FILE (NAME = 'ref_01', FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SS2014DEV\MSSQL\DATA\mydatabase_ref_01.ndf', SIZE = 90MB, MAXSIZE = UNLIMITED, FILEGROWTH = 9MB)
Search results on the last sentence of the warning message focus on people moving database files whilst the database is online. Fair enough outcome.
Is this message valid when applying changes to the file, such as sizing like I am? What is the minimum action required to have the new sizing take effect? If I set the database offline before attempting to apply the changes, naturally the Publish process is unable to open the database to do 'its business'.
Many of these databases are periodically truncated, providing good opportunities to apply these sorts of physical changes without incurring unacceptable I/O. Those that are large will get special treatment. However, many are small enough in data volumes to permit Publish to be used. Because everything is currently created in the Primary MDF we can create the new filegroups and strategically move objects from the primary, as processing windows and change opportunities permit.
Obviously I/O must be considered when moving populated tables between filegroups / files. However, I had expected that file attributes to do with sizing would not cause any major concerns. We do have plenty of time on weekends to apply changes.
I know many hardcore DBAs would prefer to do file sizing independent of the VSPro DB projects. However, we have a large number of SQL Servers with nearly all databases using the default single primary MDF. As part of this exercise, I am also setting up filegroups & files in addition to sizing as opposed to default incremental growth and we have SDLC environments that will be dealt with first.
Thanks in advance for any suggestions.
December 12, 2018 at 11:55 pm
When you run ALTER DATABASE MODIFY FILE any changes to sizing should be applied immediately. Filepath\Filename changes require the database to go offline and back online to effect the change, but resizing does not.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply