kohera-logo-regular.svg

Creating a scalable tempdb for your cloud servers

Vector_BG.png

Creating a scalable tempdb for your cloud servers

Vector_BG.png

When deploying to the cloud one of the hardest challenges is to get tempdb to scale in the same way as your virtual server.

Why is this, well as cloud machines are scalable and thus can change configurations during their lifecycle the number of tempdb files should be able to scale accordingly, so the amount of tempdb files becomes a dynamic value. Luckily for us, this parameter changes only after a shut down and restart of your virtual machine, so if we had a script that could adjust our tempdb needs every time the configuration changes we could deploy this and be more at ease.

This script does exactly that, if you place this code in a sql job that runs on startup, the script will adjust the tempdb settings accordingly every time you scale your cloud server.

I made the most important parameters completely adjustable while retaining maximum automation.

I did follow the best practices as much as possible which means that there will be an equal amount of tempdb files and cpu cores with a maximum of 8. This setting is not a parameter but can easily be adjusted when needed.

SET NOCOUNT ON;
/*

Script om tempdb te resetten op de Z:\ Drive van een (Amazon)VM

    1. Step wordt de tempdb gereset naar 1 file, en geschrinked naar 10GB

Dit is belangrijk omdat dan zowel de fileproperies als de filesettings correct geplaatst worden

  1. Daarna worden de overblijvende TempDB files effectief gewist via commandline (Om residu te voorkomen)
  2. Files worden opnieuw aangemaakt met de correcte size

*/
DECLARE @CMD nvarchar(200)
DECLARE @TempDBLocation nvarchar(200)
DECLARE @FilesizeInMB nvarchar(10)
DECLARE @FileGrowthInMB nvarchar(10)
DECLARE @TempDBPrimFile sysname
DECLARE @TempDBSecFile sysname
DECLARE @TempDBFileName varchar(2000)
DECLARE @Fileid int
DECLARE @Debug bit
DECLARE @Execute bit
DECLARE @AlwaysCleanUp bit
DECLARE @NumberOffFiles tinyint
DECLARE @NumberOffExistingFiles tinyint
SET @TempDBLocation=’Z:\’ –Location of tempdb, including the last \ sign
SET @FilesizeInMB=’1024′ — Filesize of the created files in MB
SET @FileGrowthInMB=’64’ — Filegrowth of the newly created files in MB
SET @Debug=0 –Set to 1 If you want the script to print the statements
SET @Execute=1 –Set to 1 If you want the script to execute the statements
SET @AlwaysCleanUp=0 –Set to 1 If you want the script to clean up the files on every reboot
SELECT @NumberOffFiles=CASE WHEN cpu_count < 8 THEN cpu_count ELSE 8 END FROM [sys].[dm_os_sys_info]
USE [tempdb];
Select @TempDBPrimFile=Name from sys.sysfiles where fileid=1
Select @NumberOffExistingFiles=MAX(fileid)-1,@Fileid=MAX(fileid)+1 from sys.sysfiles where groupid=1
IF SUBSTRING(@TempDBLocation,LEN(@TempDBLocation),1)<>’\’
BEGIN
PRINT ‘———————————————————-‘
PRINT ‘Error, Invallid location’
PRINT ‘———————————————————-‘
Goto Invalid
END
PRINT ‘———————————————————-‘
PRINT ‘Script Executing with the following parameters:’
PRINT ‘———————————————————-‘
PRINT ‘@TempDBLocation=’+@TempDBLocation
PRINT ‘@NumberOffFiles=’+CAST(@NumberOffFiles as varchar(5))
PRINT ‘@NumberOffExistingFiles=’+CAST(@NumberOffExistingFiles as varchar(5))
PRINT ‘@FilesizeInMB=’+@FilesizeInMB
PRINT ‘@FileGrowthInMB=’+@FileGrowthInMB
PRINT ‘@Debug=’+CAST(@Debug as char(1))
PRINT ‘@Execute=’+Cast(@Execute as char(1))
PRINT ‘@AlwaysCleanUp=’+CAST(@AlwaysCleanUp as char(1))
PRINT ‘———————————————————-‘
PRINT ‘Part 1 Resetting Tempdb to default settings on ‘+@TempDBLocation
PRINT ‘———————————————————-‘
IF (@AlwaysCleanUp=1 or @NumberOffExistingFiles<>@NumberOffFiles)
BEGIN
WHILE @NumberOffExistingFiles > 1
BEGIN
BEGIN TRY
SELECT @Fileid=MAX(fileid) from sys.sysfiles where groupid=1 and fileid < @Fileid
SELECT @TempDBSecFile=Name from sys.sysfiles where fileid=@Fileid
SET @CMD=’ALTER DATABASE [tempdb] REMOVE FILE [‘+@TempDBSecFile+’]’
IF @Execute=1 exec sp_executesql @CMD
IF @Debug=1 PRINT @CMD
END TRY BEGIN CATCH PRINT ERROR_MESSAGE() END CATCH
BEGIN TRY
SELECT @TempDBFileName=[filename] from sys.sysfiles where fileid=@Fileid
SET @CMD=’DEL ‘+@TempDBFileName+’ /Q’
IF @Execute=1 exec xp_cmdshell @CMD
IF @Debug=1 PRINT ‘EXEC xp_cmdshell ‘+@CMD
END TRY BEGIN CATCH PRINT ERROR_MESSAGE() END CATCH
set @NumberOffExistingFiles=@NumberOffExistingFiles-1
END
PRINT ‘———————————————————-‘
PRINT ‘Finished Part 2 removing residual files on ‘+@TempDBLocation
PRINT ‘———————————————————-‘
BEGIN TRY
SET @CMD = ‘USE [tempdb]; DBCC SHRINKFILE (N”’+@TempDBPrimFile+”’ , ‘+CAST(@FilesizeInMB*1024 as varchar(100))+’)’
IF @Execute=1 exec sp_executesql @CMD
IF @Debug=1 PRINT @CMD
END TRY BEGIN CATCH PRINT ERROR_MESSAGE() END CATCH
PRINT ‘———————————————————-‘
PRINT ‘Part 3 Recreating files with correct filesize on ‘+@TempDBLocation
PRINT ‘———————————————————-‘
WHILE @NumberOffFiles > 1
BEGIN
BEGIN TRY
SET @CMD=’USE [master]; ALTER DATABASE [tempdb] ADD FILE ( NAME = N”tempdev0’+CAST(@NumberOffFiles as char(1))+”’, FILENAME = N”’+@TempDBLocation+’tempdev0’+CAST(@NumberOffFiles as char(1))+’.ndf” , SIZE = ‘+@FilesizeInMB+’MB , FILEGROWTH = ‘+@FileGrowthInMB+’MB )’
IF @Execute=1 exec sp_executesql @CMD
IF @Debug=1 PRINT @CMD
END TRY BEGIN CATCH PRINT ERROR_MESSAGE() END CATCH
SET @NumberOffFiles=@NumberOffFiles-1
END
END
ELSE
PRINT ‘———————————————————-‘
PRINT ‘Had nothing to do’
Invalid:
PRINT ‘———————————————————-‘
PRINT ‘End of Script’
PRINT ‘———————————————————-‘

2319-blog-database-specific-security-featured-image
Database specific security in SQL Server
There are many different ways to secure your database. In this blog post we will give most of them a...
kohera-2312-blog-sql-server-level-security-featured-image
SQL Server security on server level
In this blog, we’re going to look at the options we have for server level security. In SQL Server we...
blog-security_1
Microsoft SQL Server history
Since its inception in 1989, Microsoft SQL Server is a critical component of many organizations' data infrastructure. As data has...
DSC_1388
Aggregaties
Power BI Desktop is een prachtige tool om snel data-analyses te kunnen uitvoeren. Je connecteert op een databron, importeert en...
dba image
DBA is not that scary
Often when talking to people who are looking for a career path in the data world I feel like there...
blog-2303
How do you link an SCD Type 2 table in Power Query?
This article uses a simple example to demonstrate how to link an SCD Type 2 table in Power Query to...