Second file missing from Tempdb after move/restart

Posted on 2009-04-15
Last Modified: 2012-06-27
I created a second file for tempdb on a MSSQL 2005 box.  Later I needed to move the tempdb to a new disk.  I did a move and everything looked fine. (no errors).

After I restart, the server comes up fine but it does not have the second file for tempdb.

I recreated a second file and again the server seemed okay with that file.  after the server was reset however that file also disappeared.  

If I try to add the file back with the same name it says it already exists but when I view the properties of tempdb it says there is only one datafile.

I would like to know where tempdb stores the names of the second file and what can be done to "restore" access to them.  I would be okay with removing them, but I don't think it is a good idea to create new ones everytime.
Question by:neteeyore
  • 6
  • 5
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24150785
as tempdb is recreated on each start, all "additional files" are indeed "dropped" on restart.

Author Comment

ID: 24150870

This doesn't seem to match my understanding.  Perhaps I am miss stating what I am doing:

If I select the tempdb, right mouse click and choose properites. Then I click on files and add a new file.  This new database file will be "dropped" when I restart the machine?

When I say they disappear, I mean they are no longer visable in this properties window and they do not get recreated when SQL starts back up.  In addition if I try to recreate them in the files tab of the properties window it gives me an error that the file already exists.

LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24151157
>This new database file will be "dropped" when I restart the machine?
actually, by restarting sql server, yes, tempdb is recreated from scratch (actually, from model database, but retaining some information like location and size)

so, the file will actually still exist on disk, but is no longer attached to the tempdb (which explains the error message you get -> you would have to delete the physical file first)

you could create a auto-start job that re-adds the file again on restart (including a file delete file first...)

now, step back: why do you want to add a second file to tempdb?
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.


Author Comment

ID: 24153210
I know there are two schools of thought on the tempdb and second files.  Based on my research some say SQL 2005 does not need it, and some say 2005 may benefit from it.  I have a large tempdb and make use of it from several different programs.  I have had some performance degradations on the server and had been working to narrow them down.  

A number of months back I created the second file and it was working fine.  In fact it regularly was recreated whenever the server was restarted so that would be my first concern.  According to what you are saying that shouldn't happen.

Anyway, I realize there are a lot of threads on optimizing performance of SQL and the Tempdb in particular. so I don't wish to use your valuable time to try and resolve that issue.  

At this point I have made some other improvements on the server that seem to be helping and my tempdb (even with one file) seems to be fine.

My concern boils down to this.  It was not my intention to change the configuration back to a single tempdb data file.  while moving the database, the server seems to have deleted any reference to it in the config.  The file you refer to which would be left behind and therefore cause my error message was already deleted (manually by me) when I tried to re-add the second file.  My problem is I don't know why the server thinks it is there if it is not.  I cannot find any reference to this second file on SQL other than the error that says it already exists.

To be absolutely certain I have redone the following
1.  Viewed the directory where I am attempting to add the second file.  the only tempdb data file is tempdb.mdf  (temdb.ldf is there but of course it is the log file)
2.  right clicked tempdb in SMS and selected properties.
3.  selected the Files page
4.  chose "Add"
5.  Entered
      LogicalName = tempdev2
      FileType = Data
      FileGroup = Primary
      InitialSize = 4096
      AutoGrowth = 10% Unrestricted
      Path = F:\Data\SQL\Information
When I click ok I get
"DataFile '[tempdev2]' could not be added to the collection, because it already exists. (Microsoft.SqlServer.Smo)"

At this point that indicates to me that SQL thinks this file is there, but I do not see any indication of it anywhere ohter than this error.

Using the attached code I only see entries for the default data file and the log file.  Where else would it store information about datafiles for tempdb?

I would be ok with not building the second file, but the fact that I can't concerns me that it may become a bigger issue later.
USE tempdb
EXEC sp_helpfile

Open in new window

LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 450 total points
ID: 24158122
>Where else would it store information about datafiles for tempdb?
I don't know, sorry.

you might try to give another logical name ...

Author Comment

ID: 24159867
That works.  But as I said, when I did that, after the next reboot, that file disappears. and then has the same issue.  If I don't add one, everything runs fine, but this is a very strange error that I was hoping to not ignore.

Thanks for your thought Angelll
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24160302
apart from the problem, let me throw this info at you:

I don't know however why you are experiencing the "loss" of the data file.
is there anything in the sql ERRORLOG file that could give a hint about that?

Accepted Solution

neteeyore earned 0 total points
ID: 24184850
I have checked the SQL logs, the System Logs, the Application logs and I see nothing about this situation.   That is what is bothering me the most.  I have monitored the system for  a few days and with a single tempdb it is running fine, but again my concern is what surprises this may cause later.


Author Comment

ID: 24935959
After numerous months and finding nothing I stumbled onto the problem using a documenation tool we purchased from Red-Gate.  

The short answer is that when the tempdb was moved to a new disk, the second file was pointed to the same filename as the first file.  Surprising the system has continued to run without problems, but it is unable (as I mentioned above) to properly handle multiple files.

correcting the situation will require manual change of the sysaltfiles entry to make it point to a different filename. (I tried all Alterdatabase combinations but they didn't work).

Thought I would share this find in case others have the problem.

LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24936028
good finding!

Author Comment

ID: 24936301
A simpler solution was to rename tempdb's filename using alter and stop trying to alter the missing second file.

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit If you want to manage em…

790 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question