Solved

Second file missing from Tempdb after move/restart

Posted on 2009-04-15
11
806 Views
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.
0
Comment
Question by:neteeyore
  • 6
  • 5
11 Comments
 
LVL 142

Expert Comment

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

Author Comment

by:neteeyore
Comment Utility

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.

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
>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?
0
 

Author Comment

by:neteeyore
Comment Utility
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

GO

EXEC sp_helpfile

GO

Open in new window

0
 
LVL 142

Assisted Solution

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

you might try to give another logical name ...
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

Author Comment

by:neteeyore
Comment Utility
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
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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?
0
 

Accepted Solution

by:
neteeyore earned 0 total points
Comment Utility
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.

0
 

Author Comment

by:neteeyore
Comment Utility
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.

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
good finding!
0
 

Author Comment

by:neteeyore
Comment Utility
A simpler solution was to rename tempdb's filename using alter and stop trying to alter the missing second file.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

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…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

772 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

10 Experts available now in Live!

Get 1:1 Help Now