Tag Archives: #DisasterRecovery

Standard

Posted by

Posted on

January 30, 2024

Posted under

Azure Managed Instance

Comments

Leave a comment

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)


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.

Standard

Posted by

Posted on

December 26, 2023

Posted under

AlwaysON, Tsql

Comments

Leave a comment

Exploring Always On Availability Replica Details and Database Health

Introduction 🚀

Maintaining the health and performance of your SQL Server Availability Groups (AG) is essential for a resilient database infrastructure. In this blog post, we’ll delve into a T-SQL script designed to retrieve detailed information about your AG replicas and the associated database health. This script provides a comprehensive overview of key metrics, allowing database administrators to monitor and ensure the robustness of their AG configurations.

Requirements 🛠️

Before using the T-SQL script, ensure that the following requirements are met:

  1. SQL Server Availability Groups: The script is designed for environments with configured Availability Groups.
  2. Permissions: The account executing the script should have sufficient permissions to query the necessary dynamic management views.

T-SQL Script 📜

Use the following T-SQL script to obtain detailed information about your AG replicas and database health:

SELECT 

    ar.replica_server_name, 

    adc.database_name, 

    ag.name AS ag_name, 

    CASE HDRS.is_primary_replica

        WHEN 1 THEN 'Primary Replica'

        ELSE 'Secondary Replica'

    END AS Replica,

    HDRS.synchronization_state_desc, 

    HDRS.synchronization_health_desc, 

    HDRS.recovery_lsn, 

    HDRS.truncation_lsn, 

    HDRS.last_sent_lsn, 

    HDRS.last_sent_time, 

    HDRS.last_received_lsn, 

    HDRS.last_received_time, 

    HDRS.last_hardened_lsn, 

    HDRS.last_hardened_time, 

    HDRS.last_redone_lsn, 

    HDRS.last_redone_time, 

    HDRS.log_send_queue_size, 

    HDRS.log_send_rate, 

    HDRS.redo_queue_size, 

    HDRS.redo_rate, 

    HDRS.filestream_send_rate, 

    HDRS.end_of_log_lsn, 

    HDRS.last_commit_lsn, 

    HDRS.last_commit_time

FROM sys.dm_hadr_database_replica_states AS HDRS

INNER JOIN sys.availability_databases_cluster AS adc 

    ON HDRS.group_id = adc.group_id AND 

    HDRS.group_database_id = adc.group_database_id

INNER JOIN sys.availability_groups AS ag

    ON ag.group_id = HDRS.group_id

INNER JOIN sys.availability_replicas AS ar 

    ON HDRS.group_id = ar.group_id AND 

    HDRS.replica_id = ar.replica_id

This script combines data from various system views to present a holistic view of your AG configuration and the health status of associated databases.

Conclusion 🎉

Effectively managing your SQL Server Availability Groups involves not only understanding the configuration details but also regularly monitoring the health of replicas and databases. By utilizing the provided T-SQL script, database administrators can proactively identify and address potential issues, ensuring the continuous availability and reliability of their databases.

Regularly run this script and incorporate the insights gained into your monitoring routine to keep your SQL Server environment robust and resilient. May your databases always be available and your replicas in sync! 💪

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.

Standard

Posted by

Posted on

December 19, 2023

Posted under

AlwaysON, Tsql

Comments

Leave a comment

Performing Always On Availability Group Health Check for a Single Database

Introduction 🌐

Always On Availability Groups (AG) in SQL Server provide high availability and disaster recovery solutions for databases. Ensuring the health of your AG is crucial to maintaining a resilient database environment. In this blog post, we’ll guide you through a T-SQL script that performs a comprehensive health check for a single database within an AG. This script will be executed on the primary replica and includes valuable information such as synchronization state, database state, and secondary lag in seconds.

Requirements 🛠️

Before diving into the health check script, make sure you meet the following prerequisites:

  1. SQL Server Availability Group: Ensure that your SQL Server instance is configured with an Availability Group containing the target database.
  2. Permission: The account executing the script should have the necessary permissions to query the sys.dm_hadr_database_replica_states dynamic management view.
  3. Primary Replica: Run the script on the primary replica of the Availability Group.

T-SQL Script 📜

Here’s the T-SQL script that will provide a detailed health check for a specific database within your Availability Group:

select 

    db_name(database_id) as [Database],

    is_primary_replica,

    synchronization_state_desc,

    database_state_desc,

    is_suspended,

    suspend_reason_desc,

    recovery_lsn,

    truncation_lsn,

    last_sent_lsn,

    last_sent_time,

    last_received_lsn,

    last_received_time,

    last_hardened_lsn,

    log_send_queue_size,

    log_send_rate,

    redo_queue_size,

    redo_rate,

    end_of_log_lsn,

    last_commit_lsn,

    last_commit_time,

    secondary_lag_seconds 

from 

    sys.dm_hadr_database_replica_states

This script retrieves essential information about the specified database, such as synchronization state, log queue details, and secondary replica lag in seconds.

Conclusion 🎉

Regularly monitoring the health of your Always On Availability Group is fundamental to ensuring the stability and reliability of your SQL Server databases. By utilizing the provided T-SQL script, you can quickly assess the state of a single database within the AG, identify any potential issues, and take proactive measures to maintain a robust database infrastructure.

Remember to schedule periodic health checks to catch any anomalies early, minimizing the risk of downtime and data loss in your SQL Server environment. Stay vigilant, and may your databases always be available and resilient! 💪

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.