Every time I have to do this, I Google for a script. I might as well write my own and put it here so at least I find myself in the Google results:
|
1 2 3 4 5 6 |
SELECT 'ALTER DATABASE tempdb MODIFY FILE (NAME = [' + f.name + '],' + ' FILENAME = ''Z:\MSSQL\DATA\' + f.name + CASE WHEN f.type = 1 THEN '.ldf' ELSE '.mdf' END + ''');' FROM sys.master_files f WHERE f.database_id = DB_ID(N'tempdb'); |
The results:
I can then copy/paste the results into SSMS, edit them, and run ’em. I like generating scripts like this rather than flat-out executing it automatically because sometimes I need to tweak specific file locations.
I’m not bothering with detecting what the file names were before, and I’m just generating new file names to match the database object’s name instead.
Erik says: See that path? Make sure you change that path to the right one. Preferably one that exists, and that SQL Server has permissions to. If you don’t, your server won’t start up unless you Google “how to start SQL Server without tempdb” and spend the next morning explaining to your boss why you should keep your job. I mean, I’ve heard stories…
Want to learn more? Take my class.
My Fundamentals of TempDB class is for curious folks who want to learn:
- What uses TempDB, and how that affects performance: not just temp tables and table variables, but also triggers, cursors, sorting indexes, workspace spills, the version store, and more
- How to host TempDB depending on how you use it: understanding what goes in the log file, the data files, why the number of data files matter, and whether your particular workload should be on SAN, local storage, or ephemeral storage, and why
- How to monitor it: after you understand what uses it and how to host it, you need to keep tabs on how its performance changes over time using DMVs, Perfmon counters, and the First Responder Kit scripts
52 Comments. Leave new
Erik; tell us more about those stories… ^_^
Haha haha, Erik I’ll like to hear your story….
hi,
in this blog https://blogs.technet.microsoft.com/dataplatforminsider/2014/09/25/using-ssds-in-azure-vms-to-store-sql-server-tempdb-and-buffer-pool-extensions/ they are talking about moving the tempdb to the temporary d-drive, but how is that working then when this tempdb got deleted?
Janjansen – if TempDB doesn’t exist on startup, SQL Server will create it as long as the folder exists. If you use ephemeral/local SSD in IaaS (like Azure VMs, Amazon EC2, or Google Compute Engine), you just need to make sure the TempDB folders are created on Windows startup before SQL Server starts.
That post just described a day in my life last week. And Erik’s post was the icing on the cake. Really annoying that SQL won’t let you refer to a drive that doesn’t exist when you’re also trying to change drive letters at the same time.
Moved a comma and added a Tab character, so the output rows line up with themselves, for that sweet sweet Alt-key block-selecting fun:
SELECT ‘ALTER DATABASE tempdb MODIFY FILE (NAME = [‘ + f.[name] + ‘]’
+ CHAR(9) /* Tab */
+ ‘,FILENAME = ”Z:\MSSQL\DATA\’ + f.[name]
+ CASE WHEN f.[type] = 1 /* Log */ THEN ‘.ldf’ ELSE ‘.mdf’ END
+ ”’);’
FROM sys.master_files f
WHERE f.database_id = DB_ID(N’tempdb’);
Yuck. The Internet formatted all the quotes so that SSMS gets super confused :’-(
OK, Brent’s answer script is now on DBA StackExchange, with some light additions. Feel free to help improve the answer there: https://dba.stackexchange.com/questions/191803/how-to-move-tempdb-files-to-a-different-drive-or-folder/
Bad Brent. You put your LDF and MDF on the same drives… 🙂 Just picking. I have constantly searched Google for these answers. In this job I finally created a Folder in my scripts folder of “Useful Tools” (also includes Adam Machanic, Ola’s, and your scripts as well).
Maybe it’s an age/cultural thing, but, rather than rely on Google… I just have a “toolbox” folder for scripts like this, so I don’t have to look one up every time I need it.
Although the “move tempdb.sql” file in my toolbox is dated 2009 and references a URL for KB 224071 that doesn’t seem to work anymore, so I guess there’s an argument for forcing one’s self to check for something more recent! (Does one still need to stop and restart SQL Server, as I noted in comments to myself? I haven’t had to move a tempdb in a long time, I guess…)
(Oops, I see Ted Locke at least beat me to saying this.)
Likewise, I used to have a folder in a network share where I used to keep handy SQL scripts.
But, when we were forced to work remotely, the shared folder on the LAN couldn’t be accessed directly. DBA team members had to first RDP to a jump box, then access the scripts from there!
I’ve found a neat alternative. ServiceNow has the option to create KB articles.
On an ongoing basis, I’m gradually transfering commonly-used script fragments into short KB articles that can be accessed anytime, anywhere.
This also makes the code snippets cross-platform. I can be copy and “paste as plain text” in Windows and Linux, without worrying about file formats.
[…] to move tempdb from one drive to another to free up space. So I googled to find a script and found this one by Brent Ozar. It has been added to StackExchange and refined a […]
I need to change the path and edit my .mdf and .ndf file name of temdb.
can anyone please help
Chary – read the post.
I can change the path but how can I change my tempdev.mdf to temp.mdf
is their any querry for that
Yes, Chary, the post. Read it. Carefully. Then do what it says. You can edit the script. If you’re not comfortable with that, then you shouldn’t be doing this task. Do not reply again.
Brent, in case you put a wrong unit, you can run a subst command… and after that, make corrections in a proper maintaining windows, it’s a fastest way.
I updated this script to allow you to change the file / folder locations at the top :
DECLARE @TempData varchar(2000) = ‘F:\MSSQL\DATA\’
, @TempLog varchar(2000) = ‘H:\MSSQL\DATA\’
SELECT ‘ALTER DATABASE tempdb MODIFY FILE (NAME = [‘ + f.name + ‘],’
+ CASE WHEN right(f.physical_name, 3) = ‘ldf’ THEN ‘ FILENAME = ”’ + @TempLog + f.name ELSE ‘ FILENAME = ”’ + @TempData + f.name END
+ CASE WHEN right(f.physical_name, 3) = ‘ldf’ THEN ‘.ldf’ WHEN right(f.physical_name, 3) = ‘ndf’ THEN ‘.ndf’ WHEN right(f.physical_name, 3) = ‘mdf’ THEN ‘.mdf’ END
+ ”’);’
FROM sys.master_files f
WHERE f.database_id = DB_ID(N’tempdb’);
You meant:
DECLARE @TempData varchar(2000) = ‘F:\MSSQL\DATA\’
, @TempLog varchar(2000) = ‘H:\MSSQL\DATA\’
SELECT ‘ALTER DATABASE tempdb MODIFY FILE (NAME = [‘ + f.name + ‘],’
+ CASE WHEN right(f.physical_name, 3) = ‘ldf’ THEN ‘ FILENAME = “‘ + @TempLog + f.name ELSE ‘ FILENAME = “‘ + @TempData + f.name END
+ CASE WHEN right(f.physical_name, 3) = ‘ldf’ THEN ‘.ldf’ WHEN right(f.physical_name, 3) = ‘ndf’ THEN ‘.ndf’ WHEN right(f.physical_name, 3) = ‘mdf’ THEN ‘.mdf’ END
+ ‘);’
FROM sys.master_files f
WHERE f.database_id = DB_ID(N’tempdb’);
Note that you still have to correct the quotation marks to the correct single and double quote instead of the left/right quotes.
Never mind. Still messed up.
DECLARE @TempData varchar(2000) = ‘F:\MSSQL\DATA\’
, @TempLog varchar(2000) = ‘H:\MSSQL\DATA\’
SELECT ‘ALTER DATABASE tempdb MODIFY FILE (NAME = [‘ + f.name + ‘],’
+ CASE WHEN right(f.physical_name, 3) = ‘ldf’ THEN ‘ FILENAME = ”’ + @TempLog + f.name ELSE ‘ FILENAME = ”’ + @TempData + f.name END
+ CASE WHEN right(f.physical_name, 3) = ‘ldf’ THEN ‘.ldf’ WHEN right(f.physical_name, 3) = ‘ndf’ THEN ‘.ndf’ WHEN right(f.physical_name, 3) = ‘mdf’ THEN ‘.mdf’ END
+ ”’);’
FROM sys.master_files f
WHERE f.database_id = DB_ID(N’tempdb’);
–Corrected the quotes. But probably still have the left/right quotes
[…] need an ALTER statement per file – if copy/paste isn’t fast enough, here’s a post by Brent Ozar to script out the […]
I think that the secondary files of the type rows data must have .ndf extension. So, this will be the correct query:
SELECT ‘ALTER DATABASE tempdb MODIFY FILE (NAME = [‘ + f.name + ‘],’
+ ‘ FILENAME = ”H:\TempDB\’ + f.name
+ CASE WHEN f.type = 1 THEN ‘.ldf’ ELSE ‘.mdf’ END
+ ”’);’
FROM sys.master_files f
WHERE f.database_id = DB_ID(N’tempdb’);
Please note that I only change de CASE statement.
No, you can name files anything you want, even MP3 or TXT.
OK. I will try it on my desktop SW. BTW, maeby I’m and old rules guy but, I preffer name the secondary files of the type rows data with the .ndf extension.
Anyway, thanks you Sr for the lesson.
JP
You’re welcome.
Thank you ,,,, as always the best !
Thanks Brent. Useful stuff.
Brent: This is probably a dumb question, but… “Is it viable to move TempDb to another drive by backing it up and restoring it, as though it were just another user database?” I’m guessing that the answer is along the lines of “No, because TempDb is itself needed to do a backup/restore, So, if you try to do it that way you’ll either get an error message or you’ll corrupt things massively.”
Blake – no, you can’t back up TempDB.
Brent: Thanks–that’s as I expected. sp_Blitz reports (among other things) “The tempdb database has files on the C drive…” That surprised me, because I knew that upon installation I specified location of tempdb on an alternate drive. So I checked and saw that tempdb.mdf and templog.ldf are on my F: drive (as intended). BUT: I’ve still got my 3 .ndf secondary data files on my C: drive–thus explaining sp_Blitz’ File Configuration warning. I see some chatter in the above comments about modifying your script to handle .ndf files, but I’m thinking that in my case it might be better to just move the .ndf files as follows:
First, run these statements:
ALTER DATABASE tempdb MODIFY FILE ( NAME = temp2 , FILENAME = ‘F:\MSSQLData\temp2’ )
ALTER DATABASE tempdb MODIFY FILE ( NAME = temp3 , FILENAME = ‘F:\MSSQLData\temp3’ )
ALTER DATABASE tempdb MODIFY FILE ( NAME = temp4 , FILENAME = ‘F:\MSSQLData\temp4′ )
Second, stop the instance (it’s not doing anything currently).
Third, physically move (i.e. copy/paste) the 3 .ndf files from their current C: location to the new F:\MSSQLData\ location
Fourth, restart the instance.
Fifth, check that it worked:
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N’tempdb’);
Does that look OK?
Blake – for free personal support, visit a Q&A site like https://dba.stackexchange.com. Thanks!
One powerful script, thanks man.
Your “Erik Says” part almost made me go away, but then I read it again. It was just a warning for remember to change the path. Ofcourse an IT man will do that.
Nice script! But I decided to modify this line:
‘ FILENAME = ”Z:\MSSQL\DATA\’ + f.name
f.name is the Logical Name, and I wanted to make it match the actual file name.
That way the original physical file name(s) can be maintained in the new directory.
so I substituted f.name with this:
reverse(left(reverse(m.physical_name), charindex(‘\’,reverse(m.physical_name), 1) – 1))
Thanks for that script, I’ve got dozens and dozens of files to move and this helped me a lot !
Regards…
Brent,
Is it possible to, besides moving the file, to reduce it in order to occupy less space in the target drive /folder at the moment of restart? Without actually resizing the file before the restart, because sometimes is just not possible.
Thanks
Andres – for general questions, head to a Q&A site like https://dba.stackexchange.com.
Sorry, I didn’t realice it was so general….thanks anyway. BTW it seems by BOL that we should reduce the datafile first (Shrink or something else).
Good one Brent. Right when I needed it.
Glad I could help!
Did any one tried moving these files in docker /container world.
This steps doesnt work after the POD or sql server restarted .
is there any other method to work in K8 or docker?
For general questions about other technologies, head to a Q&A site like https://dba.stackexchange.com or https://sqlservercentral.com.
Exactly what I needed today, thanks!
Hi I want to move the temp db to a new drive but I would like to have only 1 .mdf rather than having multiples. How do I achieve this?
For support questions, head to a support forum like https://dba.stackexchange.com or https://sqlservercentral.com.
I had done this in previous versions of SQL Server so I followed this script, fully intending on robocopying the files to the new location after I stopped sql server. Much to my shagrin when I stopped sql server and went to the destination directory some of the files had magically appeared with the new name. I thought this is weird, so I moved them out and robocopied the original files, renamed them and wat wat waaa server no start.
Luckily I looked at the script and it looked ok. Then someone suggested ‘oh just clear the directory’ so I moved all the tempdb files to another directory and viola sql server started up and built the files in the new location automagically.
Thank you SQL2016 you may have just saved my job.
So when you run the script in sql2016 no need to stop and start the service, it will just build the files in the new location on the fly. And I believe it will just leave the old files as fodder in the old location (Ihaven’t tested this yet).
This is my way to change path:
USE [tempdb]
GO
SELECT ‘ALTER DATABASE tempdb MODIFY FILE (NAME = [‘ + f.name + ‘],’
+ ‘ FILENAME = ‘
+ CASE WHEN f.type = 1 THEN replace(f.physical_name,’F:\Log\’,’J:\SQLLOGS\’)
when f.type = 0 then replace(f.physical_name,’F:\Data\’,’J:\SQLDATA\’)
ELSE ‘brak pliku’ END
+ ”’);’
FROM sys.master_files f
WHERE f.database_id = DB_ID(N’tempdb’);
thanks, but after this script, the initial size is to low. how if we can get same configuration as before?
Brent, I have looked at this site dozens of times and always get good trustworthy information. Thank you!
Today I’m moving our db to AWS and I wanted the tempdb on it’s on volume (drive), so I found your site again. And I ran this:
SELECT ‘ALTER DATABASE tempdb MODIFY FILE (NAME = [‘ + f.name + ‘],’
+ ‘ FILENAME = ”T:\’ + f.name
+ CASE WHEN f.type = 1 THEN ‘.ldf’ ELSE ‘.mdf’ END
+ ”’);’
FROM sys.master_files f
WHERE f.database_id = DB_ID(N’tempdb’);
ALTER DATABASE tempdb MODIFY FILE (NAME = [tempdev], FILENAME = ‘T:\tempdev.mdf’);
ALTER DATABASE tempdb MODIFY FILE (NAME = [templog], FILENAME = ‘T:\templog.ldf’);
ALTER DATABASE tempdb MODIFY FILE (NAME = [temp2], FILENAME = ‘T:\temp2.mdf’);
ALTER DATABASE tempdb MODIFY FILE (NAME = [temp3], FILENAME = ‘T:\temp3.mdf’);
ALTER DATABASE tempdb MODIFY FILE (NAME = [temp4], FILENAME = ‘T:\temp4.mdf’);
It all worked fine except the tempdb files, like temp4.mdf, aren’t on the T drive. Nothing is. Do I need to restart SQL? I’m worried that SQL won’t start if I do that.
Thanks!
IIRC, the files are moved on restart.
How many times have I done this the hard way. BOOM!
Thanks 3000 for sharing.