4

Does anyone know of a way to accomplish setting a filegroup to AUTOGROW_ALL_FILES without setting the database to single user mode? I frequently add new filegroups/files to a 24/7 production database where killing active sessions and rolling back transactions (via SET SINGLE_USER WITH ROLLBACK IMMEDIATE) is definitely not preferred. IMO this operation should be able to be accomplished with other active sessions on the database.

  • What exact command you are running, can you share? AutoGrow means you are modifying file size? Or you are adding new files to existing filegroup? – SqlWorldWide Apr 20 '17 at 23:09
  • e.g. - ALTER DATABASE [dbName] MODIFY FILEGROUP [fgName] AUTOGROW_ALL_FILES - this is a replacement for TF1117 new to SS2016 and is used to grow all files in a filegroup at the same time, once the grow threshold has been met. – Richard Herling Apr 26 '17 at 20:16
  • I see you found your answer. Thanks for letting me know. – SqlWorldWide Apr 27 '17 at 1:41
2

It seems there is no way around it. This is per an offline conversation with Paul Randal from SQL Skills. So that would mean I would need to either have no other open sessions on the db, or set it to single user mode prior to running the set statement. Thanks for the assistance Paul.

1

Per Microsoft's documentation, there is no requirment to set a database to single_user mode when running ALTER DATABASE MODIFY FILEGROUP <FILEGROUP> AUTOGROW_ALL_FILES.

If you are finding yourself setting single user mode I would recommend taking a look at the locks that are blocking that change for you.

An alternative would be to grow the data files themselves rather than the filegroup and considering usage of trace flag 1117.

  • 1
    I apologize, I should have noted that I was referring to SQL Server 2016. In 2016 trace flag 1117 is no longer functional and has been replaced with AUTOGROW_ALL_FILES. Additionally, it seems that it is blocked by any session with a Shared lock on the database. Throwing the following error: Msg 5070, Level 16, State 2, Line 3 Database state cannot be changed while other users are using the database 'dbName'. I am wondering if there is no documentation anywhere on this because most users would be setting this for FGs at the time of database creation. Still no known workaround. – Richard Herling Apr 26 '17 at 20:05

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.