How to Stop & Delete a SQL Server Trace

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.

The trace id is the id you find by doing a select * from sys.traces

The status is either a 0, 1 or 2:

0 stops the trace, 1 starts the trace, 2 closes the specified trace and deletes its definition from the server. (see MSDN image screenshot below)

Status for sp_trace_setstatus(According to Microsoft, “A trace must be stopped first before it can be closed.”)

Let’s walk through the process.

First, do a select * from sys.traces to get the trace ID. Second, stop the trace by executing sp_trace_setstatus trace_id, 0 then, close/delete the trace by executing sp_trace_setstatus trace_id, 2. (see below screenshot for example)

sp_trace_setstatus screenshot

Hope that helps!

How to Refresh IntelliSense Cache in SSMS

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).use database red errorWhy did this happen when I just created a new database? That’s because the local IntelliSense cache hasn’t updated yet.

How to Refresh IntelliSense Cache in SSMS

If you want to force the IntelliSense Cache to update, go to Edit, then IntelliSense, then click “Refresh Local Cache.” (See snapshot below)

refresh ssms intellisense cache

That should refresh your IntelliSense Cache and remove all those annoying red squiggly underlines!

How to Find the Space Used in Your Data Files

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

Here is a sample result of the output:

SCRIPT-Space-Used-vs-Space-Left-Datafiles

 

Gain More Real Estate in SQL Server Management Studio

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.”

The configuration for this is quite simple:¬† Go to Tools, then click on Option, expand Query Results, expand SQL Server, click on Results to Grid and make sure the “Display results in a separate tab” is checked. (see image below)

You might have to restart SSMS or open another session for the new layout to take effect. Enjoy!

gain more real estate in sql server management studio

Msg 5041, Level 16, State 1, Line 1 – Resize Datafile

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);
GO

and got the below error:

Msg 5041, Level 16, State 2, Line 1
MODIFY FILE failed. File¬†‘tempdb’ does not exist. (screenshot below)

Msg 5041, Level 16, State 2, Line 1

After some research I found out that the file name that I was using was incorrect. To find the correct logical name, you have to right-click the database, go to Properties —> Files and you’ll see it under Logical Name (see green arrow in screenshot image below). In my case it was “tempdev” NOT “tempdb” (which is what I used and got the error).

Msg 5041, Level 16, State 2, Line 1

Once I put that name in the script it ran perfectly fine. Make sure to restart SQL Service in order to see the change.

ALTER DATABASE tempdb MODIFY FILE (name = tempdev, size = 2MB);
GO