2

I'm on a SQL Server 2008 R2 box.

According to SQL Server, my TempDB looks like this:

CREATE DATABASE [tempdb] ON  PRIMARY 
( NAME = N'tempdev', FILENAME = N'D:\Program Files\Microsoft SQL
 Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\tempdb.mdf' , SIZE = 6499584KB , MAXSIZE = 
81920000KB , FILEGROWTH = 10%)

LOG ON 

( NAME = N'templog', FILENAME = N'T:\Program Files\Microsoft SQL 
Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\templog.ldf' , SIZE = 512KB , MAXSIZE = 
UNLIMITED, FILEGROWTH = 10%)

GO

Which is fine... Except I have 2 Tempdb files: Tempdb.mdf and Tempdb2.mdf.

I can't find it anywhere in sys.masterdatafiles, I can't delete it in windows -- because it says Windows is using it, and when the first tempdb file grows, it does too, so I know SQL Server is using it.

How can I find this file in SQL Server so I can remove it from use?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Dave Clary
  • 55
  • 1
  • 7
  • 1) Why do you want to have only 1 data file? 2) How do you know there are 2 data files? 3) What does it say in tempdb.sys.database_files? – Robert L Davis Jan 06 '13 at 20:33
  • If this is a recent change, then restarting the SQL Service should either update the metadata to make it current or release its lock on the file. – Robert L Davis Jan 06 '13 at 20:58
  • Do you have a **second instance** of SQL Server on that same machine? – marc_s Jan 06 '13 at 21:20
  • Robert, I'd like to have more than one data file -- but I have to figure out the mystery of the second file, I think, before I add more files... BTW... 16 cores, but only 3 drives available to me... so 16 temp files (one per core), or 3 (one per drive?)? I know there's 2 data files because I can see them in Windows explorer. – Dave Clary Jan 06 '13 at 21:23
  • select file_id, name, left(physical_name,3) + '..' + RIGHT(physical_Name, 16) as physical_name from tempdb.sys.database_files

    results:

    file_id name physical_name 1 tempdev D:\..\Data\tempdb.mdf 2 templog T:\..Data\templog.ldf Mysterious D:\..\Data\tempdb2.mdf not showing. Therein the dilemma.     – Dave Clary Jan 06 '13 at 21:27
  • Sorry, still new to stackoverflow as a poster. Can't figure out formatting the responses. :/ – Dave Clary Jan 06 '13 at 21:30
  • @marc_s -- No, only one instance of SQL Server. There is an SSRS Instance, but its data files are accounted for -- unless SSRS has a tempdb too, but I don't think that's the case, is it? – Dave Clary Jan 06 '13 at 21:30
  • Did you try restarting the SQL service? – Robert L Davis Jan 06 '13 at 23:43
  • You don't need to partition the tempdb files to spearate drives (unless you are bottlenecked on disk IO). The reason for multiple files is so that there are multiple allocation pages being used, not to spread out the IO. – Robert L Davis Jan 06 '13 at 23:49
  • For more guidance on tempdb files, I recommend checking out my whitepaper ( https://www.idera.com/Action/RegisterWP.aspx?WPID=43 ) and/or my webcast ( https://www.idera.com/Events/RegisterWC.aspx?EventID=208 ) on tempdb performance and manageability. – Robert L Davis Jan 06 '13 at 23:55

1 Answers1

2

Problem solved:

To answer question, yes, I had stopped and restarted service -- no apparent change to sys.master_files, or anything else.

I decided I'd try to go ahead and add the new tempdb files anyway.

Went into SSMS, added a tempdb2, and tempdb3, hit OK....

and SQL came back and told me that tempdb2 was already in the collection.

WTF, I asked myself, without abbreviating, and tried 

SELECT * 
FROM sys.master_files
WHERE DB_NAME(database_id) = 'tempdb'

again...

And there was the little bugger, all peaceful and happy-like.

A refresh of the tempDB object in SSMS, right clicking properties, and there was the file in the files list, ready to be manipulated.

So very, very strange. Thanks for helping me sort it out.

Dave Clary
  • 55
  • 1
  • 7