3

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.

13
  • What edition are you using? How exactly are you adding files to tempdb? Feb 22, 2014 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, 2014 at 20:14
  • That's a version, not an edition. Are you using Express, for example? Feb 22, 2014 at 20:34
  • @AaronBertrand Oops, my mistake. Standard edition.
    – Mansfield
    Feb 22, 2014 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, 2014 at 22:31

1 Answer 1

5
+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 ....

2
  • 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, 2014 at 18:00
  • @Mansfield: Thanks! I'm glad the solution was relatively simple. :)
    – Jon Seigel
    Feb 26, 2014 at 18:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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