The recommended location for the tempDb on an Azure virtual machine, is the non-persisted D: drive. This drive is actually a hard drive connected to the underlying physical hardware, so it should offer better performance then the persisted data and OS disks. The persisted disks are located on Azure Blob Storage, and accessed via a local area network in the Azure data center.

When the VM is moved to a different physical host, the contents of the D: are lost. Most of the time, if you are just rebooting the VM, you will remain on the same physical host, and the contents of your D: will remain in place. 

Getting back to the tempDb, if you store it on the D:, you need to create a startup task to recreate your desired directory structure on D:, and assign the correct permissions. Even with the correct permissions on D:, SQL server will not create a directory structure for you. So if the path does not exist, SQL server will not start.

If you find yourself in the spot where SQL will not start, the easiest way to fix this, is to recreate the path on the D: so that you can get SQL server up and running, then move the tempDb.

For SQL Server 2012, there should be 2 error event with IDs 17204 and 5123 which will list the path that it is trying to use.

To move the tempDb, you can use the following SQL statements. You will need to restart SQL server in order for the changes to take effect.

USE master
go
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = ‘D:tempdb.mdf’)
go
ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = ‘D:templog.ldf’)
go

SELECT name, physical_name FROM sys.master_files WHERE database_id = DB_ID(‘tempdb’)
go

Startup Task

In order to protect myself from this again, I created a simple batch file which is run via the Windows Task scheduler at system start-up. This task creates a folder called D:SqlTemp and gives the MSSQLSERVER group modify permissions, which allows it to create the tempDb files.

Here is the batch file that I created. In addition to creating D:SqlTemp and setting the permissions, I created an additional Temp folder.

mkdir “d:sqlTemp”
mkdir “D:temp”
icacls “d:SqlTemp” /grant “MSSQLSERVER”:M
icacls “d:SqlTemp” /grant “AscendDevOps”:F
icacls “d:temp” /grant “AscendDevOps”:F

For the scheduled task, I have it configured to run as SYSTEM, with a trigger of “At System Startup”