July 15, 2009 at 1:38 am
So earlier today I executed the statement ;
Alter database tempdb modify file (name = templog, filename = 'D:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA')
Now, when I restarted the instance it failed to start. I get 3 errors, all stating access is denied to the folder I am attempting to access, however the account I am logging on as has full admin rights to the server, and I even added it just in case to the entire directory it is accessing. Still nothing. I tried setting this to log on as the local account and nothing. There is plenty of space to create the tempdb file however something must have gone wrong.
Anyone have any ideas. I am attempting to find in the registry if I can modify this back, but I do not see this entry in the registry.
Major help would be appreciated, this is a live prod server!!
Link to my blog http://notyelf.com/
July 15, 2009 at 3:12 am
1) Start the instance of SQL Server in master-only recovery mode by entering one of the following commands at the command prompt. The parameters specified in these commands are case sensitive. The commands fail when the parameters are not specified as shown.
For the default (MSSQLSERVER) instance, run the following command:
NET START MSSQLSERVER /f /T3608
For a named instance, run the following command:
NET START MSSQL$instancename /f /T3608
2) For each file to be moved, use sqlcmd commands or SQL Server Management Studio to run the following statement.
ALTER DATABASE database_name MODIFY FILE( NAME = logical_name , FILENAME = 'new_path\os_file_name' )
3) Exit the sqlcmd utility or SQL Server Management Studio.
4) Stop the instance of SQL Server. For example, run NET STOP MSSQLSERVER.
5) Move the file or files to the new location.
6) Restart the instance of SQL Server. For example, run NET START MSSQLSERVER.
July 15, 2009 at 3:14 am
Check out the fallowing URL – http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/8ceb82fd-fc56-4a99-9317-590cca683f2b%5B/url%5D. I don’t have time to test it, but it seemed to help some of the DBAs that are participating in the thread.
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/
July 15, 2009 at 3:27 am
You should mention the full file name. Not just the folder name. For example:
alter database tempdb modify file (name = templog, filename = 'D:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\templog.LDF')
And you have copy the file yourself to the new location. System will not copy or create it.
July 15, 2009 at 4:04 am
Suresh B. (7/15/2009)
You should mention the full file name. Not just the folder name. For example:
alter database tempdb modify file (name = templog, filename = 'D:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\templog.LDF')And you have copy the file yourself to the new location. System will not copy or create it.
Actually the system will create the files because this is the TempDb. The TempDB is build from scratch each time that the server starts, so there is no need to copy/create the files manually. Even if you will copy or create the files, they won’t be used and the server will create new files instead of the old files.
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/
July 15, 2009 at 4:18 am
Thanks Adi
July 15, 2009 at 11:37 am
Thanks all.
I realized after posting the problem with the statement I executed.
Your tips helped me get the tempdb situated and the sql server instance is up and running!
Link to my blog http://notyelf.com/
July 15, 2009 at 1:24 pm
Whenever I move sqlserver files, I always xcopy the current folders (without files) to the new location including the authorisations !
(/O parameter)
I'm sure ROBOCOPY will also have such an option.
You probably need to put that auth back in place (grant the correct auth to the service account !) and off you go.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 15, 2009 at 11:58 pm
shannonjk (7/15/2009)
Thanks all.I realized after posting the problem with the statement I executed.
Your tips helped me get the tempdb situated and the sql server instance is up and running!
Happy to know that your SQL Server instance is up and running 🙂
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply