Introduction:
In the dynamic realm of Azure SQL Managed Instances, configuring database file sizes is a routine task. However, challenges can arise, as I recently encountered while attempting to modify the Tempdb database file size. This blog post details the steps taken to address a perplexing error message that surfaced during this process.

Problem Statement:
The objective was straightforward: adjusting the maximum file size of the Tempdb database on an Azure SQL Managed Instance to 25 GB.

However, both through the graphical user interface (GUI) and Transact-SQL (T-SQL) commands, an error persisted:

TITLE: Microsoft SQL Server Management Studio
——————————
Alter failed for Database ‘tempdb’.  (Microsoft.SqlServer.Smo)
For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=16.100.47008.0&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Alter+Database&LinkId=20476
——————————
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
——————————
MODIFY FILE failed for database ‘tempdb’, file id 1. Size of file (41435136 KB) is greater than MAXSIZE (26214400 KB). (Microsoft SQL Server, Error: 5040)
For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-5040-database-engine-error
——————————

The error surfaced when attempting the operation through the graphical user interface (GUI), and repeating the process using Transact-SQL (T-SQL) resulted in the identical error.

The error notification suggests that the existing size of the Tempdb database file (tempdev) exceeds the size I intended to establish. Contrary to this indication, the actual size of tempdev is only 16 MB, and my intention was to set the Maximum File size to 25 GB.

The error message indicates that the file size is reported as 41,435,136 KB, equivalent to 39.5 GB. Attempting to establish the Maximum file size of Tempdev below 39.5 GB resulted in an error. However, setting it to 40 GB proved successful.

This particular Azure SQL Managed Instance is of the Business Critical tier with 80 Vcores and includes 4 replicas. To delve deeper into the issue, I connected to the read replica of this managed instance through a query window, utilizing the ApplicationIntent=ReadOnly parameter.

I’ve established a connection to the user database JBSWiki to confirm that I am indeed connected to the Read Replica, as verified by executing the following query:

SELECT DATABASEPROPERTYEX(DB_NAME(), 'Updateability');

Upon execution, you will observe the result as READ_ONLY, affirming your connection to the Read Replica. It’s crucial to note that running the same query under the context of Tempdb will display READ_WRITE. To avoid confusion, ensure the query is executed on a user database to validate your connection to a Read Replica.

-> I executed below query to check the tempdb size on Read replica,

use tempdb
select file_id, type, type_desc, name, physical_name, size, max_size, growth, is_percent_growth from sys.database_files

-> Below is the output,

The size of tempdev on the read replica was configured at 39.5 GB, creating complications when attempting to adjust the Maximum file size on the Azure SQL Managed Instance. To address this, I reduced the size of tempdb on the read replica to 15 GB using DBCC SHRINKFILE. Following this adjustment, I successfully set the Maximum File size of the Tempdb data file to 25 GB.

Summary:
Navigating the nuances of database file sizes in an Azure SQL Managed Instance, particularly when dealing with read replicas, demands a comprehensive approach. Verifying the reported sizes, understanding the intricacies of database contexts, and proactive measures such as shrinking the Tempdb on read replicas are pivotal for resolving challenges like the ‘MODIFY FILE failed’ error. This journey underscores the significance of a meticulous troubleshooting process, ensuring a smooth configuration experience within the Azure SQL environment.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.