Deadlock Simulator app for Developers

Recent, I was involved in assisting a deadlock issue and specifically the exception handling part in an application. Together with a colleague from Visual Studio/Languages team Daniel Sauer, we built a sample application to showcase a deadlock and to demonstrate how developers can handle deadlocks in their code. Of course the best option is to never…

0

Functions like STRING_AGG(), CONCAT_WS(), TRIM() Appear to Break in Visual Studio 2017 Database Projects

The following functions were introduced in SQL Server 2017. STRING_AGG() CONCAT_WS() TRIM() TRANSLATE(). Question: How come they don’t work in Visual Studio 2017? SQL72014: .Net SqlClient Data Provider: Msg 195, Level 15, State 10, Procedure uspStoredProc1, Line 6 ‘STRING_AGG’ is not a recognized built-in function name. Answer: Visual Studio 2017 ships with SQL Server 2016…

0

Automatically Generating SQL Server Memory Dumps at a Defined Interval

Over the years of troubleshooting SQL Server we have come to see the need for manually generating a memory dump of the SQL Server process for in-depth analysis. On several occasions, generating multiple memory dumps at a pre-defined interval has been required in order to see if the state of a particular thread or threads…

0

OS Hang or Out of Memory due to SQL Ser… No Wait, it’s SQL Analysis Services (SSAS)

Recently, we have observed a number of cases where DBAs or application developers are complaining about out-of-memory errors or even machine not responding (hangs) despite the fact that there is plenty of available memory on the system. Frequently this is on systems where SQL Server is running and the issue occurs after increasing the ‘max server…

0

Create a monitoring and notification mechanism for HADR worker thread Pool

A customer approached us asking for help on automatic a monitoring process. The goal was to send some type of notification when a particular threshold was reached. He had already discovered this blog but still needed some guidance on how to get notified. Monitoring SQL Server 2012 AlwaysOn Availability Groups Worker Thread Consumption We created…

0

Dealing with error 8169 “Conversion failed when converting from a character string to uniqueidentifier. “

Customer was receiving the following error: Msg 8169, Level 16, State 2, Line 1 Conversion failed when converting from a character string to uniqueidentifier. Here are all the ways that you can recreate this error: use tempdb go create table t1 (cuid uniqueidentifier default NEWID(), cint int) create table t2 (cuid_char varchar (20), cint int)…

0

Filestream and Full-Text – Full Solution for Document Indexing in SQL Server

Recently someone was asking if there is a simple SQL Server solution where you would generate text documents and simply pass them to SQL Server to catalog without writing an application to do this. Also, would like to be able to index and search the context of these documents. The solution in SQL Server would…

0

What really happens when HADR_CLUSAPI_CALL wait type is set?

In a customer scenario we saw a query against system views related to AlwaysOn taking a fairly long time. SELECT * FROM   sys.availability_databases_cluster adc INNER JOIN sys.availability_replicas ar ON adc.group_id = ar.group_id WHERE  adc.database_name = ‘db1’  Investigation showed that the query was predominantly waiting on HADR_CLUSAPI_CALL. As the name suggests, this is coming from the HADR (High-Availability-Disaster-Recovery) functionality of SQL, known as AlwaysOn. Also CLUSAPI_CALL is short (but…

0

DBMIRROR_DBM_MUTEX: The world of Redo Operations

There are times when you just run into something and you can deem less important and continue. And there are times, where you have to stop and deal with it. In the past few weeks this wait type has simply come up way too many times. First, there is a great empirical blog post by Joe…

0

DbMgrPartnerCommitPolicy::SetSyncState May Seem More Mysterious than It Actually Is

AlwaysOn has become a major SQL Server technology. In my own process of learning it, I have often wondered what this mysterious message means. DbMgrPartnerCommitPolicy::SetSyncState: 00000026BD96D330:4 Decoding it may help others out there settle the fear of the unknown. 1. This message indicates the Synchronization state of an AlwaysOn partner: thus the ”  DbMgrPartnerCommitPolicy::SetSyncState” 2. The…

0