SQL Server Performance Forum – Threads Archive

Moving Tempdb location

Hi , I moved the tempdb data & log file to different location from where it was installed & then i copied the data & log to that destination location. When i restarted the sqlserver , its not starting. DAC is not enabled on that machine. Is there anywhere in register where i can go i change to location to old one. How could i solve this problem .. Thanks
emamuthu

---

This are the error i getting when starting the sqlserver<br /><br />FCB:<img src=’/community/emoticons/emotion-3.gif’ alt=’:O’ />pen: Operating system error 5(Access is denied.) occurred while creating or opening file ‘Crogram FilesMicrosoft SQL ServerMSSQL.1MSSQLData empdb.mdf’. Diagnose and correct the operating system error, and retry the operation.<br /><br />CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file ‘Crogram FilesMicrosoft SQL ServerMSSQL.1MSSQLData empdb.mdf’.<br /><br />FCB:<img src=’/community/emoticons/emotion-3.gif’ alt=’:O’ />pen failed: Could not open file Crogram FilesMicrosoft SQL ServerMSSQL.1MSSQLData empdb.mdf for file number 1. OS error: 5(Access is denied.).<br /><br /><br />

---

Got it…. Startup account doesn’t have enough rights to read the directory. Thanks
emamuthu

---

BOL has info on how to move system databases and also to support move of tempdb. indeed, your serviceaccount needs to have the privilages to access the folder where the data and logfile(s) reside.

---

Always make sure Service account is member of Local Admininstrators group…
MohammedU.
Moderator
SQL-Server-Performance.com

---

but then again, that goes against the "least privileged" philosophy …

---

Also when you move tempdb, you don’t need to copy the physical files. they get created at startup. Simply run the query to move them, restart SQL Server, then delete the old files. Saves time if they’re an appreciable size.

---

I believe the problem is resolve by this.
Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

---

]]>