SQL Server can fail to start after Azure recreates local temporary storage because the configured tempdb directory and permissions are missing. Use the SQL IaaS Agent extension as the supported solution, with custom startup automation only as a fallback.

SQL Server tempdb on Azure Ephemeral Disks: It Does Not Need Persistence. Its Directory Does.

$Three-panel comic: a DBA in a server room says “ok reboot succesful!” next to a screen reading “SERVER REBOOT SUCCESSFUL.” He then says “wait why sql server is not up?” as a screen shows SQL Server service MSSQLSERVER “Not Running” with the error “Could not start service ‘MSSQLSERVER’ on server. The system cannot find the path specified.” Finally he asks “what happened to the path?” as a screen shows “SQL Server Startup Failed… Wrong Path: C:\SQL\Data\tempdb.mdf” next to another screen reading “D:\SQLData\ … Path not found.”$

02:15. A scheduled maintenance window completes on an Azure VM running SQL Server.

The VM is healthy. Windows is running. The local temporary disk is online.

SQL Server is not.

The error log shows:

Unable to open the physical file ‘D:\SQLTEMP\tempdb.mdf’. Operating system error 3: The system cannot find the path specified.

The D: drive exists.

The D:\SQLTEMP directory does not.

That distinction matters.

Losing the contents of tempdb is expected. SQL Server recreates tempdb every time the Database Engine starts. Losing the directory that SQL Server expects to use is different. If the directory is missing, or the SQL Server service account cannot access it, tempdb initialization fails and SQL Server may not start.

The first response should not automatically be “build a scheduled task.”

For SQL Server VMs registered with the SQL IaaS Agent extension, Azure can manage the tempdb folder and its permissions after reprovisioning. A custom PowerShell startup process should normally be the fallback for unmanaged or nonstandard deployments.

What actually failed?

The configured location of each tempdb file is stored in SQL Server metadata:

SELECT
    file_id,
    name,
    type_desc,
    physical_name,
    state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb')
ORDER BY file_id;

Assume the files point to:

D:\SQLTEMP\

Azure local temporary storage is nonpersistent. Its contents can be lost when the VM is stopped, redeployed, moved during certain maintenance events, or otherwise reprovisioned.

A successful standard restart does not necessarily erase the temporary disk. That is precisely why this issue can remain hidden: several restarts may work, followed by a later Azure lifecycle event that recreates the local storage.

When the disk is recreated, the drive can return without the custom directory structure and NTFS permissions that previously existed.

SQL Server then follows this sequence:

SQL Server service starts
    ↓
SQL Server reads the configured tempdb paths
    ↓
D:\SQLTEMP does not exist, or the service account cannot access it
    ↓
SQL Server cannot create the tempdb files
    ↓
tempdb initialization fails
    ↓
SQL Server startup fails

The failure is not that tempdb data was lost.

The failure is that the prerequisites for recreating tempdb were lost.

Is the local temporary disk still the right place for tempdb?

For most standalone SQL Server workloads on Azure VMs, the local temporary disk can be a good location for tempdb.

The reasons are straightforward:

tempdb is recreated during every SQL Server startup.
Its contents do not need to survive a restart.
Local temporary storage can provide low-latency, high-throughput I/O.
Its I/O does not consume the provisioned IOPS of the managed data disks.

This is not a universal rule.

Before using the local disk, verify:

The VM SKU provides local temporary storage.
The disk has enough capacity for peak tempdb usage.
The selected VM family supports the intended configuration.
The SQL Server instance is not using an unsupported ephemeral-storage pattern.
A future VM resize will not remove or reduce the local disk.
Monitoring covers both disk availability and free space.

Microsoft recommends the local temporary disk for most SQL Server workloads that are not part of a failover cluster instance.

For an FCI, shared storage remains the default recommendation. Placing tempdb on local ephemeral storage is an advanced configuration that requires custom monitoring because loss of that local disk does not trigger the same cluster behavior as loss of shared storage.

Important: Some newer VM series with an uninitialized NVMe ephemeral disk have specific SQL Server deployment limitations. Confirm the current Azure documentation for the selected VM SKU before placing tempdb there.

Preferred solution: let Azure manage the folder

For a supported SQL Server VM registered with the SQL IaaS Agent extension, use the SQL virtual machine resource to manage the tempdb directory and permissions.

In the Azure portal:

Open the SQL virtual machines resource for the VM.
Select Storage configuration.
Select Configure next to tempdb.
Enable Configure tempdb data files.
Enable Manage tempdb database folders on restart.
Review the path, file count, initial size, and autogrowth configuration.
Apply the change.
Restart the SQL Server service during an approved maintenance window.
Validate the configuration after restart.

The SQL IaaS Agent extension can recreate the required folder and permissions when the local temporary storage has been reprovisioned.

This is preferable to custom startup orchestration because:

SQL Server can remain configured for normal automatic startup.
The configuration stays inside the supported Azure SQL VM management model.
There is no custom scheduled task to maintain.
There is no separate PowerShell script to secure, monitor, and version.
Operational ownership remains clearer.

Validate the Azure-managed configuration

Check the configured files:

SELECT
    file_id,
    name,
    type_desc,
    physical_name,
    size * 8.0 / 1024 AS size_mb,
    CASE
        WHEN is_percent_growth = 1
            THEN CONCAT(growth, N'%')
        ELSE CONCAT(growth * 8.0 / 1024, N' MB')
    END AS autogrowth
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb')
ORDER BY file_id;

After a controlled restart, confirm:

The configured folder exists.
The SQL Server service account has the required NTFS permissions.
SQL Server starts without manual intervention.
Every tempdb file is created in the expected location.
The SQL Server error log contains no file-access or tempdb initialization failures.
The SQL IaaS Agent extension is healthy.

When a custom fallback is justified

Custom startup automation may still be appropriate when:

The VM is not registered with the SQL IaaS Agent extension.
The extension cannot be used because of platform or policy constraints.
The SQL Server installation or disk layout is nonstandard.
Multiple instances require separate folder and service handling.
An existing orchestration platform already controls service startup.
The Azure storage configuration pane is unavailable for the deployment.

In that case, the automation must do more than create a folder.

It should:

Wait for the expected volume to become available.
Confirm that the volume is healthy.
Create the folder idempotently.
Apply the required NTFS permissions.
Start SQL Server by its actual Windows service name.
Wait for SQL Server to reach the Running state.
Start SQL Server Agent only after the Database Engine is running.
Log success and failure.
Return a nonzero exit code on failure.
Prevent overlapping executions.

Hardened PowerShell fallback

The following example targets a default SQL Server instance.

Adjust:

$FolderPath
$SqlServiceAccount
$DriveLetter
service names for named instances
timeout and retry values
the logging location

Save it as:

C:\Scripts\Initialize-SqlTempDb.ps1

[CmdletBinding()]
param()

Set-StrictMode -Version Latest
$ErrorActionPreference = 'Stop'

$FolderPath        = 'D:\SQLTEMP'
$DriveLetter       = 'D'
$SqlServiceAccount = 'DOMAIN\sqlservice'

# Actual Windows service names for a default instance.
$SqlServiceName   = 'MSSQLSERVER'
$AgentServiceName = 'SQLSERVERAGENT'

$LogDirectory     = 'C:\ProgramData\SqlTempDbStartup'
$LogFile          = Join-Path $LogDirectory 'Initialize-SqlTempDb.log'
$VolumeTimeoutSec = 120
$ServiceTimeout   = [TimeSpan]::FromMinutes(3)

function Write-Log {
    param(
        [Parameter(Mandatory)]
        [string] $Message,

        [ValidateSet('INFO', 'WARN', 'ERROR')]
        [string] $Level = 'INFO'
    )

    if (-not (Test-Path -LiteralPath $LogDirectory)) {
        New-Item -Path $LogDirectory -ItemType Directory -Force | Out-Null
    }

    $timestamp = Get-Date -Format 'yyyy-MM-dd HH:mm:ss.fff'
    Add-Content -LiteralPath $LogFile -Value "$timestamp [$Level] $Message"
}

try {
    Write-Log 'Starting tempdb directory initialization.'

    $deadline = (Get-Date).AddSeconds($VolumeTimeoutSec)
    $volume = $null

    do {
        $volume = Get-Volume -DriveLetter $DriveLetter -ErrorAction SilentlyContinue

        if ($null -eq $volume) {
            Start-Sleep -Seconds 5
        }
    }
    until ($null -ne $volume -or (Get-Date) -ge $deadline)

    if ($null -eq $volume) {
        throw "Volume $DriveLetter`: was not available within $VolumeTimeoutSec seconds."
    }

    if ($volume.HealthStatus -ne 'Healthy') {
        throw "Volume $DriveLetter`: is not healthy. Current state: $($volume.HealthStatus)."
    }

    New-Item -Path $FolderPath -ItemType Directory -Force | Out-Null
    Write-Log "Confirmed directory $FolderPath."

    $acl = Get-Acl -LiteralPath $FolderPath

    $rule = New-Object System.Security.AccessControl.FileSystemAccessRule(
        $SqlServiceAccount,
        'Modify',
        'ContainerInherit,ObjectInherit',
        'None',
        'Allow'
    )

    $acl.SetAccessRule($rule)
    Set-Acl -LiteralPath $FolderPath -AclObject $acl

    Write-Log "Granted Modify permission to $SqlServiceAccount on $FolderPath."

    $sqlService = Get-Service -Name $SqlServiceName -ErrorAction Stop

    if ($sqlService.Status -ne 'Running') {
        Start-Service -InputObject $sqlService
        $sqlService.WaitForStatus('Running', $ServiceTimeout)
        Write-Log "Service $SqlServiceName reached Running state."
    }
    else {
        Write-Log "Service $SqlServiceName is already running."
    }

    $agentService = Get-Service -Name $AgentServiceName -ErrorAction Stop

    if ($agentService.Status -ne 'Running') {
        Start-Service -InputObject $agentService
        $agentService.WaitForStatus('Running', $ServiceTimeout)
        Write-Log "Service $AgentServiceName reached Running state."
    }
    else {
        Write-Log "Service $AgentServiceName is already running."
    }

    Write-Log 'tempdb startup initialization completed successfully.'
    exit 0
}
catch {
    Write-Log -Level 'ERROR' -Message $_.Exception.Message
    exit 1
}

Do not use display names as service names

For a default instance, the Windows service names are normally:

MSSQLSERVER
SQLSERVERAGENT

For a named instance called INST1, they are normally:

$SqlServiceName   = 'MSSQL$INST1'
$AgentServiceName = 'SQLAgent$INST1'

The $ is literal because the strings use single quotes.

Confirm the actual service names before deployment:

Get-Service |
    Where-Object {
        $_.Name -like 'MSSQL*' -or
        $_.Name -like 'SQLAgent*'
    } |
    Select-Object Name, DisplayName, Status, StartType

Do not confuse those values with display names such as:

SQL Server (MSSQLSERVER)
SQL Server Agent (MSSQLSERVER)

Managed service accounts

For an MSA or gMSA, include the trailing $ in the NTFS principal:

$SqlServiceAccount = 'DOMAIN\sqlsvc$'

Why grant Modify instead of Full Control?

The SQL Server service account needs to create, read, write, extend, and delete its tempdb files.

Modify normally provides those capabilities without also granting the ability to change ownership and permissions.

Use broader permissions only when there is a documented requirement.

Configure service startup for the fallback

The scheduled task must complete before SQL Server tries to initialize tempdb.

For this fallback design, set SQL Server and SQL Server Agent to manual startup:

Set-Service -Name 'MSSQLSERVER' -StartupType Manual
Set-Service -Name 'SQLSERVERAGENT' -StartupType Manual

For a named instance:

Set-Service -Name 'MSSQL$INST1' -StartupType Manual
Set-Service -Name 'SQLAgent$INST1' -StartupType Manual

SQL Server Configuration Manager is also appropriate.

Do not change SQL Server service accounts through services.msc. SQL Server Configuration Manager performs SQL Server-specific service-account configuration that the generic Windows Services console does not.

Changing only the Windows startup type is a Service Control Manager operation and can be performed through Set-Service, sc.exe, or SQL Server Configuration Manager.

Create the scheduled task

Create a task that:

Runs at system startup.
Runs as SYSTEM.
Runs with highest privileges.
Does not launch a second copy while the first is running.
Retries after a transient startup failure.
Records its history.
Preserves the script’s exit code.

Example:

$TaskName   = 'Initialize SQL Server tempdb'
$ScriptPath = 'C:\Scripts\Initialize-SqlTempDb.ps1'
$PowerShell = "$env:SystemRoot\System32\WindowsPowerShell\v1.0\powershell.exe"

$action = New-ScheduledTaskAction `
    -Execute $PowerShell `
    -Argument "-NoProfile -NonInteractive -ExecutionPolicy Bypass -File `"$ScriptPath`""

$trigger = New-ScheduledTaskTrigger -AtStartup

$principal = New-ScheduledTaskPrincipal `
    -UserId 'SYSTEM' `
    -LogonType ServiceAccount `
    -RunLevel Highest

$settings = New-ScheduledTaskSettingsSet `
    -MultipleInstances IgnoreNew `
    -ExecutionTimeLimit (New-TimeSpan -Minutes 10) `
    -RestartCount 3 `
    -RestartInterval (New-TimeSpan -Minutes 1)

Register-ScheduledTask `
    -TaskName $TaskName `
    -Action $action `
    -Trigger $trigger `
    -Principal $principal `
    -Settings $settings `
    -Description 'Creates the tempdb folder, applies NTFS permissions, and starts SQL Server services.' `
    -Force

Verify it:

Get-ScheduledTask -TaskName 'Initialize SQL Server tempdb'

Configure the tempdb files

Review the current configuration:

SELECT
    file_id,
    name,
    type_desc,
    physical_name,
    size * 8.0 / 1024 AS size_mb,
    growth,
    is_percent_growth
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb')
ORDER BY file_id;

Move the primary data and log files:

ALTER DATABASE tempdb
MODIFY FILE
(
    NAME = N'tempdev',
    FILENAME = N'D:\SQLTEMP\tempdb.mdf'
);

ALTER DATABASE tempdb
MODIFY FILE
(
    NAME = N'templog',
    FILENAME = N'D:\SQLTEMP\templog.ldf'
);

Repeat ALTER DATABASE ... MODIFY FILE for every additional tempdb data file.

The metadata changes immediately, but SQL Server uses the new path only after the next restart.

File count and sizing

Do not apply “one file per CPU” as an unconditional rule.

A reasonable starting point is:

Use equally sized tempdb data files.
Use the same fixed autogrowth increment for all data files.
For servers with up to eight logical processors, starting with the same number of files is common.
For larger servers, eight files is a common starting point.
Add files in equal-sized groups only when allocation contention remains.
Pre-size the files for the normal workload.
Avoid percentage-based autogrowth.

The final configuration should be driven by measured contention, capacity, and workload behavior—not CPU count alone.

Validate the fallback properly

Running the task while SQL Server is already online does not validate the failure scenario.

A controlled non-reboot test should include:

Confirm the rollback plan.
Stop SQL Server Agent.
Stop SQL Server.
Rename or remove the tempdb directory.
Run the scheduled task manually.
Confirm that the directory was recreated.
Confirm the NTFS permissions.
Confirm that SQL Server reached Running.
Confirm that SQL Server Agent reached Running.
Confirm that all tempdb files were created in the expected location.
Review the script log.
Review the scheduled-task result.
Review the SQL Server error log.

Run the task:

Start-ScheduledTask -TaskName 'Initialize SQL Server tempdb'

Inspect its result:

Get-ScheduledTask -TaskName 'Initialize SQL Server tempdb' |
    Get-ScheduledTaskInfo |
    Select-Object LastRunTime, LastTaskResult, NextRunTime

Check the directory and ACL:

Test-Path -LiteralPath 'D:\SQLTEMP'
Get-Acl -LiteralPath 'D:\SQLTEMP' | Format-List

Check the services:

Get-Service -Name 'MSSQLSERVER', 'SQLSERVERAGENT' |
    Select-Object Name, Status, StartType

Check the active tempdb files:

SELECT
    file_id,
    name,
    type_desc,
    physical_name,
    state_desc
FROM tempdb.sys.database_files
ORDER BY file_id;

Review the SQL Server error log:

EXEC sys.xp_readerrorlog 0, 1, N'tempdb';

This validates the script logic.

It does not fully validate the startup trigger or Azure reprovisioning behavior.

Before declaring the design production-ready, test at least one approved Azure lifecycle scenario in a nonproduction environment:

Guest OS restart.
Stop/deallocate/start.
VM resize.
Azure redeploy.

The expected result is not that the temporary disk retains its contents.

The expected result is that SQL Server starts successfully even when the previous folder and files are gone.

Save this

Run this after any Azure lifecycle event — restart, stop/deallocate/start, resize, or redeploy — to confirm tempdb actually came back healthy, not just that SQL Server is accepting connections.

-- 1. Did every tempdb file load from the configured path?
SELECT file_id, name, type_desc, physical_name, state_desc
FROM tempdb.sys.database_files
ORDER BY file_id;

-- 2. Any tempdb file-access or initialization failures logged at startup?
EXEC sys.xp_readerrorlog 0, 1, N'tempdb';

# 3. Does the configured folder exist, and does the service account still have access?
Test-Path -LiteralPath 'D:\SQLTEMP'
Get-Acl -LiteralPath 'D:\SQLTEMP' | Format-List

# 4. Are SQL Server and SQL Server Agent both actually running (not just Automatic)?
Get-Service -Name 'MSSQLSERVER', 'SQLSERVERAGENT' | Select-Object Name, Status, StartType

If using the SQL IaaS Agent extension, confirm its health in the SQL virtual machines resource. If using the custom fallback, confirm the scheduled task’s LastTaskResult is 0.

Reproduce the failure safely, non-production only:

Stop SQL Server Agent, then SQL Server.
Rename or delete the configured tempdb folder.
Start SQL Server.
Confirm it either fails to start with the expected file-access error, or — with the extension managing the folder — recreates the folder and starts normally.
Restore the environment from the rollback plan before handing it back.

Rule of thumb: a successful restart proves nothing about this failure mode. Only a test that removes the folder first does.

Monitoring requirements

Whether Azure or custom automation manages the folder, monitor the dependency.

At minimum, monitor:

SQL Server service state.
SQL Server Agent service state.
SQL IaaS Agent extension health.
Existence and accessibility of the configured tempdb path.
Free space on the local temporary disk.
Disk latency and throughput.
Scheduled-task failures, when using the fallback.
PowerShell log errors, when using the fallback.
SQL Server error-log entries related to file access and tempdb initialization.
VM SKU changes that alter local temporary-storage capacity or availability.

A folder-existence check alone is insufficient.

The folder may exist while:

The service account has lost access.
The drive letter points to the wrong volume.
The disk is nearly full.
The SQL Server service is failing for a different tempdb reason.
The SQL IaaS Agent extension is unhealthy.

Rollback

To remove the custom fallback:

Move every tempdb file to persistent storage:

ALTER DATABASE tempdb
MODIFY FILE
(
    NAME = N'tempdev',
    FILENAME = N'E:\SQLDATA\tempdb.mdf'
);

ALTER DATABASE tempdb
MODIFY FILE
(
    NAME = N'templog',
    FILENAME = N'E:\SQLLOG\templog.ldf'
);

Repeat the command for every additional tempdb data file.
Restart SQL Server and confirm that all files use the persistent paths.
Restore automatic startup:

Set-Service -Name 'MSSQLSERVER' -StartupType Automatic
Set-Service -Name 'SQLSERVERAGENT' -StartupType Automatic

Remove the scheduled task:

Unregister-ScheduledTask `
    -TaskName 'Initialize SQL Server tempdb' `
    -Confirm:$false

Archive or remove the custom script and monitoring configuration.

What not to do

Do not assume every reboot wipes the Azure temporary disk.
Do not assume a successful reboot proves the design is safe from reprovisioning.
Do not build a scheduled task before checking whether the SQL IaaS Agent extension can manage the folder.
Do not use SQL Server service display names in Start-Service.
Do not grant Full Control when Modify is sufficient.
Do not validate only while SQL Server is already running.
Do not use “one tempdb file per CPU” as a universal sizing rule.
Do not move tempdb to a local disk without validating VM capacity and SKU behavior.
Do not leave manual service startup undocumented when using the fallback.

Conclusion

Putting tempdb on Azure local temporary storage is not inherently the mistake.

The mistake is assuming that its directory and permissions will always be there when SQL Server starts.

For registered SQL Server VMs, the preferred solution is to let the SQL IaaS Agent extension manage the folder and permissions.

For deployments where that is not possible, use a hardened startup process that validates the volume, recreates the directory, applies least-privilege permissions, starts the services in order, logs failures, and is tested against an actual Azure lifecycle event.

tempdb does not need persistence.

Its startup prerequisites do.

Official references

Continue reading

Marios Pavlidis
Principal Database Administrator