Can't find owner of tempdb.ndf files 2

[KirbyWallace]

I have 6 tempdb_N.ndf files in a folder. We know they are in use (can't rename them, and task manager shows them in use by SQLSERVR.EXE).

The problem is, we can't find which database owns them. I've looked at the filegroups for all the databases on the server, and none of them claim to be using these tempdb files.

Anyone have any idea how we can identify the owner of these guys? We need to move them as they got erroneously setup on our temp/swap drive.

 

[gmmastros]

Based on the name, I would suggest that TempDB is using the files. You can check this by running this in a query window:

exec tempdb.dbo.sp_helpfile

This will list the files that the database is using, including the fill path.

Please be aware that there could be multiple instances of SQL Server running on the computer, so you should check them all. To get a list of instances on the computer...

At a Command Prompt:
sqlcmd -L

(the L must be upper case).

Also....
Click Start -> Run
Type: services.msc
scroll down to SQL Server

There can only be one default instance of sql server installed on a computer, but there can be multiple named instances. On my computer, I see this:

[tt]
SQL Server
SQL Server (SQL2005)
SQL Server (SQL2008)
SQL Server (SQLEXPRESS)
[/tt]

I have SQL server installed 4 times on my computer. If you find multiple instances installed on yours, you'll want to run the query at the top of this post on each of the instances until you find the one using those files.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[KirbyWallace]

BTW - I have dumped the contents of sys.master_files and there is no mention of these .NDF files there.

 

[KirbyWallace]

@GMMASTROS

Bingo! That does show these files in use by Tempdb.

I guess now I am wondering why when I open tempdb properties in SSMS and choose "FILES" (between "General" and "FileGroups"), they are not listed THERE? Bizarre! I guess this is one good example of the fact that you cannot do everything in SSMS, and a good DBA needs to know how to script his way around the server.


Now, I need to figure out how to tell SQL Server to stop using them so I can delete them, or move them to another location. I kinda think I don't need them. There are NINE data files involved in tempdb, and two log file segments. I don't think I need that many.

 

[KirbyWallace]

Here's my output:

tempdev	1	C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\tempdb.mdf	PRIMARY	8192 KB	Unlimited	0 KB	data only
templog	2	C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\templog.ldf	NULL	1024 KB	Unlimited	0 KB	log only
tempdb1e	3	e:\database\tempdb1e.ndf	PRIMARY	8192000 KB	Unlimited	0 KB	data only
tempdblog	4	e:\database\tempdblog.ldf	NULL	20480000 KB	2147483648 KB	0 KB	log only
tempdb2	5	F:\Database\Tempdb\tempdb_2.ndf	PRIMARY	8192000 KB	Unlimited	0 KB	data only
tempdb3	7	F:\Database\Tempdb\tempdb3.ndf	PRIMARY	8192000 KB	Unlimited	0 KB	data only
tempdb4	8	F:\Database\Tempdb\tempdb4.ndf	PRIMARY	8192000 KB	Unlimited	0 KB	data only
tempdb5	9	F:\Database\Tempdb\tempdb5.ndf	PRIMARY	8192000 KB	Unlimited	0 KB	data only
tempdb2e	10	E:\database\tempdb2e.ndf	PRIMARY	8192000 KB	Unlimited	0 KB	data only
tempdb3e	11	e:\database\tempdb3e.ndf	PRIMARY	8192000 KB	Unlimited	0 KB	data only
tempdb4e	12	e:\database\tempdb4e.ndf	PRIMARY	8192000 KB	Unlimited	0 KB	data only

The file parts on DRIVE F are the ones that do not show up in the SSMS database properties FILES dialog. I'm pretty certain F:\ cannot be a network drive because SQL cannot use network mapped drives - it requires local drives, if I am not mistaken. But F:\ may be a map to a local drive.

At this point, I am pretty certain, too, that we do not need this much tempdb space. So, now I need to figure out how to remove files (specifically, the ones mapped to F:\) from participating in this tempdb.

I'd wager that this will require stopping the server while this takes place. Yes? And do you know how to do this?

Thanks!

 

[SQLBill]

If you want to get rid of those files, check out the BOL for DBCC SHRINKFILE. You want to use the EMPTYFILE option.

First you will use that option which will empty the file. Then use ALTER DATABASE to remove the file.

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/e02b2318-bee9-4d84-a61f-2fddcf268c9f.htm

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[gmmastros]

I don't think I need that many.

Without A LOT more information, we cannot tell you if you need that many either. I can tell you this...

When SQL Server writes data to a table, several things happen (and they try to happen as quickly as possible). Suppose you update data in a table. SQL Server will write data to a log file (indicating that an update is about to occur). Then SQL Server will update the data, and finally, SQL Server writes to the log file that the update is complete. These things need to happen sequentially in case the sql server box goes poof (for example a power outage). When your data file and log file are on the same physical disk, the hardware must spin the disk and position the read/write head to the location of the log file, write the data, reposition to the data file, write the data, reposition to the log file again, and write some more data. That's a lot of disk activity for such a simple operation. When your data and log file are on separate disks, there is less hardware activity and therefore better performance.

Similar things happen with the TempDB. In your T-SQL code, whenever you use a temp table and/or table variable, stuff gets written to TempDB. If TempDB is on the same physical disk as your user table, you will have more disk activity than if you have TempDB on a separate disk.

Also, since most servers have multiple CPU's you can have concurrent queries running, so having multiple files for your TempDB can speed things up. By putting these multiple files on separate disks, you will certainly speed up certain queries with the database.

Do you need that many files? I don't know, but you should be aware that removing them will likely slow things down. The amount of decreased performance may not be noticeable, but then again... it might.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom