For more information about Filegroup, please read the article “How to Create a Database on SQL Server“. Especially in large and partitioned databases, we need to set the filegroup as read only. In the article “How To Create Partition On SQL Server” you can see how to create a partition.
By right-clicking on the database, we click on properties, and we’ll get to the filegroups tab and set the filegroup that we want as readonly.
If you have an active session on the database you will get the error as follows.
In order to avoid this error, we get the script of the change we made by clicking on the script in the screenshot above.
We then run the script again by editing it as follows.(At First Alter Database To Single_User Mode, Finaly Alter Database To Multi_User Mode)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
USE [Test] GO ALTER DATABASE Test SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO declare @readonly bit SELECT @readonly=convert(bit, (status & 0x08)) FROM sysfilegroups WHERE groupname=N'READONLYFILEGROUP' if(@readonly=0) ALTER DATABASE [Test] MODIFY FILEGROUP [READONLYFILEGROUP] READONLY GO USE [master] GO declare @readonly bit SELECT @readonly=convert(bit, (status & 0x08)) FROM sysfilegroups WHERE groupname=N'READONLYFILEGROUP' if(@readonly=0) ALTER DATABASE [Test] MODIFY FILEGROUP [READONLYFILEGROUP] READONLY GO ALTER DATABASE Test SET MULTI_USER WITH ROLLBACK IMMEDIATE GO |
