Technical Article

move tempdb

,

The script is na easy way to re-allocate your TempDb. It builds several T-SQL statements, depending on which parameter you've added. At least one new file location should be specified. The parameters regarding filesize, max filesize and growth steps are optional.If specified, these values are being used for every datafile.

If you specify 'Y' for the parameter @debug, no action is taken. Instead the commands are presented.

If you've specified the max filesize, this script also displays the maximum diskspace being used by Tempdb. This is a basic calculation for each filetype so you'll have to review these numbers by yourself.

The script does not check the number of datafiles compared to the nuber of CPUs

/*
Move TempDB datafiles
v20140909 Wilfred van Dijk (wvand@wilfredvandijk.nl)
*/use master
set nocount on
go

declare @new_datafile_location varchar(256)
declare @new_logfile_location varchar(256)
declare @new_datafile_size int
declare @new_datafile_maxsize int
declare @new_logfile_size int
declare @new_logfile_maxsize int
declare @new_datagrowth_size int
declare @new_loggrowth_size int
declare @new_datagrowth_is_percent bit
declare @new_loggrowth_is_percent bit

declare @debug char(1)
declare @SQLCmd table(id int identity(1,1), TSQL nvarchar(512), type int NULL)
declare @Statement nvarchar(512)
declare @max_datafile_size bigint
declare @max_logfile_size bigint
/*
MANDATORY PARAMETERS:
(at least one new location must be specified)
*/set @debug = 'Y'
set @new_datafile_location = -- (no backslash added)
set @new_logfile_location = -- (no backslash added)
/*
OPTIONAL PARAMETERS HERE:
*/--set @new_datafile_size = 
--set @new_logfile_size =
--set @new_datafile_maxsize = 
--set @new_logfile_maxsize =
--set @new_datagrowth_size =
--set @new_loggrowth_size =
--set @new_datagrowth_is_percent = 0
--set @new_loggrowth_is_percent = 0
/*
CREATE COMMANDS
1) create subdirectories
*/insert into @SQLCmd(TSQL)
select 'EXECUTE master.dbo.xp_create_subdir "'+ @new_datafile_location + '"'
where @new_datafile_location is not null
union
select 'EXECUTE master.dbo.xp_create_subdir "'+ @new_logfile_location + '"'
where @new_logfile_location is not null
/*
2) Change location(s)
*/insert into @SQLCmd
SELECT 'ALTER DATABASE Tempdb MODIFY FILE ( NAME = ' + name + ', FILENAME = "'+ @new_datafile_location + right(physical_name, charindex('\', reverse(physical_name))) + '"', type
FROM sys.master_files
WHERE database_id = 2 and type = 0
and @new_datafile_location is not null
;
insert into @SQLCmd
SELECT 'ALTER DATABASE Tempdb MODIFY FILE ( NAME = ' + name + ', FILENAME = "'+ @new_logfile_location + right(physical_name, charindex('\', reverse(physical_name))) + '"', type
FROM sys.master_files
WHERE database_id = 2 and type = 1
and @new_logfile_location is not null;
/*
3) Change size (if specified)
*/if @new_datafile_size is not null
update @SQLCmd
set TSQL = TSQL + ', SIZE = ' + cast(@new_datafile_size as varchar) + 'MB'
where type = 0
;
if @new_logfile_size is not null
update @SQLCmd
set TSQL = TSQL + ', SIZE = ' + cast(@new_logfile_size as varchar) + 'MB'
where type = 1;
/*
4) Change maxsize (if specified)
*/if @new_datafile_maxsize is not null
update @SQLCmd
set TSQL = TSQL + ', MAXSIZE = ' + cast(@new_datafile_maxsize as varchar) + 'MB'
where type = 0
;
if @new_logfile_maxsize is not null
update @SQLCmd
set TSQL = TSQL + ', MAXSIZE = ' + cast(@new_logfile_maxsize as varchar) + 'MB'
where type = 1;
/*
5) filegrowth settings (if specified)
*/if @new_datagrowth_size is not null
update @SQLCmd
set TSQL = TSQL + ', FILEGROWTH = ' + cast(@new_datagrowth_size as varchar) + case when @new_datagrowth_is_percent = 1 then '%' else 'MB' end
where type = 0
;
if @new_loggrowth_size is not null
update @SQLCmd
set TSQL = TSQL + ', FILEGROWTH = ' + cast(@new_loggrowth_size as varchar) + case when @new_loggrowth_is_percent = 1 then '%' else 'MB' end
where type = 1;
/*
FINALIZE: Add closing parenthesis
*/update @SQLCmd
set TSQL = TSQL + ')'
where type is not null
/*
Show max size allocated for TempDb
*/if @new_datafile_maxsize is not null
set @max_datafile_size = @new_datafile_maxsize * (select count(*) from @SQLCmd where type = 0)

if @new_logfile_maxsize is not null
set @max_logfile_size = @new_logfile_maxsize * (select count(*) from @SQLCmd where type = 1)

print '-- TempDb uses a maximum of '+ cast(@max_datafile_size as varchar) + 'MB for datafiles on '+ @new_datafile_location
print '-- TempDb uses a maximum of '+ cast(@max_logfile_size as varchar) + 'MB for logfiles on '+ @new_logfile_location
/*
Execute or display?
*/if @debug = 'Y'

select TSQL from @SQLCmd order by id

else

begin

declare c_lus cursor for
select TSQL from @SQLCmd

open c_lus
fetch next from c_lus into @Statement

while @@FETCH_STATUS = 0

begin

print '-- Executing: "' + @Statement + '"'
exec (@Statement)
fetch next from c_lus into @statement

end

close c_lus
deallocate c_lus

print '-- Finished'
print '-- Restart MSSQL Server to apply changes'
print '-- After the restart, delete the Tempdb datafiles at the old location'

end

Rate

5 (1)

Share

Share

Rate

5 (1)