SQL Server Performance Forum – Threads Archive

TEMPDB – Move without a restart using EMPTYFILE?

So I searched the forums, read referenced articles, etc. and didn’t see this as a possible solution so I thought I’d ask the Gurus. I have 5 data files for my TEMPDB, 4 of which reside on a D drive at 1 gig each with no growth, 1 on a F drive at 4 gig with unlimited growth. I have an application that uses these files very heavily which is why I have the file on F. I was afraid there wasn’t enough room on D the last time I ran the process and had to create the file on F which I did before the system crashed (at that times the files on D could grow but I was running out of disk space in a hurry). Also F is a faster drive then D and is more fault tolerant (trying to get the hardware guys to address that issue!) So I want to get rid of the files on D and create some new ones on F to replace them (I understand it is a help to have a temp db file for each processor based on a article on MS I read sometime back). With other databases you can use DBCC SHRINKFILE with the EMPTYFILE option then drop the file. Can this be done with tempdb? I haven’t found any reference that you can or can’t – every article about moving tempdb states to do the alters and restart. Sure, I can do that when I have an opportunity to do so (this box is used for other processes so I can’t do it on a whim) but I’m curious if this works? Anyone ever tried it? Thanks, Dave OS Windows 2k, SQL 2000 SE SP3

hi Dave, I think this link will help you out http://www.databasejournal.com/features/mssql/article.php/3379901 -Johnson

Johnson, Yes, I read that article but that solution again requires that you restart SQL server to complete the move. As does every article I have been able to locate and BOL that discusses how to move tempdb. The question I have is can you move tempdb without restarting SQL server using DBCC SHRINKFILE with the EMPTYFILE parameter and then droping the file you emptied as long as you have one or more files that you didn’t empty. Just as you can with a non-system database as described in MS KB Article 814576 http://support.microsoft.com/default.aspx?scid=kb;en-us;814576 Dave Schlieder

TEMPDB is a system database and in order to MODIFY any of the system database, SQL Server services must be restarted and without that there will not be affected. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Satya, Not trying to beat a dead horse but.. "TEMPDB is a system database and in order to MODIFY any of the system database, SQL Server services must be restarted and without that there will not be affected." To a certain extent, I have to disagree with that statement. You can add data files to a system database and you do not need to restart SQL in order for SQL to recognize and use those new files. I have done this many times with tempdb on my servers and have never needed to restart SQL for those files to be used by SQL Server. As I stated, I added a file to tempdb while it was being heavily used and SQL recognized it as soon as the file was created. Granted, if tempdb only has a single file and you want to move it, then the logical process is to do what is in every article, alter and restart and SQL will rebuild the file where you want it to. But in my scenario I have more than one file. So I still ask my original question, has anyone ever tried to do this? Added: Since it seems no one has attempted this and I had a moment, I did – it doesn’t work, you can’t delete the primary file. I could do this with other files I had added but not the original file. It was just a curious question. Regards, Dave Schlieder

Adding and moving files (for any db) are different operations and each have different requirements. You will have to stop & restart the services.

Dave I’m not diverting the issue (may be I should have stated if you delete ….) and in order to take affect of new settings (in your case) deleting a file from TEMPDB requires a restart of SQL SErver services. We do this many times on the development box and in Production always allocate optimum size on TEMPDB. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>