SQL Server has a setting which will grow all database files in a filegroup any time that one file grows. This setting is great for databases that are new, or otherwise can have the setting enabled.
However exclusive access is required, therefore it's not practical to enable this setting on some databases. You could entertain using single user mode or connecting via the DAC, but the downtime might not be worth it. Remember, resizing files manually does pause I/O, but it's less intrusive than no connections at all.
If downtime is a concern, consider enabling the Instant File Initialization setting for your SQL Service before running this. However, if you don't want to enable that, running this more frequently could result in shorter, less noticed pauses in I/O.
This script was built to be run as a SQL Agent job.
Specify 2 things for this script to work:- Database Name where it says 'EnterDatabaseNameHere'.
- Filegroup Name, where it says 'PRIMARY'.
The script will:
1. Find the largest data file in the file group
2. Generate a script and if enabled, run the script to resize all the other data files.
*/
DECLARE
@dbname NVARCHAR(128),
@dbid INT,
@filegroup NVARCHAR(128),
@filegroupid SMALLINT,
@filesize BIGINT,
@filesizemb NVARCHAR(128),
@script NVARCHAR(MAX);
SET @dbname = 'EnterDatabaseNameHere';
SET @filegroup = 'PRIMARY';
SET @dbid = DB_ID(@dbname);
SET @filegroupid = FILEGROUP_ID(@filegroup)
SET @filesize = (SELECT max(size)
FROM sys.master_files
WHERE database_id = @dbid
AND data_space_id = @filegroupid
AND type = 0);
SET @filesizemb = ROUND(@filesize * 8 / 1024, 0)
SET @script = ( SELECT 'ALTER DATABASE ['+ DB_NAME(database_id) + '] MODIFY FILE ( NAME = N''' + [name] + ''', SIZE = ' + @filesizemb + ' MB);' + CHAR(10)
FROM sys.master_files
WHERE database_id = @dbid
AND data_space_id = @filegroupid
AND size < @filesize
AND type = 0
FOR XML PATH(''));
SELECT @script
--EXEC sp_executesql @script
