How To Install SQL Server 2019 on Linux (CentOS)

Everyone is talking about Linux. SQL on Linux? Wow! I think it’s about time I start to dive into it. If you’re like me, then the only thing you know about Linux is that you know it’s an OS. That’s it. I have never installed/configured it let alone installing/configuring an app on it.

Then came along SQL Server 2017 (and now 2019), and the ability to run it on Linux. That was really eye-opening to me. Microsoft definitely loves Linux!

Microsoft and Linux

This post will go into how to install Linux (CentOS) and SQL Server 2019.

Continue reading “How To Install SQL Server 2019 on Linux (CentOS)”

applicationintent=readonly in Azure Data Studio

Recently, I had to use Azure Data Studio to access a application intent read only secondary replica. I had to use Azure Data Studio because I was using a Mac. I usually use SSMS on my Windows machines. If you want to connect with the “applicationintent=readonly” property via SQL Server Management Studio, you do so by typing it out in the “Additional Connection Parameters” as shown in the screenshot below:

Continue reading “applicationintent=readonly in Azure Data Studio”

What’s Msg 208, Level 16, State 1, Line 1

It’s usually something simple that’s overlooked that ends up causing the biggest troubleshooting headache. Let me explain. I was creating a test database called “testDB” with a test table called “testTable” (yes I know, I put a lot of thought in the naming of these objects) and when I tried to insert data into this new testTable I got the following error:

Msg 208, Level 16, State 1, Line 1 Invalid object name ‘testTable’.

Msg 208, Level 16, State 1, Line 1What the hec? How can it be an invalid object? I just created it! Continue reading “What’s Msg 208, Level 16, State 1, Line 1”

How To Find All Tables, Columns, Data Types of SQL Server Database

Recently I had to find all the tables, columns, data types, etc. from a database. Below is a thorough script that brings back all the tables, attributes, data types, whether the column allows NULLS, whether it’s a Primary Key, or a Foreign Key (and if so, the referencing table). It’s extremely useful and easy to run. Continue reading “How To Find All Tables, Columns, Data Types of SQL Server Database”

How to Find Last Login Date of a SQL Server Login?

Feel free to watch the how-to video above or read below.

There are many options to find the last login date for a a SQL Server login. Even though there are awesome scripts like Adam Machanic’s “Who is Active” (download link here), sometimes you might find yourself without internet access, or perhaps at a client site that doesn’t have “Who is Active” installed and you forgot your thumb drive at home. :) Continue reading “How to Find Last Login Date of a SQL Server Login?”

Find Rogue Transactions in SQL Server

One of the developers approached me today asking why their simple SELECT SQL query was taking forever. I walked over to their desk and noticed their SQL code had a BEGIN TRAN but no COMMIT or ROLLBACK. I ran a:

SELECT @@trancount

…but that didn’t bring back anything. So then I ran:

DBCC OPENTRAN

…and it returned an open transaction with its associated SPID.

How to find rogue transactions in SQL Server

I used the KILL command to kill SPID 57 (Kill 57) and the developer’s query returned instantly.

And just in case you were wondering, the cause of the rogue transaction was a BEGIN statement that the developer ran without a COMMIT or ROLLBACK and the developer tried to access that same table in another session window.

SQL Server Database Instance-Level Backup Compression Setup

I was fortunate enough to attend Paul Randal’s and Kimberly Tripp’s IETPO1 this past Spring. During the week long training I met Tim Radney (he’s a SQL Consultant at SQLSkills). I approached him, introduced myself and as we were talking, the subject of SQL Server backups came up. I explained my work’s current backup strategy and how I’d like to make it more efficient, both in speed and disk space. Tim suggested I enable the instance-wide backup compression option in SQL Server Management Studio (see image below) Continue reading “SQL Server Database Instance-Level Backup Compression Setup”