In the realm of database management, optimizing performance while minimizing resource contention is a perennial challenge. One of the critical areas where contention can be notably reduced is in the utilization of temporary storage mechanisms like tempdb. SQL Server’s In-Memory OLTP feature offers a compelling solution to this challenge, particularly through the use of in-memory optimized table variables. This post explores how leveraging In-Memory OLTP table variables can significantly improve performance by alleviating tempdb allocation contention, especially when compared to traditional tab ..read more

Introduction In the realm of SQL Server replication, encountering errors can be a daunting aspect of database management. One such error that frequently perplexes database administrators is the replication error 20598. This error typically arises during transactional replication when an UPDATE command is applied to a subscriber that cannot find the corresponding row. This blog post aims to demystify this error, exploring its causes and providing a step-by-step resolution to help you maintain the integrity and performance of your SQL Server replication environment. Issue The error message in qu ..read more

Transactional replication is a critical component of many SQL Server environments, providing high availability, load balancing, and other essential benefits. However, managing replication latency, the delay between an action occurring on the publisher and it being reflected on the subscriber, is vital for ensuring system performance and data integrity. In this blog post, we’ll explore a proactive approach to monitor and alert on replication latency, helping database administrators (DBAs) maintain optimal system health. The Issue: Replication latency can sometimes go unnoticed until it impacts ..read more

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 hur ..read more

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 ..read more

Introduction In the dynamic realm of database management, monitoring and optimizing query performance is paramount. SQL Server offers the Query Store feature, a powerful tool that captures and stores detailed information about query execution plans and runtime statistics. In this blog post, we’ll delve into leveraging T-SQL to extract valuable insights from the Query Store, focusing on queries executed within the last 7 days. Query Store Essentials Before we dive into T-SQL scripts, let’s briefly revisit the Query Store. It acts as a repository for query-related data, allowing database adminis ..read more

Introduction In the ever-evolving landscape of database management, one crucial aspect is performance monitoring. SQL Server provides a powerful tool called the Query Store, which allows database administrators to capture and analyze query performance over time. One key metric to consider is the duration for which the Query Store retains data. In this blog post, we will explore how to determine the number of days the Query Store has data for a specific database. The Query Store: An Overview The Query Store in SQL Server is a feature designed to simplify performance troubleshooting by persistin ..read more

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 ..read more

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 hur ..read more

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 ..read more