Dynamically grow a data file

Question

Post reply

Dynamically grow a data file

MyDoggieJessie

SSC-Forever

Points: 44486
More actions
December 5, 2014 at 10:50 pm

#301739

Is it possible to dynamically grow a data file for TempDB?
I have some TSQL that I want to execute (this is raw tsql, not something I'd typically share) when I get an alert that TempDB is growing. SET NOCOUNT ON;
DECLARE @TempDB TABLE (FileID tinyint, name varchar(50), size bigint)
DECLARE @TotalSize decimal(19,4), @TotalFiles int, @CurrentTempDBSize bigint, @SQL varchar(1000), @DBName varchar(50)
SET @SQL = ''
INSERT INTO @TempDB
SELECT [file_id], name, size
FROM tempdb.sys.database_files WHERE [type] = 0
SELECT @TotalSize = SUM(size * 1.0/128), @TotalFiles = COUNT(1)
FROM @TempDB
SELECT @CurrentTempDBSize = SUM (unallocated_extent_page_count) * 8/1024
FROM sys.dm_db_file_space_usage
/* Determine if we have more than 80% of TempDB used, if so add some space */
IF (@CurrentTempDBSize/@TotalSize * 100) < 20
BEGIN
/* Determine if we are dealing with a single/multiple files */
IF (@TotalFiles > 1)
BEGIN
PRINT 'Grow multiple files'
WHILE (SELECT COUNT(1) FROM @TempDB) > 0
BEGIN
SELECT TOP 1 @DBName = name from @TempDB
SET @SQL = 'USE [master]; ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N''' + @DBName + ''', SIZE = 256MB)'
--PRINT @SQL
EXEC (@SQL)
DELETE FROM @TempDB WHERE name = @DBName
END
END
ELSE BEGIN
PRINT 'Grow single file'
END
ENDThe code runs fine, but doesn't actually increase the size of the file. This should be possible right?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 1

Why not just set it to auto-grow?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 2

Echoing Jeff´s question and adding some more, are you using a single file tempdb configuration? Do you know what is causing the growth? Are you experiencing any congestion in the tempdb?

😎

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 3

This is more for a case when there are multiple data files in TempDB and I don't wish to set autogrowth because I don't want to risk growing a single file...so I would grown both incrementally at the same time.

Of course, TempDB will be presized to start with; at a size that should never need to be extended - this is just for the unlikely event that no DBA is available during a time when it does need to grow; and a job could kick off to extended it out just a little until one is available to determine what caused the growth to begin with; and remedy the issue.

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 4

Eirikur Eiriksson

SSC Guru

Points: 182882

December 6, 2014 at 8:40 am

#1763122

Quick thought, use trace flags 1117 and 1118

😎

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 5

MyDoggieJessie (12/6/2014)
This is more for a case when there are multiple data files in TempDB and I don't wish to set autogrowth because I don't want to risk growing a single file...so I would grown both incrementally at the same time.
Of course, TempDB will be presized to start with; at a size that should never need to be extended - this is just for the unlikely event that no DBA is available during a time when it does need to grow; and a job could kick off to extended it out just a little until one is available to determine what caused the growth to begin with; and remedy the issue.

Writing code for what can be done automatically just makes more maintenance (troubles) for you and the system. As Eirikur suggested, consider the use of Trace Flags 1117 and 1118. Here are a couple of articles on them that are well worth the read. I don't know why MS doesn't just make them a default, especially since heavy hitters like Paul Randal have demonstrated that 1) there's no down side to turning them on and 2) there's quite the benefit of having them turned on and it's recommended by Microsoft and many other heavy hitters like Kendra Little.

http://www.brentozar.com/archive/2014/06/trace-flags-1117-1118-tempdb-configuration/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 6

Thanks guys, I wasn't aware of these trace flags. I read the article and do have some concerns that Trace flag 1117 will cause all files to grow equally (not just limited to TempDB), but that's something I can bring back to the table and discuss with the DBA group.

FWIW - I completely agree that what I was doing was only adding more unnecessary complexity to a problem that could be avoided, but was trying to find a way to accomplish what I was being asked to do.

Much appreciate the feedback and wisdom.

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 7

MyDoggieJessie (12/6/2014)
Thanks guys, I wasn't aware of these trace flags. I read the article and do have some concerns that Trace flag 1117 will cause all files to grow equally (not just limited to TempDB), but that's something I can bring back to the table and discuss with the DBA group.
FWIW - I completely agree that what I was doing was only adding more unnecessary complexity to a problem that could be avoided, but was trying to find a way to accomplish what I was being asked to do.
Much appreciate the feedback and wisdom.

You're a wise man for being concerned but read this. You concerns will vanish...

http://www.sqlskills.com/blogs/paul/misconceptions-around-tf-1118/[/url]

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)