Tempdb is empty and not created from model database after Azure SQL MI restarts at maintenance window

Li, Jindong 46

Hi,

I find that tempdb is empty and not created from model database after Azure SQL MI restarts at maintenance window.

I want to know is there any way to make tempdb create from model database? If no, is there any way to trigger a procedure after SQL MI restarted?

Thanks

Best regards,

Jindong

Oury Ba-MSFT 20,716 Reputation points Microsoft Employee

2023-08-14T23:32:13.0866667+00:00

@Li, Jindong Thank you for reaching out.

TempDB is regenerated upon every start of the server instance and any objects that may have been created in TempDB during a previous session will not persist upon a service restart, an instance update management operation or a failover.

You can now configure the number of TempDB files and their growth increments to tune your instance performance even more. Your TempDB configurations will be persisted upon a server restart, a resize management operation or a failover. Additionally, a snapshot isolation property for TempDB will now also be persisted upon a server restart, an instance update management operation or a failover.

Improve your SQL Managed Instance performance with new TempDB configurations

I want to know is there any way to make tempdb create from model database? If no, is there any way to trigger a procedure after SQL MI restarted?

I haven't not tried that yet, but you might need to use SQL Server Agent service to trigger a procedure after an Azure SQL Managed Instance restarts.

Please check here Automate management tasks using SQL Agent jobs in Azure SQL Managed Instance

Regards,

Oury

Your answer