5

I would like to change all file groups in my database to AutoGrow_all_Files, but only if it is currently AutoGrow_Single_File, like this:

ALTER DATABASE mydatabase MODIFY FILEGROUP [myfilegroup] AUTOGROW_ALL_FILES

How can I check this database property?

2

sys.filegroups contains a column is_autogrow_all_files that indicates if the setting is already turned on for the filegroup in question.

This should show a list of alter database statements you can run:

DECLARE @cmd NVARCHAR(2000);
DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC 
FOR
SELECT '
select ''ALTER DATABASE ' + QUOTENAME(d.name) + ' MODIFY FILEGROUP '' + QUOTENAME(ds.name) + '' AUTOGROW_ALL_FILES;''
from ' + QUOTENAME(d.name) + '.sys.data_spaces ds
    INNER JOIN ' + QUOTENAME(d.name) + '.sys.filegroups fg ON ds.data_space_id = fg.data_space_id
WHERE fg.is_autogrow_all_files = 0;
'
FROM sys.databases d;
OPEN cur;
FETCH NEXT FROM cur INTO @cmd;
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT (@cmd);
    FETCH NEXT FROM cur INTO @cmd;
END
CLOSE cur;
DEALLOCATE cur;

The code will display a set of statements you can run for each database on the instance, to modify each file group.

And example of the statement generated is:

select 'ALTER DATABASE [msdb] MODIFY FILEGROUP ' + QUOTENAME(ds.name) + ' AUTOGROW_ALL_FILES;'
from [msdb].sys.data_spaces ds
    INNER JOIN [msdb].sys.filegroups fg ON ds.data_space_id = fg.data_space_id
WHERE fg.is_autogrow_all_files = 0;
  • 1
    thank you for giving me just what I needed. I have had do change a few things; your code works on master, model, and in-memory filegroups. I've posted my edition, in case you want to see how I did. – Henrik Staun Poulsen Aug 16 '16 at 6:20
  • I'm glad to help, @Henrik. – Max Vernon Aug 17 '16 at 14:46
4

Complementing to both Max and Kenneth's answers ..

I would suggest you to apply SQL Server 2016 CU1 as there is a known bug with AUTOGROW_ALL_FILES property -

The property isn't preserved after the database or the server restarts. The sys.filegroups viewstill shows that the corresponding column for the property (is_autogrow_all_files and is_read_only) is set to 1. However, it's not actually effective.

Also, its important to note that in SQL Server 2016 you dont need TF 1117 since is_autogrow_all_files is equivalent of TF1117

  • thank you for pointing this out, but yes, we are running CU1. – Henrik Staun Poulsen Aug 16 '16 at 6:18
2

The setting is per filegroup. You can look in sys.filegroups.

SELECT is_autogrow_all_files, * FROM sys.filegroups

sys.filegroups bol: https://msdn.microsoft.com/en-us/library/ms187782.aspx

2

SSMS 17.8 introduced UI support for this feature. From SSMS 17.8 is now available:

In this release of SQL Server Management Studio, we have introduced UI and scripting support for the AUTOGROW_ALL_FILES database filegroup property.

This property was introduced in SQL Server 2016 to replace trace flag 1117, but it was only settable via T-SQL script. Now you can set the property via a checkbox in the Database Properties -> Filegroups page

enter image description here Image source: https://cloudblogs.microsoft.com/uploads/prod/2018/06/image-1-sql-server.png

You can also use the “Script” button to script out the change:

USE [AdventureWorks2016_EXT] 

GO

declare @autogrow bit 

SELECT @autogrow=convert(bit, is_autogrow_all_files) 
  FROM sys.filegroups WHERE name=N'PRIMARY' 

if(@autogrow=0) 

ALTER DATABASE [AdventureWorks2016_EXT] 
  MODIFY FILEGROUP [PRIMARY] AUTOGROW_ALL_FILES 

GO
1

I hope this can help. Sometimes the execution will not be successful when in use by others. You may either run it again or add a script to set the DB to single mode.

-- Run the results of this query, It will create queries for all databases in the server.

DECLARE @command varchar(1000) 
SELECT @command = 'use [?]; SELECT ''ALTER DATABASE ? MODIFY FILEGROUP ['' + b.groupname + ''] AUTOGROW_ALL_FILES;'' 
FROM dbo.sysfiles a (NOLOCK)
JOIN dbo.sysfilegroups b (NOLOCK) ON a.groupid = b.groupid
GROUP BY b.groupname'

