Hi,
i have a client that is trying to enforce filegroups when new databases are created. AS the admin doesnt create the database and is done by a 3rd Party Software via the 'Create Database' command, we do not have the ability to enforce it.
We tried a server tigger monitoring for the create database command but then found you cannot alter a database due to it being in a trigger or in a multi statement transaction.
I did toy with the idea of programmitcally adding a step to a job, from the server trigger that would run after a few seconds but not sure if this would work,
given that SQL server uses filegroups heavily im surprisedthere isnt an easier way to configure this, at least not that i can see.
So essentially when a 'CREATE DATABASE' command is run, we would want to add a filegroup with multiple files over a different disks.
I have built the below which works when i run it after the database is created however this requires constant admin int database is created ?y way to do this when the database is created ?
Appreciate any help you can give.
What I have tried:
Here is the code for creating the filegroup (note this isnt the final product its just a test one we drew up to tests the disks etc.
SET @SQL =
'
USE master
ALTER DATABASE ' + @DatabaseName + '
ADD FILEGROUP FG_' + @DatabaseName + ';
BEGIN TRANSACTION
ALTER DATABASE ' + @DatabaseName + '
ADD FILE
(
NAME = FGF_' + @DatabaseName + '_1,
FILENAME = ''F:\SQLDATA\FGF_' + @DatabaseName + '_1.ndf'',
SIZE = 50MB,
FILEGROWTH = 50MB
),
(
NAME = FGF_' + @DatabaseName + '_2,
FILENAME = ''G:\SQLDATA\FGF_' + @DatabaseName + '_2.ndf'',
SIZE = 50MB,
FILEGROWTH = 50MB
),
(
NAME = FGF_' + @DatabaseName + '_3,
FILENAME = ''H:\SQLDATA\FGF_' + @DatabaseName + '_3.ndf'',
SIZE = 50MB,
FILEGROWTH = 50MB
),
(
NAME = FGF_' + @DatabaseName + '_4,
FILENAME = ''I:\SQLDATA\FGF_' + @DatabaseName + '_4.ndf'',
SIZE = 50MB,
FILEGROWTH = 50MB
),
(
NAME = FGF_' + @DatabaseName + '_5,
FILENAME = ''J:\SQLDATA\FGF_' + @DatabaseName + '_5.ndf'',
SIZE = 50MB,
FILEGROWTH = 50MB
),
(
NAME = FGF_' + @DatabaseName + '_6,
FILENAME = ''K:\SQLDATA\FGF_' + @DatabaseName + '_6.ndf'',
SIZE = 50MB,
FILEGROWTH = 50MB
),
(
NAME = FGF_' + @DatabaseName + '_7,
FILENAME = ''L:\SQLDATA\FGF_' + @DatabaseName + '_7.ndf'',
SIZE = 50MB,
FILEGROWTH = 50MB
)
TO FILEGROUP FG_' + @DatabaseName + ';
';
EXEC (@SQL);