An internal error happened while generating a new DBTS for database ‘Db1’

Recently I worked on an issue that encountered this error and I had to research the source code to figure it out. Msg 42029, Level 16, State 1, Line 3 An internal error happened while generating a new DBTS for database “Db1”. Please retry the operation This error has to do with the Database Timestamp…

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

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

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 Unique Columns when Using Table Partitioning

Recently, I had the opportunity to present table and index partitioning and a follow up question came up regarding partitioning a table with unique constraint/index. The Special Guidelines for Partitioned Indexes article describes the right approach   “Partitioning Unique Indexes   When partitioning a unique index (clustered or nonclustered), the partitioning column must be chosen…

4