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

Windows Internal Database (WID) is Not Destined as a Regular SQL Server

If you ever considered using the Windows Internal Database (WID) as a regular SQL Server service, consider the following: “Windows Internal Database is a relational data store used by the following applications and services. Active Directory Rights Management Services (AD RMS) Windows Server Update Services Windows System Resource Manager ” It is not supported or intended…

1

How to create a Deadlocked Scheduler Scenario at will

  First, please do not try this on your production server, because it will actually work. Second, this example is to illustrate how Deadlock scheduler issues can and do occur with the goal of increasing your understanding of the problem. Over the years of working with SQL Server, I learned that to understand a technology…

3

Meditation: Replicate Data from Multiple Instances into a Single Reporting Database

  Somebody approached me recently with a question like this: Question: We’ll be hosting our existing application in one data center, but we’ll be setting up 2 more instances in two different data centers. Is possible to use replication to move all the data into an single database so that we can report on all…

0