BEST SQL Server Logging, Recovery, & Transaction Log Course

There is a great comprehensive training video series by Paul Randal called, “SQL Server Logging, Recovery and the Transaction Log.” This is a Pluralsight 7.5 hour brain dump of everything Paul Randal knows about logging, recovery and transaction log.

If you are a SQL Server DBA, or a tech geek that loves how SQL Server internals work, then you must check out this video training series (see link below).

https://www.pluralsight.com/courses/sqlserver-logging

I personally went through this course twice and still feel like I need to go through it again. It’s definitely the best course material on the topic by one of the best in the industry.

My advice for anyone starting out: take your time, take notes, take breaks, relax….enjoy!

 

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?

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”