[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now


Second file missing from Tempdb after move/restart

Posted on 2009-04-15
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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?
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.


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 1800 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: http://support.microsoft.com/kb/328551

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

656 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