DaveWentzel.com            All Things Data

Adding New DataFiles vs Autogrowth

 

--select @@version

--setup

IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID('DBA_DATA_FILE_GROWTH'))

DROP PROC DBA_DATA_FILE_GROWTH

GO

CREATE PROC DBA_DATA_FILE_GROWTH

( @newdriveforfilegroup char(1) = NULL --single letter drive (no mountpoints), if you want to assign a drive to a filegroup, use with...

, @filegroup varchar(255) = NULL --the fg you want to expand on the newdrive.

, @use_entire_drive bit = 0 --use the entire drive (really, totalfree - 500MB) for the given fg, otherwise, use growth param on first file in fg

, @pidbgrowthvalue bigint = NULL --override file growth setting on first file in given fg

, @pvdbgrowthtype varchar(200) = NULL --KB or PERCENT

, @debug_mode varchar(3) = 'NO' --do no file manipulations, just test the logic and string building

, @use_debug_stats_for_date datetime = null --use test case statistics

, @file_size_governor_GB int = 300 --a file size cannot be created > this value

, @override_max_num_files_governor bit = 0 --override the algorithm that says #files/filegroup <= #processors

)

AS

BEGIN --proc

/*

runs via the maintenance scheduler in the context of the given database.

works with 2000/2005

Will not work with SAN "mount points" since xp_fixeddrives doesnt work with them

will create a new data file for the given filegroup on the fg drive if the fg has less than x %/KB free space and log to eventvwr and HTRACE

if there is less than x % free space or y MB free space on fg drive then log to eventvwr

can pass in parameters to handle new drives being added for the filegroup

logs metrics to table DBA_space_statistics/DBA_DRIVE_STATISTICS

@max_num_files_governor ensures that num_files_per_filegroup <= #processors on box

So,why not just allow autogrowth?:

--during autogrowth events transactions are serialized causing perf problems

--constant autogrowth causes fragmentation which can be severe

--a growing log or db file can consume all drive space, this will alert us

Tables:

DBA_DRIVE_STATISTICS: whenever procedure is run rows are added to the table to list freespace on available fixed drives. Maintains history.

DBA_SPACE_STATISTICS: whenever procedure is run rows are added to the table to list the database space statistics. Maintains history.

Common Usages:

should be added to the job maintenance scheduler.

EXEC DBA_DATA_FILE_GROWTH; --runs with the most common options

--want to devote a new SAN volume M for data on filegroup PRIMARY

EXEC DBA_DATA_FILE_GROWTH @newdriveforfilegroup = 'M',@FILEGROUP='PRIMARY', @use_entire_drive = 1;

 

*/

SET NOCOUNT ON

IF (@debug_mode = 'YES')

BEGIN

SELECT 'RUNNING IN DEBUG MODE, NO DB CHANGES WILL OCCUR!'

END

DECLARE @runtime datetime, @rows int, @error int, @exec_string varchar(8000), @proc_count int, @message varchar(8000)

DECLARE @dbgrowthvalue bigint, @dbgrowthtype varchar(200)

SELECT @runtime = getdate()

--get processor info

CREATE TABLE #proc ([Index] int, [Name] varchar(200), Internal_value bigint , Character_value varchar(256));

INSERT INTO #proc EXEC master..xp_msver;

SELECT @proc_count = Internal_Value

FROM #proc

WHERE Name = 'ProcessorCount';

if not exists (select * from sysobjects where id = object_id('DBA_DRIVE_STATISTICS') and type = 'U')

BEGIN

CREATE TABLE DBA_DRIVE_STATISTICS (ObjectID bigint IDENTITY(1,1), Drive varchar(5),FreeMB bigint,RunDtime datetime)

END

if not exists (select * from sysobjects where id = object_id('DBA_SPACE_STATISTICS') and type = 'U')

BEGIN

CREATE TABLE DBA_SPACE_STATISTICS (ObjectID bigint IDENTITY(1,1), DBName varchar(200),LogicalFileName varchar(200),FileID int, Drive varchar(20),DBFileName varchar(2000),DBFileGroup varchar(200),DBSizeKB bigint,dbmaxsize varchar(200),DBGrowthValue int, DBGrowthType varchar(20),RunDtime datetime ,TotalSizeMB bigint, UsedSizeMB bigint, ReservedSizeMB bigint, PercentFreeSpace numeric(4,2))

END

 

INSERT INTO DBA_DRIVE_STATISTICS (Drive,FreeMB) exec master..xp_fixeddrives

UPDATE DBA_DRIVE_STATISTICS SET RunDtime = @runtime WHERE RunDtime is null;

INSERT INTO DBA_SPACE_STATISTICS (DBName,LogicalFileName,FileID,Drive,DBFileName,DBFileGroup,DBSizeKB,DBMaxSize,DBGrowthValue,DBGrowthType, TotalSizeMB, UsedSizeMB, ReservedSizeMB,RunDTime)

select

db_name(dbid) as dbname

,name

,fileid

,left(filename,1) as drive

,filename

,filegroup_name(groupid) as filegroup

,convert(nvarchar(15),convert(bigint,size)*8) as dbsizeKB

,(case maxsize when -1 then N'Unlimited'

else convert(nvarchar(15),convert(bigint,maxsize)*8) end) as dbmaxsize

,dbgrowthvalue=(case status & 0x100000 when 0x100000 then convert(nvarchar(15),growth)

else convert(nvarchar(15),convert(bigint,growth)*8) end)

,dbgrowthtype=(case status & 0x100000 when 0x100000 then 'PERCENT'

else 'KB' end)

,null as TotalSizeMB

,null as UsedSizeMB

,null as ReservedSizeMB

,@runtime as RunDTime

from master..sysaltfiles

where dbid = db_id()

and status & 0x40 = 0 --data files only

--temp holding for showfilestats

if (select object_id('tempdb..#DBA_temp')) is not null

BEGIN

drop table #DBA_temp

END

CREATE TABLE #DBA_temp (Fileid int, FileGroup int, TotalExtents bigint, UsedExtents bigint, [Name] varchar(255),FileName varchar(255));

insert into #DBA_temp EXEC ('DBCC SHOWFILESTATS WITH NO_INFOMSGS')

--space calculations

--each extent = 64K...hence, extent*64 = size in KB

UPDATE DBA_SPACE_STATISTICS

SET TotalSizeMB = TotalExtents * 64.0/1024

, UsedSizeMB = UsedExtents * 64.0/1024

, ReservedSizeMB = TotalExtents * 64.0/1024 - UsedExtents * 64.0/1024

, PercentFreeSpace = convert(numeric(4,2),(1-(UsedExtents * 64.0/1024)/(TotalExtents * 64.0/1024))*100)

FROM #DBA_temp t

JOIN DBA_SPACE_STATISTICS stats

ON stats.DBFileName = t.FileName

AND stats.FileID = t.Fileid

AND stats.RunDtime = @runtime;

IF (@debug_mode = 'YES' AND @use_debug_stats_for_date IS NOT NULL)

BEGIN

--we assume we are debugging and passed in the table values for the two STATS tables...use those

SELECT @runtime = @use_debug_stats_for_date

END

select 'Drive FreeSpace Stats for this run:';

select * from DBA_DRIVE_STATISTICS where RunDtime = @runtime;

select 'Database Space Stats for this run: ';

select * from DBA_SPACE_STATISTICS where RunDtime = @runtime;

drop table #DBA_temp

---analysis for each filegroup

DECLARE @fg varchar(256), @TotalSizeMB bigint, @UsedSizeMB bigint, @ReservedSizeMB bigint, @FreeSpacePercent numeric(4,2), @FreeMB bigint, @total_files_in_fg int, @new_sizeMB bigint, @autogrowth_soon bit

DECLARE filegps CURSOR FOR

