Deploying a SQL VM using a marketplace image can sometimes lead to deployment failures, particularly when the selected size for the tempdb exceeds the capacity of the Windows OS disk. This issue arises even if the intention is to host the tempdb file on a separate, non-OS drive.
Understanding the limitation detail is crucial for a seamless deployment process and preventing avoidable errors. With careful planning and configuration, you can resolve this challenge and ensure your SQL VM deployment completes successfully.
Scenario:
When deploying a SQL Virtual Machine (VM) using a marketplace image using following configuration.
-
Local Storage Available for this VM Size: 300 GB on the D drive
-
OS Disk Size Selected: 127GB
-
Selected Tempdb Size: 180 GB (8 data files of 20 GB each, totaling 160 GB, plus a 20 GB log file)
-
Validation passes
-
However, the deployment will fail
Cause
This issue occurs because the overall size of the TempDB files surpasses the maximum allocated size for the OS disk, which is limited to 127 GB. As a result, the deployment cannot proceed as intended, even though the local storage on the D drive appears sufficient.
The below error can be seen in the operation details above for the failure:
{
"status": "Failed",
"error": {
"code": "Ext_StorageConfigurationSettings_ApplyNewTempDbSettingsError",
"message": "Error: 'Status(StatusCode=\"DeadlineExceeded\", Detail=\"Deadline Exceeded\")'"
}
}
Workaround
To resolve this issue, deploy a smaller tempdb size initially and then increase it post-deployment:
-
Initial Deployment: Set the initial size to 1 GB each for 8 data files (totaling 8 GB) and a 1 GB log file, making a total of 9 GB.
-
Post-Deployment: Use the following T-SQL commands to increase the tempdb size to the desired size (e.g., 20 GB):
USE [master]
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'temp2', SIZE = 20971520KB )
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'temp3', SIZE = 20971520KB )
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'temp4', SIZE = 20971520KB )
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'temp5', SIZE = 20971520KB )
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'temp6', SIZE = 20971520KB )
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'temp7', SIZE = 20971520KB )
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'temp8', SIZE = 20971520KB )
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', SIZE = 20971520KB )
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'templog', SIZE = 20971520KB )
GO
Conclusion
To avoid deployment failures related to exceeding the OS disk size, start by deploying with a smaller TempDB size and increase it after deployment. Always ensure the TempDB size is within the allocated disk limits during the initial deployment to prevent errors.
This article uses the Azure portal for the deployment example. If you're using other methods such as Azure CLI, ARM templates, or PowerShell, make sure to follow the appropriate steps for setting the TempDB size. While the specific process may vary, the key idea is to ensure the initial TempDB size stays within the available OS disk space to avoid deployment failures.
Updated Jan 07, 2025
Version 1.0RaghuTanikella
Microsoft
Joined January 09, 2020
Modernization Best Practices and Reusable Assets Blog
Follow this blog board to get notified when there's new activity