SSMS Doesn’t Show ALL FILES in Properties Dialog

You often hear people say that SSMS is pretty good, but you still have to script some things. Here’s one of those things.

I had a problem where I found (just laying about the place) some Data and Log files. It was pretty clear from their name that they belonged to some TEMPDB, but since you can name a file anything, that’s no gaurantee.

Anyway, I knew they were in use (can’t rename them, and task manager shows them in use by SQLServr.EXE). But for the life of me, I could not figure out WHICH database they were a part of. I looked first at the obvious candidate, tempdb, but according to the Files tab on the Database Properties Dialog in SSMS, they didn’t belong to it.

SSMS says these are the only files involved in the tempdb!

I even went so far as to step through all the remaining databases on this server, one by one, checking each. No luck. These files don’t seem to belong to any db here.

Enter script, stage right.

In a query window, run the following:

exec tempdb.dbo.sp_helpfile

Now look at the results IT produces:

SSMS says these are the only files involved in the tempdb!

And there, on the F: drive are my mysterious tempdb files.

Just so you know. Sometimes SSMS doesn’t tell you everything.


About combatdba

I'm a production DBA at a terabyte-class SQL Server Shop
This entry was posted in S.W.A.T.. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s