February 17, 2009 at 12:51 am
Hi,
I've moved tempdb files to another drive 2 hours ago (suppose D:\tempdb\..). and when I run exec sp_helpfile, it says that the tempdb files are in the new place but there's nothing displayed in that folder now!
IS there anything wrong?
February 17, 2009 at 1:02 am
peace2007 (2/17/2009)
Hi,I've moved tempdb files to another drive 2 hours ago (suppose D:\tempdb\..). and when I run exec sp_helpfile, it says that the tempdb files are in the new place but there's nothing displayed in that folder now!
IS there anything wrong?
are u able to connect to the sql server??
Regards,
[font="Verdana"]Sqlfrenzy[/font]
February 17, 2009 at 1:10 am
yes I connected to that and executed a few queries 😎
February 17, 2009 at 1:17 am
Could it be that you are connected to a different server in the management studio?
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 17, 2009 at 1:29 am
No 'm sure I've connected to the correct server
There's one point, I haven't restarted server after moving files. is it the case?
February 17, 2009 at 1:40 am
Moving temp db is simple process but it will require a restart. This is because sql server core engine is always using tempdb for all its internal process.
February 17, 2009 at 2:08 pm
restarting sql services will solve your problem.
February 17, 2009 at 2:12 pm
When you run ALTER DATABASE ... MOVE ... SQL doesn't actually move any files. If it's a user database that you've done the alteration to, you have to take it offline, move the files and then bring it backup online. With TempDB, next time tempDB's created it will be created in the new place.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 17, 2009 at 10:52 pm
Thanks all 🙂
February 18, 2009 at 3:22 am
If you want to move tempdb while SQL is running, you can use ALTER DATABASE to add new files to tempdb. You can then use SHRINKFILE with EMPTYFILE to clear out your old tempdb files, and then issue a drop for those files.
However, even if they are no longer being used they will continue to be allocated to tempdb until you next restart SQL Server.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply