Memorizing DBCC commands (or any command for that matter) can be difficult if you don’t use it often. Thank goodness for the folks at Microsoft for creating the DBCC HELP command. Below is a quick way to use DBCC HELP to figure out any command (with syntax) that might have slipped your mind. Continue reading “How to use DBCC HELP”
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”
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’.
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!
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.
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 sdb