CODEWITHSUNDEEP
sql-server-2008-r2tempdb
Dave Clary
Dave Clary

Reputation: 55

TempDB has 2 data files, but SQL Server doesn't know it?

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?

Upvotes: 2

Views: 4549

Answers (1)

Dave Clary
Dave Clary

Reputation: 55

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.

Upvotes: 2

Related Questions