select distinct DBFileGroup

FROM DBA_SPACE_STATISTICS

WHERE RunDtime = @runtime;

OPEN filegps

FETCH NEXT FROM filegps INTO @fg

WHILE (@@FETCH_STATUS = 0)

BEGIN --filegroup loop

--reset loop stuff

SELECT @autogrowth_soon = 0

SELECT @exec_string = 'ALTER DATABASE ' + quotename(db_name()) + ' '

--sum space statistics by filegroup

SELECT @TotalSizeMB = SUM(TotalSizeMB), @UsedSizeMB=SUM(UsedSizeMB), @ReservedSizeMB=SUM(ReservedSizeMB), @total_files_in_fg = count(*)

, @FreeSpacePercent = ((1. - (sum(convert(numeric,UsedSizeMB))/sum(convert(numeric,(TotalSizeMB))))) *100)

FROM DBA_SPACE_STATISTICS

WHERE RunDtime = @runtime

GROUP BY DBFileGroup

HAVING DBFileGroup = @fg;

SELECT '%FreeSpace for filegroup ' + @fg + ' is ' + convert(varchar(20),@FreeSpacePercent)

--if growth values not supplied, query and use first file in fg parameters

IF (@pidbgrowthvalue is null or @pvdbgrowthtype is null)

BEGIN

SELECT TOP 1 @dbgrowthvalue=dbgrowthvalue, @dbgrowthtype=dbgrowthtype

FROM DBA_SPACE_STATISTICS

WHERE RunDtime = @runtime

AND DBFileGroup = @fg;

END

ELSE

BEGIN

SELECT 'Growth parameters passed in, using those to determine new file requirements'

SELECT @dbgrowthvalue = @pidbgrowthvalue, @dbgrowthtype = @pvdbgrowthtype

END

 

--do we want to grow by % or by KB

IF @dbgrowthtype = 'PERCENT'

BEGIN --%

IF (@FreeSpacePercent > @dbgrowthvalue)

BEGIN

SELECT 'Adequate FreeSpace % exists in fg ' + @fg + '. No work to do for this fg'

--FETCH NEXT FROM filegps INTO @fg

--CONTINUE

END

ELSE

BEGIN

--need to run the add file logic

SELECT @autogrowth_soon = 1

END

END --%

ELSE

BEGIN --KB

IF (@ReservedSizeMB > @dbgrowthvalue / 1024)

BEGIN

--free space > growthvalue in KB, no work to do

SELECT 'Adequate FreeSpace in KB exists in fg ' + @fg + '. No work to do for this fg'

END

ELSE

BEGIN

SELECT @autogrowth_soon = 1

END

END --KB

--do we need to actually add a file?

IF (@autogrowth_soon = 1)

BEGIN

--check for number of files governor

IF (@override_max_num_files_governor = 0) AND (@total_files_in_fg >= @proc_count)

BEGIN

--not overriding and filecount >= proc count

SELECT @message = 'File cannot be created for filegroup: ' + @fg + ' because number of files in filegroup >= processors in SQL. This can be overridden with @override_max_num_files_governor'

RAISERROR (@message, 16,1) WITH LOG

FETCH NEXT FROM filegps INTO @fg

CONTINUE

END

--were we passed a drive parameter to use for growth of given filegroup?

IF (@newdriveforfilegroup IS NOT NULL AND @filegroup=@fg)

BEGIN

--yes, check its space and build a file

SELECT @FreeMB = FreeMB

FROM DBA_DRIVE_STATISTICS

WHERE Drive = @newdriveforfilegroup

AND RunDtime = @runtime;

--drive must have at least 500MB, otherwise abort

IF (@FreeMB < 500 OR @FreeMB is NULL)

BEGIN

SELECT @message = 'Drive ' + @newdriveforfilegroup + ' was specified, but does not have at least 500MB free. Aborting.'

RAISERROR (@message,16,1) WITH LOG

FETCH NEXT FROM filegps INTO @fg

CONTINUE

END

IF (@use_entire_drive = 1)

BEGIN

--fill the drive (total - 500) with the new file

SELECT @exec_string = @exec_string + 'ADD FILE (NAME=' + @fg + '_' + convert(varchar(20),@total_files_in_fg + 1) + ' ,FILENAME='''

SELECT @exec_string = @exec_string + @newdriveforfilegroup + ':\' + @fg + '_' + convert(varchar(20),@total_files_in_fg + 1) + '.ndf'' '

SELECT @exec_string = @exec_string + ',SIZE=' + convert(varchar(20),CASE WHEN (@FreeMB - 500) > (@file_size_governor_GB * 1024) THEN (@file_size_governor_GB * 1024) ELSE (@FreeMB - 500) END) + ' MB ,FILEGROWTH=' + convert(varchar(20),@dbgrowthvalue)

SELECT @exec_string = @exec_string + CASE @dbgrowthtype WHEN 'PERCENT' THEN '%' ELSE @dbgrowthtype END + ') TO FILEGROUP ' + quotename(@fg)

SELECT 'Filegroup: ' + @fg + ' close to extend. Building a file to utilize entire specified drive: ' + @newdriveforfilegroup

SELECT 'Running the following command to add file:'

SELECT @exec_string

IF @debug_mode != 'YES'

BEGIN

EXEC (@exec_string)

END

END

ELSE

BEGIN

--(@use_entire_drive = 0)...use growth stats already determined above

--MB value specified...build file

IF (@dbgrowthtype = 'KB')

BEGIN

SELECT @exec_string = @exec_string + 'ADD FILE (NAME=' + @fg + '_' + convert(varchar(20),@total_files_in_fg + 1) + ' ,FILENAME='''

SELECT @exec_string = @exec_string + @newdriveforfilegroup + ':\' + @fg + '_' + convert(varchar(20),@total_files_in_fg + 1) + '.ndf'' '

SELECT @exec_string = @exec_string + ',SIZE=' + convert(varchar(20),CASE WHEN @dbgrowthvalue > (@file_size_governor_GB * 1024 * 1024) THEN (@file_size_governor_GB * 1024 * 1024) ELSE @dbgrowthvalue END) + ' KB '

SELECT @exec_string = @exec_string + ',FILEGROWTH=' + convert(varchar(20),@dbgrowthvalue)

SELECT @exec_string = @exec_string + CASE @dbgrowthtype WHEN 'PERCENT' THEN '%' ELSE @dbgrowthtype END + ') TO FILEGROUP ' + quotename(@fg)

END

ELSE

IF (@dbgrowthtype = 'PERCENT')

BEGIN

--% specified...determine value...build file

SELECT @new_sizeMB = convert(bigint,((@dbgrowthvalue * .01) * @TotalSizeMB))

SELECT @exec_string = @exec_string + 'ADD FILE (NAME=' + @fg + '_' + convert(varchar(20),@total_files_in_fg + 1) + ' ,FILENAME='''

SELECT @exec_string = @exec_string + @newdriveforfilegroup + ':\' + @fg + '_' + convert(varchar(20),@total_files_in_fg + 1) + '.ndf'' '

SELECT @exec_string = @exec_string + ',SIZE=' + convert(varchar(20),CASE WHEN @new_sizeMB > (@file_size_governor_GB * 1024) THEN (@file_size_governor_GB * 1024) ELSE @new_sizeMB END) + ' MB '

SELECT @exec_string = @exec_string + ',FILEGROWTH=' + convert(varchar(20),@dbgrowthvalue)

SELECT @exec_string = @exec_string + CASE @dbgrowthtype WHEN 'PERCENT' THEN '%' ELSE @dbgrowthtype END + ') TO FILEGROUP ' + quotename(@fg)

END

--run the string

SELECT 'Filegroup: ' + @fg + ' close to extend. Building a file to utilize portion of specified drive: ' + @newdriveforfilegroup

