Archive for the ‘Filegroups’ Category

The Problem

When checking the error logs on a SQL 2016 box I noticed this error message: Trace flag 1117 is discontinued. Use the options provided with ALTER DATABASE.
Trace flags T1117 and T1118 have been discontinued as of SQL Server 20016.

T1118 tells SQL to avoid mixed extents and use full extents.
T1117 makes sure all files in a filegroup grow at an even rate.

I now need to replicate this behavior on the SQL 2016 box

The Solution

T1118 doesn’t matter so much to me. The database where it has the most effect is tempdb and this now uses full extents by default.

T1117 has been replaced with an Alter Database Command:
ALTER DATABASE Adventureworks2016 MODIFY FILEGROUP [PRIMARY] AUTOGROW_ALL_FILES
GO
ALTER DATABASE Adventureworks2016 MODIFY FILEGROUP [PRIMARY] AUTOGROW_SINGLE_FILE
GO

Unfortunately you can’t apply this to the model db. So, to apply this to all current databases I wrote a script to loop through and check the status of the filegroup and change it if needed.

Please test this script before you run it. You might want to change the two Exec command with Print and so check the output.

Note: You can check the status of a database by running this script :
SELECT * from sys.filegroups
You will see there is a column is_autogrow_all_files. This should be set to 1

This script will not work if the database is in use

The Script

SELECT [name] as DBName, cast(0 as bit) as Checked
into #DBTest
FROM sys.Databases as db
where database_id > 4
and is_read_only = 0;

DECLARE @DBName as Varchar(250);
DECLARE @sql as Varchar(max);

While Exists (select 1 FROM #DBTest where Checked = 0)
BEGIN
SELECT top 1 @DBName = DBName
FROM #DBTest
where Checked = 0;

SET @sql = ‘USE ‘ + @DBName + ‘
DECLARE @sqlinner as Varchar(max);
DECLARE @fg as Varchar(250);
SELECT ”ALTER DATABASE ‘ + @DBName + ‘ MODIFY FILEGROUP [” + name + ”] AUTOGROW_ALL_FILES ” as sl , cast(0 as bit) as Checked, name as FG
INTO #FileGroups
from sys.filegroups
WHERE is_autogrow_all_files = 0;
While Exists (select 1 FROM #FileGroups where Checked = 0)
BEGIN
SELECT top 1 @sqlinner = sl, @fg = FG
FROM #FileGroups where Checked = 0;

EXEC(@sqlinner);

UPDATE #FileGroups
SET Checked = 1
WHERE FG = @fg;

END
DROP TABLE #FileGroups

exec(@sql);

UPDATE #DBTest
SET Checked = 1
WHERE DBName = @DBName;
END

DROP TABLE #DBTest

Advertisements

Before a filegroup can be removed all files associated with it need to be deleted.

Before a file can be deleted all objects associated with it need to be removed.

Just removing a file not a filegroup

If you have multiple files on a filegroup and you just want to get rid of one you can run this command.

DBCC ShrinkFile(logical_file_name,EmptyFile);

This will move any objects in the file onto the other files in the filegroup. You can then delete the file.

ALTER DATABASE DatabaseName REMOVE FILE logical_file_name;

Removing a file and a filegroup

To do this you will have to make sure that here are no objects associated with the file group. This could be Tables etc, Indexes, or even Blob objects.

This bit of code will show you the objects on a file group.

SELECT o.[name], o.[type], i.[name], i.[index_id], f.[name]

FROM sys.indexes i

INNER JOIN sys.filegroups f

ON i.data_space_id = f.data_space_id

INNER JOIN sys.all_objects o

ON i.[object_id] = o.[object_id]

WHERE i.data_space_id = f.data_space_id

AND o.type = ‘U’ — User Created Tables

I got this from:

http://blog.sqlauthority.com/2009/06/01/sql-server-list-all-objects-created-on-all-filegroups-in-database/

If there are objects you want to keep you will need to move them to another filegroup. To move a non-clustered index you will need to drop and recreate it on the new file group. To move a table you need to drop and recreate the clustered index.

Once the filegroup has no user objects empty you can run the same commands for removing a file as – shrinkfile and remove file.

Then you will need to run the code below to drop the filegroup:

DATABASE [databaseName] REMOVE FILEGROUP [Filegroupname];

Remember: you can not drop the Primary filegroup.