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.
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:
Now look at the results IT produces:
And there, on the F: drive are my mysterious tempdb files.
Just so you know. Sometimes SSMS doesn’t tell you everything.