If you work for the government and have to create a SQL Server Trace that monitors all the trace event IDs that come in the DISA STIGs then you have come to right place! (I feel like I’m hosting an infomercial) Continue reading “How to Create a DISA STIG SQL Server Trace”
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.
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.
Setup SQL Email Alert for Disk Space Usage
I had an application go kaput on me all of a sudden and that wasn’t good. I had gotten back from lunch (always happens when I get back from lunch) and was immediately approached by the Sys Admin saying that a certain web application couldn’t connect to the SQL database. He wanted me to check out why and get back to him ASAP. Continue reading “Setup SQL Email Alert for Disk Space Usage”
Calculate Database Compression Ratio
Here’s a quick and easy formula to help show upper management how much your backup compression strategy is saving the company.
Let’s assume your original non-compressed SQL backup file is 100MB. After you enabled compression your compressed backup size is now 5MB. Plug in the numbers below and you get: Continue reading “Calculate Database Compression Ratio”
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”
SQL Server Email Alerts Setup
Recently at work we had an issue where a SQL login account’s password expired and the application that uses that login stopped working. The Window’s password policy for our organization requires passwords to expire after 60 days. Continue reading “SQL Server Email Alerts Setup”
With All Due Respect…
Recently at work, I came back from lunch to my coworkers yelling,
“We’ve been looking for you! You need to restore the database!”
I was extremely surprised and caught off-guard by the verbal attack. I tried my best to keep my cool, even though my heart was racing fast, and asked for further explanation as to why I should “restore the database?”
They replied, “We can’t connect to our application!” Continue reading “With All Due Respect…”
People You Meet Along The Way
There are always people you come across in life that stand out from others. They are the ones that end up becoming life long friends.
In terms of my professional career, there are two people that I credit for being a DBA and starting this blog. Continue reading “People You Meet Along The Way”
Blogging Baby Steps
Looking back, I don’t remember a time that I ever liked to write. I was the kid in school that always took forever to write a paper because it was “never good enough.”
With some encouragement from my friend Tim Radney, I decided to take the plunge and travel down the road of blogging. I do work full time as a SQL Server DBA so most of my posts will be centered around things I learn everyday at work.
Like the title of this blog says, I’m starting out with “baby steps” but hopefully I’ll be up and running very soon!