Connectivity Problems Caused by Issues in SQL Server

Very frequently connectivity problems to SQL Server are issues originating from network-related or authentication issues. Here are examples of connectivity timeout errors: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. System.Data.SqlClient.SqlException (0x80131904): Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login…

0

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

How to install Sql Server Data Tools (SSDT) 2017 Offline (Internal Network installation)

Here is a request that came up recently: “When installing SSDT manually it downloads a shell copy of Visual Studio 2017 from the Internet. However, if I get the offline copy of SSDT for VS2017 it does not include the Shell copy of VS2017 so the install fails because no Visual Studio 2017 instance is…

0

Deadlock Simulator app for Developers: How to Handle a SQL Deadlock issue in Your App

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