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 always on 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…

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

Procedure Execution with Table-Valued Parameters Involved Cannot be Replicated

  Somebody approached me with this error, asking whether this is by design or caused by certain conditions. Msg 25023, Level 16, State 1, Server <removed name>, Procedure sp_MSaddexecarticle, Line 192 Stored procedures containing table-value parameters cannot be published as ‘[serializable] proc exec’ articles.   Step 1: The first thing I wanted to do was…

0

Finding Large Transactions that Bloat Your Transaction Log

  I know this is a fairly common problem and there might be multiple solutions out there, but I figured adding another one may not hurt. An application I was supporting recently, was leading to 100s of GB of transaction log growth in spurts and we needed to discover what was causing the growth. I…

4

Dealing with Diacritics in Your Data

  Question: My customer wants to store data other than English in his table, using the Latin alphabet. For example, he has Spanish, German, Portuguese and English data. He wants to be able to return all the variations of the same character with one query, regardless of whether it has a diacritic mark (accent). For…

0