How to configure tempdb in Azure SQL Managed Instance(preview)

[JovanPop]

First published on MSDN on Mar 13, 2018

IMPORTANT:  Content of this article is not valid anymore. Managed Instance do not allow you to change the number of tempdb files. This was preview feature that is not available in the current version of the service.

Azure SQL Database Managed Instance (public preview) is a fully-managed SQL Server instance in Azure cloud. In the current public preview there are some known issues and bugs that will be fixed during public preview period. One of the potential issues is number of files and max_size that is set on tempdb . In this post, I will explain the issue and how you can solve it.



Managed Instance is currently in public preview and it has pre-configured tempdb database split into 12 files and each file has 14GB limit (max_size). This means that you can have queries that use up to 168 GB tempdb space.

Managed Instance allows you to manage database files. If you have workload that needs more tempdb space, you can alter files in tempdb and increase this limit:
alter database tempdb modify file (name = 'tempdev', maxsize = UNLIMITED)
alter database tempdb modify file (name = 'templog', maxsize = UNLIMITED)
alter database tempdb modify file (name = 'tempdev2', maxsize = UNLIMITED)
alter database tempdb modify file (name = 'tempdev3', maxsize = UNLIMITED)
alter database tempdb modify file (name = 'tempdev4', maxsize = UNLIMITED)
alter database tempdb modify file (name = 'tempdev5', maxsize = UNLIMITED)
alter database tempdb modify file (name = 'tempdev6', maxsize = UNLIMITED)
alter database tempdb modify file (name = 'tempdev7', maxsize = UNLIMITED)
alter database tempdb modify file (name = 'tempdev8', maxsize = UNLIMITED)
alter database tempdb modify file (name = 'tempdev9', maxsize = UNLIMITED)
alter database tempdb modify file (name = 'tempdev10', maxsize = UNLIMITED)
alter database tempdb modify file (name = 'tempdev11', maxsize = UNLIMITED)
alter database tempdb modify file (name = 'tempdev12', maxsize = UNLIMITED)

You can easily generate this script and put some other max_size value instead of UNLIMITED using the following query:
select concat('alter database tempdb modify file (name = ''',name,''', maxsize = UNLIMITED)')
from master.sys.master_files
where db_name(database_id) = 'tempdb'
One limitation in the current public preview is that tempdb don't preserves custom settings after fail-over happens. If you add new files to tempdb or change file size, these settings will not be preserved after fail-over, and original tempdb will be re-created on the new instance. This is a temporary limitation and it will be fixed during public preview.

However, since Managed Instance supports SQL Agent, and SQL Agent can be configured to execute some script when SQL Agent start, you can workaround this issue and create a SQL Agent job that will pre-configure your tempdb .

SQL Agent will start whenever Managed Instance fail-over and the job that contains script above can increase tempdb size before you start running your workload on the new instance.

One example of SQL Agent job that starts whenever Manage Instance starts and configures tempdb is shown in the following code:
USE [msdb]
GO

/****** Object: Job [Configure tempdb] Script Date: 3/13/2018 3:02:18 PM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]] Script Date: 3/13/2018 3:02:18 PM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Configure tempdb',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Increase max_size on tempdb] Script Date: 3/13/2018 3:02:18 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Increase max_size on tempdb',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'alter database tempdb modify file (name = ''tempdev'', maxsize = UNLIMITED)
alter database tempdb modify file (name = ''tempdev2'', maxsize = UNLIMITED)
alter database tempdb modify file (name = ''tempdev3'', maxsize = UNLIMITED)
alter database tempdb modify file (name = ''tempdev4'', maxsize = UNLIMITED)
alter database tempdb modify file (name = ''tempdev5'', maxsize = UNLIMITED)
alter database tempdb modify file (name = ''tempdev6'', maxsize = UNLIMITED)
alter database tempdb modify file (name = ''tempdev7'', maxsize = UNLIMITED)
alter database tempdb modify file (name = ''tempdev8'', maxsize = UNLIMITED)
alter database tempdb modify file (name = ''tempdev9'', maxsize = UNLIMITED)
alter database tempdb modify file (name = ''tempdev10'', maxsize = UNLIMITED)
alter database tempdb modify file (name = ''tempdev11'', maxsize = UNLIMITED)
alter database tempdb modify file (name = ''tempdev12'', maxsize = UNLIMITED)

',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'On failover',
@enabled=1,
@freq_type=64,
@freq_interval=0,
@freq_subday_type=0,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20180313,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959,
@schedule_uid=N'c2c03bd3-12be-4b41-ac40-b6dbe16479ad'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO