Archive for the ‘Files’ 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

Adding extra files to Tempdb

Posted: November 3, 2015 in DBA, Design, Files, Tempdb

This isn’t actually a problem. When I build a new instance I like a tempdb file for each cpu. This script will create those files in the directory where the current tempdb data file is. Please note that it sizes tempdb data and log files too, you may want to change those values

/* Script Starts*/
DECLARE @cpuCount as int;
DECLARE @Files as int;

SELECT @Files = COUNT(*)
FROM tempdb.sys.database_files
WHERE type_desc = ‘ROWS’;

SELECT @cpuCount = cpu_count /hyperthread_ratio
FROM sys.dm_os_sys_info
–Print @cpuCount
–Print @Files

Alter Database Tempdb modify file(Name=tempdev, Size=300, filegrowth = 150MB);
Alter Database Tempdb modify file(Name=templog, Size=50, filegrowth = 50MB);

DECLARE @FileLocation as Varchar(750);

–You may need to check that the name of the tempdb data file is tempdb.mdf (select * from tempdb.sys.master_files)
Set @FileLocation = (SELECT SUBSTRING(physical_name, 1,
CHARINDEX(N’tempdb.mdf’,
LOWER(physical_name)) – 1) DataFileLocation
FROM master.sys.master_files
WHERE database_id = 2 AND FILE_ID = 1)
–Print @FileLocation

DECLARE @diff as int;
SET @diff = @cpuCount – @Files
If @diff > 7
set @diff = 8 – @Files

DECLARE @x as TinyInt;
SET @x = @Files;
DECLARE @file as Varchar(10);
DECLARE @fileName as Varchar(250);
While @diff > 0
BEGIN
SET @file = ‘tempdev’ + Cast(@x as varchar(2))
SET @fileName = @FileLocation + ‘\’ + @file + ‘.ndf’;
DECLARE @sql as Varchar (8000);
SET @sql = ‘ALTER DATABASE TempDb ‘;
SET @sql = @sql + ‘ADD FILE ‘;
SET @sql = @sql + ‘( ‘;
SET @sql = @sql + ‘NAME = ‘ + @file + ‘, ‘;
SET @sql = @sql + ‘FILENAME = ”’ + @fileName + ”’, ‘;
SET @sql = @sql + ‘SIZE = 300MB, ‘;
SET @sql = @sql + ‘FILEGROWTH = 150MB’;
SET @sql = @sql + ‘);’;
Exec (@sql);
SET @diff = @diff -1;
SET @x = @x + 1;
END;

This happened on a SQL 2000 system. I wanted to change the autogrowth on a file from 10% to a MB setting. I did this through the GUI and got an error saying the system couldn’t find the file I was trying to change.

I then tried to change it using ALTER Database. It still said there was no such file.

I right-clicked on the DB and checked the files again. Then I had a look in dbo.sys.files and could see it. I went through windows Explorer and took a look at the file. It was there and in the place where SQL was telling me it was – yet it still insisted it wasn’t there.

Solution

I ran this script:

SELECT *
FROM master..sysaltfiles
WHERE dbid= DB_ID();

This time the file had a different logical name. It was actually a better name than the one sysfiles was claiming. So I ran a script to change th efile’s name to the one sysAltFiles had:

ALTER Database DBName
MODIFY FILE (NAME = ‘DBName_data’, NEWNAME = ‘ DBName _Data’);

Now I was able to get in and change the autogrow setting.