Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I recently moved my Temp database files onto a separate drive (I had two data files and one log file).

However, after restarting the server, my second data file disappeared both from the file list on the gui and from sp_helpfile. I've managed to reproduce this behaviour several times: I add a new data file, see it appear both in the gui and sp_helpfile, and disappear after rebooting the server.

I've run dbcc checkdb against the database and it returns no errors. There's no information either in the SQL logs or the windows application event log.

After doing a little more research, I ran the following query:

select d.name, m.* from sys.master_files m
inner join sys.databases d 
on (m.database_id = d.database_id)
where d.name like 'tempdb'
order by 1, 2

Results: http://pastebin.com/Zu5fJ2hU

It shows four different secondary data files, none of which appear in sp_helpfile. If I try to use any of those names SQL server doesn't allow me to. None of these files physically exist on the disk. My server is SQL 2012 Standard.

share|improve this question
    
What edition are you using? How exactly are you adding files to tempdb? –  Aaron Bertrand Feb 22 at 19:08
    
@AaronBertrand Sql server 2012 (as tagged :)). I'm adding them through the gui by clicking "add" on the files page of the database properties window. I've done it this way in the past many times with no issues. –  Mansfield Feb 22 at 20:14
    
That's a version, not an edition. Are you using Express, for example? –  Aaron Bertrand Feb 22 at 20:34
    
@AaronBertrand Oops, my mistake. Standard edition. –  Mansfield Feb 22 at 20:42
1  
@JonSeigel I've reproduced this behaviour: Creating a new database does not give the service accounts any permission on the MDF/LDF files. Not sure if this is expected behaviour, or problematic? –  Mansfield Feb 24 at 22:31
show 8 more comments

1 Answer 1

up vote 4 down vote accepted
+50

(Consolidating the comments into an answer, so it's more easily consumable by searchers.)

It appears this is related to not granting the appropriate permissions to the data container when moving the location of the physical files. Without having tried it, I believe this would also apply to user databases, not just tempdb.

I have an old, but still technically accurate, video on how to move all the system databases (not just tempdb) here, which includes showing the process of assigning permissions to the new data container. The database engine requires Full Control on This Folder, Subfolders, and Files starting at the data container root.

After the permissions are set correctly on the data container, the dangling files should be removable from the system catalogs using ALTER DATABASE ... REMOVE FILE ....

share|improve this answer
    
Thanks for posting! I'll reboot the server this weekend, and if everything continues to be fine I'll mark as answer/award the bounty. –  Mansfield Feb 26 at 18:00
    
@Mansfield: Thanks! I'm glad the solution was relatively simple. :) –  Jon Seigel Feb 26 at 18:03
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.