SELECT 'Running the following command to addd file:';

SELECT @exec_string;

IF (@debug_mode != 'YES')

BEGIN

EXEC (@exec_string);

END

END --@use_entire_drive

END --we were passed a drive param

ELSE

BEGIN

--no drive param passed, can we build a file using free space on an existing drivespec for fg?

--assume we need to keep at least 500 MB free on drive

--get list of available drivespecs

DECLARE @drv char(1), @file_created bit

SELECT @file_created = 0 , @new_sizeMB = NULL

DECLARE drives CURSOR FOR

SELECT DISTINCT Drive

FROM DBA_SPACE_STATISTICS

WHERE DBFileGroup = @fg

AND RunDtime = @runtime;

OPEN drives

FETCH NEXT FROM drives into @drv

WHILE (@@FETCH_STATUS = 0)

BEGIN

IF @file_created = 0

BEGIN --@file_created = 0

--for the given drive in the filegroup...does it have enough space to handle another file

SELECT @FreeMB = FreeMB

FROM DBA_DRIVE_STATISTICS

WHERE Drive = @drv

AND RunDtime = @runtime;

IF @FreeMB > 500

BEGIN

--space exists, build a file...determine @new_size

IF @dbgrowthtype = 'PERCENT'

BEGIN

SELECT @new_sizeMB = convert(bigint,((@dbgrowthvalue * .01) * @TotalSizeMB))

END

ELSE --KB

BEGIN

SELECT @new_sizeMB = (@dbgrowthvalue / 1024)

END

--ensure that @new_sizeMB < (@FreeMB - 500 MB)

IF (@new_sizeMB > (@FreeMB - 500))

BEGIN

--trying to allocate too much space, just use @FreeMB - 500

SELECT @new_sizeMB = @FreeMB - 500

END

--build string

SELECT @exec_string = @exec_string + 'ADD FILE (NAME=' + @fg + '_' + convert(varchar(20),@total_files_in_fg + 1) + ' ,FILENAME='''

SELECT @exec_string = @exec_string + @drv + ':\' + @fg + '_' + convert(varchar(20),@total_files_in_fg + 1) + '.ndf'' '

SELECT @exec_string = @exec_string + ',SIZE=' + convert(varchar(20),CASE WHEN @new_sizeMB > (@file_size_governor_GB * 1024) THEN (@file_size_governor_GB * 1024) ELSE @new_sizeMB END) + ' MB '

SELECT @exec_string = @exec_string + ',FILEGROWTH=' + convert(varchar(20),@dbgrowthvalue)

SELECT @exec_string = @exec_string + CASE @dbgrowthtype WHEN 'PERCENT' THEN '%' ELSE @dbgrowthtype END + ') TO FILEGROUP ' + quotename(@fg)

SELECT @file_created = 1

--run the string

SELECT 'Filegroup: ' + @fg + ' close to extend. Building a file to utilize space of existing data drive: ' + @drv

SELECT 'Running the following command: ';

SELECT 'Running: ' + @exec_string;

IF (@debug_mode != 'YES')

BEGIN

EXEC (@exec_string);

END

END

ELSE

BEGIN

--space does not exist check the next drv

SELECT @file_created = 0

END

END --@file_created = 0

FETCH NEXT FROM drives into @drv

END

CLOSE drives

DEALLOCATE drives

IF @file_created = 0

BEGIN

--we couldnt find existing drive space, log the fact

SELECT @message = 'Could not find at least 500 MB free space on any drive utilized for filegroup ' + @fg

RAISERROR (@message,16,1) WITH LOG

FETCH NEXT FROM filegps INTO @fg

CONTINUE

END

END --no drive param passed, can we build a file using free space on an existing drivespec for fg?

END --do we need to actually add a file?

FETCH NEXT FROM filegps INTO @fg

END --filegroup loop

CLOSE filegps

DEALLOCATE filegps

END --proc

 

SQL Server Data Files

DBA Scripts

Add new comment