EXEC sp_MSforeachdb @command
1

Here is my solution to the problem of getting even file growth on all files in the database:

SET NOCOUNT ON 
DECLARE @db_name sysname, @sql NVARCHAR(max)='', @Mainsql NVARCHAR(max)='', @rowCount int

SELECT @Mainsql = @@VERSION
--SELECT @Mainsql 
--Microsoft SQL Server 2008 R2 (SP3) - 10.50.6529.0 (X64)   Mar 19 2015 13:19:13   Copyright (c) Microsoft Corporation  Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor) 
--Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64)   Oct 28 2016 18:17:30   Copyright (c) Microsoft Corporation  Developer Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) 

IF @Mainsql NOT LIKE '%SQL Server 2016%' BEGIN
    SELECT 'This script does not work on servers prior to SQL Server 2016. Please check before continuing'
    SELECT @Mainsql
END

Begin TRY
    DROP TABLE #h
END TRY
BEGIN CATCH
END CATCH
CREATE TABLE #h (i INT IDENTITY(1,1), sqltxt NVARCHAR(max))
DELETE FROM #h

DECLARE dbcursor CURSOR FAST_FORWARD READ_ONLY FOR 
    SELECT name FROM sys.databases 
    WHERE is_read_only=0 AND state_desc='ONLINE' 
    AND source_database_id is NULL AND name NOT IN ('master', 'model', 'msdb')
    ORDER BY name

OPEN dbcursor

FETCH NEXT FROM dbcursor INTO @db_name

WHILE @@FETCH_STATUS = 0
BEGIN
    RAISERROR (' %s' , 10, 1, @db_name) WITH NOWAIT
    SET @rowCount=0
    SELECT @Mainsql =''
    SELECT @Mainsql += '
    SELECT @rowCount=count(*)
    FROM [' + @db_name + '].sys.filegroups F 
    WHERE type <> ''FD''  /* no FILESTREAM_DATA_FILEGROUP */
    AND type <> ''FX'' /* no MEMORY_OPTIMIZED_DATA_FILEGROUP */
    AND is_autogrow_all_files=0
    '
    BEGIN TRY
        EXEC sys.sp_executesql @Mainsql, N'@rowCount int output', @rowCount OUTPUT
    END TRY
    BEGIN CATCH
        RAISERROR ('Unable to access %s' , 10, 1, @db_name) WITH NOWAIT
        RAISERROR ('SQL: %s' , 10, 1, @Mainsql) WITH NOWAIT
    END CATCH

    --SELECT @Mainsql AS max, @rowCount rowcnt

    IF @rowCount <> 0 BEGIN
        SELECT @sql += 'INSERT INTO #h ( sqltxt ) SELECT ''PRINT'''''' + ''[' + @db_name + ']''''''
        ' 
        SELECT @sql += 'INSERT INTO #h ( sqltxt ) SELECT ''USE '' + ''[' + @db_name + ']''
        ' 
        IF @db_name <>'tempdb' BEGIN 
            SELECT @sql += 'INSERT INTO #h ( sqltxt )
            SELECT ''ALTER DATABASE [' + @db_name + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;''
            '
        END 
        SELECT @sql += 'INSERT INTO #h ( sqltxt )
        SELECT ''ALTER DATABASE [' + @db_name + '] MODIFY FILEGROUP ['' + fg.name + ''] AUTOGROW_ALL_FILES''
        FROM [' + @db_name + '].sys.filegroups FG 
        WHERE FG.type <> ''FD''  /* no FILESTREAM_DATA_FILEGROUP */
        AND FG.type <> ''FX'' /* no MEMORY_OPTIMIZED_DATA_FILEGROUP */
        AND FG.is_autogrow_all_files=0
        '

        IF @db_name <>'tempdb' BEGIN 
            SELECT @sql += 'INSERT INTO #h ( sqltxt )
            SELECT ''ALTER DATABASE [' + @db_name + '] SET MULTI_USER;''
            '
        END 
    END 
    --SELECT @sql 
    FETCH NEXT FROM dbcursor INTO @db_name
END

SELECT @sql
EXEC sp_executesql @sql

CLOSE dbcursor
DEALLOCATE dbcursor

SELECT @sql=''
SELECT @sql+=sqltxt + '
' FROM #h H 
ORDER BY i
SELECT @sql
--EXEC sp_executesql @sql

It still has problems; if there are no files in a file group, I get an error message.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.