CODEWITHSUNDEEP
sql-server
cnom
cnom

Reputation: 3241

SQL server set AUTOGROW_ALL_FILES fails

I try to execute this:

USE [MyDB]
GO
declare @autogrow bit
SELECT @autogrow=convert(bit, is_autogrow_all_files) FROM sys.filegroups WHERE name=N'PRIMARY'
if(@autogrow=0)
    ALTER DATABASE [MyDB] MODIFY FILEGROUP [PRIMARY] AUTOGROW_ALL_FILES
GO

And it fails with:

Database state cannot be changed while other users are using the database 'HistoryDBTest'

How can I go around it?

Upvotes: 1

Views: 2505

Answers (1)

Thom A
Thom A

Reputation: 95554

You'll need to change the database to single user mode. Use this with care; considering the error is "other users are using the database" this means that those users will have their connections to the database cut off, and their transactions rolled back.

USE master;
GO
ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
USE [MyDB];
GO
DECLARE @autogrow bit;
SELECT @autogrow = CONVERT(bit, is_autogrow_all_files)
FROM sys.filegroups
WHERE name = N'PRIMARY';
IF (@autogrow = 0) ALTER DATABASE [MyDB] MODIFY FILEGROUP [PRIMARY] AUTOGROW_ALL_FILES;
GO
USE master;
GO
ALTER DATABASE MyDB SET MULTI_USER;

Upvotes: 10

Related Questions