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 142

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 142

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?
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.


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 142

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 142

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 142

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

813 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now