The default layout in SSMS is too restrictive. I’m always having to scroll up and down and it gets pretty annoying. So I made a simple settings change that gives me more “real estate.” Continue reading “Gain More Real Estate in SQL Server Management Studio”
Recently, I wanted to resize the tempdb file size to 2 MB so I ran the below script:
ALTER DATABASE tempdb MODIFY FILE (name = tempdb, size = 2MB);
and got the below error:
Msg 5041, Level 16, State 2, Line 1
MODIFY FILE failed. File ‘tempdb’ does not exist. (screenshot below)
If you want to find the last time your SQL Server was restarted, the quickest way is to query the sys.dm_os_sys_info DMV for the ‘sqlserver_start_time’ column (See snapshot below)
Before I do any testing I like to execute the sp_cycle_errorlog stored procedure. That way I can quickly see the specific SQL Server log entry without having to scroll / filter through tons of log. Continue reading “How To Use sp_cycle_errorlog”
I have enabled TDE (Transparent Data Encryption) on almost all of my production SQL Server database servers. Yesterday I was approached by the IA (Information Assurance) team and they wanted to know what was the encryption level (key length and algorithm) of one of the database servers. Continue reading “How to Check SQL Server Database Encryption Algorithm”
During a recent “interview” I was asked, “What two isolation levels in SQL Server will prevent phantom reads?”
I had never heard of “phantom reads” before but thought the person meant, “dirty reads.” So I replied, “READ COMMITTED and SNAPSHOT isolation levels.” Continue reading “Dirty Reads vs Phantom Reads in SQL Server”
This morning at work I created a whole bunch of test tables with dummy data in them. After inserting all that data I realized I created the table with the wrong name. So instead of dropping and recreating everything, I used the “sp_rename” stored procedure. The syntax is as follows:
exec sp_rename ‘old Table name’, ‘new Table name’
It’s pretty simple and easy to execute.
One thing to note according to Microsoft (see image below)
I had an incident at work where I had to analyze the SQL Server error log. I was looking for a specific piece of information and so I thought I could just open the error log in notepad and find what I was looking for with the good old “CTRL+F.” That just took longer due to the confusing format. Below is a better and faster way. Continue reading “How to Read SQL Server Error Log Using sp_readerrorlog”
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”
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”
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?”
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:
…but that didn’t bring back anything. So then I ran:
…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.
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”
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”
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”