This blog post is meant to help distinguish the differences between REORGANIZE and REBUILD when it comes to index fragmentation. If you are serious about learning more of the internals of how indexing works in SQL Server, watch Kimberly Tripp’s (blog | twitter) video called, “Index Internals” here (open link and scroll down to “Index Internals”). Continue reading “INDEX REORGANIZE vs REBUILD in SQL Server”
Most recently I had to figure out the edition (Enterprise, Standard, etc.) of SQL Server running on a specific production database server. The client wanted to know to what extent was the database server STIG’d. Continue reading “How to Find SQL Server Edition Information”
For the longest time I’ve had a hard time remembering the difference between SQL Server DDL vs DML statements. I had a hard time remembering what statement fell under what category. Was INSERT, UPDATE, DELETE a DML command or DDL? What about CREATE / ALTER? It was all confusing to me. Continue reading “SQL Server DDL vs DML”
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’.
What 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”
I attended my very first SQL Server User Group meeting last night and it was awesome! I want to start by giving my friend Tim Radney a BIG thank you for telling me about the importance of attending these sessions!
I joined the Northern Virginia (NOVA) SQL User Group a few weeks ago and attended my very first session last night. The session was called, “Hands-On Lab- Migrating a SQL Server Database to a Microsoft Azure SQL Database” and it was an online lab. Continue reading “My First SQL Server User Group Session”
I was fortunate enough to attend IEPTO1 (Immersion Event on Performance Tuning and Optimization part 1) by Kimberly Tripp (twitter) and Paul Randal (twitter) of SQLSkills this past Spring. It was absolutely amazing! If you are a DBA, developer, or just an IT tech geek who loves to learn then you must attend this event. Find registration information here. Continue reading “My Review of Immersion Event on Performance Tuning and Optimization 1”
There were a few times where I had to delete/close an existing SQL Server Trace and create a new one. It’s a 2-step process to completely remove a SQL Server Trace. First, you stop the trace. Second, you delete/close it.
sp_trace_setstatus takes 2 parameters, trace id and status. Continue reading “How to Stop & Delete a SQL Server Trace”
I recently created a database called “testDB” and when I typed “USE testDB” in the SSMS Query window it gave me a little red squiggly underline (see below snapshot).Why did this happen when I just created a new database? That’s because the local IntelliSense cache hasn’t updated yet. Continue reading “How to Refresh IntelliSense Cache in SSMS”
I recently had to resize a data file on of my production SQL Server databases and needed to know an appropriate size to resize it to. So, I created this simple script that queries sys.database_files and brings back the File ID, File Location, File Name, Original Size, Space Used and Space Left.
SELECT sdb.file_id as [File ID], sdb.physical_name [File Location], sdb.name [File Name], CONVERT(numeric(10,2),ROUND(sdb.size/128.,2)) AS [Original Size in MB], CONVERT(numeric(10,2),ROUND(FILEPROPERTY(sdb.name, 'SpaceUsed')/128.,2)) AS [Space Used in MB], CONVERT(numeric(10,2),ROUND((sdb.size - FILEPROPERTY(sdb.name, 'SpaceUsed'))/128.,2)) AS [Space Left in MB] FROM sys.database_files sdbContinue reading “How to Find the Space Used in Your Data Files”
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”