Generating a SQL Server, SSAS, SSIS, SSRS Memory Dump via PowerShell script (and SQLDumper.exe)

Recently, we published a PowerShell script to create memory dumps of SQL Server, SSAS, SSRS, and SSIS – called it SQLDumpHelper.ps1 . The PS script uses SQLDumper.exe and it makes it very easy to generate the dumps by avoiding having to look up SQLDumper.exe command switches. I wrote the PS script as an extension to…

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

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