Posted by

Posted on

February 6, 2024

Posted under

SQL Server 2022

Comments

FILESTREAM Feature is Disabled

Encountering errors during database restoration in SQL Server can be a daunting experience, especially when the process halts with messages about disabled features you thought were properly configured. One such error involves the FILESTREAM feature:

Msg 5591, Level 16, State 4, Line 2
FILESTREAM feature is disabled.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

This error halts the restoration of databases that utilize the FILESTREAM feature for storing unstructured data such as documents, images, and videos. Let’s dive into what causes this error and how you can resolve it, ensuring a smooth restoration process.

Understanding the Error

The error messages indicate two key points:

FILESTREAM feature is disabled: This implies that the operation you’re attempting requires the FILESTREAM feature, which is not currently enabled on your SQL Server instance.
RESTORE DATABASE is terminating abnormally: Following the first message, this indicates that the restore operation cannot proceed due to the disabled FILESTREAM feature.

This situation typically arises when trying to restore a database that uses FILESTREAM data on a server where FILESTREAM is not enabled.

How to Resolve the Error

Resolving this error involves a two-fold approach: enabling FILESTREAM on your SQL Server and then proceeding with the database restoration. Here’s a step-by-step guide to navigate this process.

1. Enable FILESTREAM on SQL Server

First, you need to enable FILESTREAM at the server level:

Open SQL Server Configuration Manager: Navigate to the SQL Server Services.
Modify SQL Server Instance Properties: Right-click on your SQL Server instance > Properties > FILESTREAM tab.
Enable FILESTREAM: Check the boxes to enable FILESTREAM for Transact-SQL access, for file I/O streaming access, and for remote clients as per your requirements.
Restart SQL Server Service: Apply the changes and restart the service for the changes to take effect.

2. Enable FILESTREAM at the SQL Instance Level

After enabling FILESTREAM on the server, you must also enable it at the SQL instance level:

Open SQL Server Management Studio (SSMS): Connect to your SQL Server instance.
Run the SQL Script to Enable FILESTREAM: Execute the following commands:

EXEC sp_configure filestream_access_level, 2

RECONFIGURE

3. Restoring the Database

With FILESTREAM enabled both on the server and at the instance level, you’re now set to retry the database restore operation. This can be done through SSMS or by using the RESTORE DATABASE T-SQL command.

Tips and Considerations

Backup Strategy: Always have a robust backup strategy, especially for databases utilizing features like FILESTREAM.
Compliance and Policies: Enable FILESTREAM in accordance with your organization’s data and security policies.
Testing: Test the restore process in a non-production environment to ensure everything works as expected before making changes in a live environment.

Conclusion

Encountering the “FILESTREAM feature is disabled” error during database restoration can be frustrating, but it’s a manageable issue. By carefully enabling FILESTREAM on both the server and instance levels, you can overcome this hurdle. Always remember the importance of understanding SQL Server’s features and configurations to maintain smooth database operations.

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.

Posted by

Vivek Janakiraman

Posted on

February 6, 2024

Posted under

AlwaysON

Comments

SQL Server Always On Series: Availability group DDL operations are permitted only when you are using the master database

Introduction
During a recent attempt to perform a manual failover from Always On Availability Group from JBSAG1 to JBSAG2 using Microsoft SQL Server Management Studio (SSMS), an unexpected error disrupted the process. The error, specifically identified as Microsoft SQL Server Error 35208, posed a challenge to the manual failover operation. In this detailed account, we’ll explore the encountered issue, outline the steps taken to address it, and ultimately achieve a successful manual failover.

Issue
Upon initiating the Always On Availability Group manual failover, the process encountered a hurdle with the following error:

TITLE: Microsoft SQL Server Management Studio
Manual Failover failed (Microsoft.SqlServer.Management.HadrTasks)

ADDITIONAL INFORMATION:
Failed to perform a manual failover of the availability group ‘JBSWiki’ to server instance ‘JBSAG2’. (Microsoft.SqlServer.Management.HadrModel)

For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=16.100.47021.0&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&LinkId=20476

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Availability group DDL operations are permitted only when you are using the master database. Run the ‘USE master’ statement, and retry your availability group DDL statement. (Microsoft SQL Server, Error: 35208)
For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-35208-database-engine-error

Navigating through this error was the initial challenge, particularly when attempting the failover using the SSMS graphical user interface (GUI). Executing the ‘USE master’ statement in this context presented uncertainties, prompting a reevaluation of the failover approach.

Solution
To address the dilemma, the following steps were taken:

GUI Failover Attempt:

Initially, the failover was attempted through the SSMS GUI, raising questions about how to execute the ‘USE master’ statement within the graphical interface.

Scripted Failover Action:

The failover wizard was restarted, and instead of concluding the process through the GUI, the failover action was scripted for manual execution. I started the failover wizard again and this time instead of clicking finish at the end, tried scripting the failover action.

Manual Execution of Failover Command:

The failover command was manually executed from the SSMS SQLCMD query window, successfully completing the failover process.

Database Context Discovery:

Investigation revealed that the database context for a new query window was set to a user database (JBDB) instead of ‘master,’ leading to a pivotal realization. I tried clicking on a new query window and I saw that the database context for that query window was set to an user database JBDB and not master.

Connection Options Adjustment:

This is when I realized that I might have connected to the SQL Server instance with an User Database specified on the “Connect to database” in “Options <<” as part of making a connection from SSMS. Please check screenshot below,

After realizing this, I disconnected the existing sessions on SSMS and changed “Connect to database” in “Options <<” to “Master” and connected to SQL server JBSAG1 and JBSAG2.

Success After Correction:

Following these corrective actions, subsequent manual failover and failback attempts via SSMS were executed seamlessly.

Summary
In summary, this journey through troubleshooting manual failover with error 35208 underscored the importance of the database context, especially when initiating DDL operations. The solution involved a meticulous adjustment of connection options within SSMS, ensuring a connection to the ‘master’ database before attempting manual failover. This article provides a detailed account of the encountered challenge, the thought process behind the solution, and the successful resolution achieved through careful steps and insights.

Thank You,
Vivek Janakiraman

Posted by

Vivek Janakiraman

Posted on

January 30, 2024

Posted under

Azure Managed Instance

Comments

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