Solved

Second file missing from Tempdb after move/restart

Posted on 2009-04-15
11
826 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
[X]
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
11 Comments
 
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.
0
 

Author Comment

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

0
 
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?
0
Turn Insights Into Action

You’ve already invested in ITSM tools, chat applications, automation utilities, and more. Fortify these solutions with intelligent communications so you can drive business processes forward.

With xMatters, you'll never miss a beat.

 

Author Comment

by:neteeyore
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
GO
EXEC sp_helpfile
GO

Open in new window

0
 
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 ...
0
 

Author Comment

by:neteeyore
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
0
 
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?
0
 

Accepted Solution

by:
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.

0
 

Author Comment

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

0
 
LVL 143

Expert Comment

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

Author Comment

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

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

695 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