November 23, 2015 at 6:48 am
I tried moving my tempdb to a different drive other than C: but now sql service won't start. I've tried to start from cmd prompt with -f. I think the service starts but when I try to open a 2nd command prompt and run sqlcmd to try and alter the tempdb file location back to C: I get the below. Any help is greatly appreciated.
C:\Users\bryana.admin>sqlcmd
Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : Named Pipes Provider: C
ould not open a connection to SQL Server [2]. .
Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : A network-related or in
stance-specific error has occurred while establishing a connection to SQL Server
. Server is not found or not accessible. Check if instance name is correct and i
f SQL Server is configured to allow remote connections. For more information see
SQL Server Books Online..
November 23, 2015 at 6:53 am
What are the errors you are getting in your application log or SQL log?
Maybe the path you have specified for the tempdb files to be created in don't exist or spelt incorrectly.
November 23, 2015 at 7:06 am
This is from the log. I've tried to give full control to every account I can think of. The file path is correct.
Error: 5123, Severity: 16, State: 1.
2015-11-23 08:43:31.74 spid10s CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file 'E:\MSSQL\DATA'.
November 23, 2015 at 7:11 am
The account that SQL server is running under needs full control permission of the folder where the files are. Check Config Manager, check the service it's running under and make sure the permissions are correct.
That said error message looks weird. It's giving a CREATE FILE error on a folder. What were the ALTER DATABASE statements that you ran? What other messages are there in the error log relating to TempDB.
See https://www.simple-talk.com/sql/backup-and-recovery/the-sql-server-instance-that-will-not-start/ for how to start SQL without TempDB.
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
November 23, 2015 at 7:11 am
Bryan.avergonzado (11/23/2015)
This is from the log. I've tried to give full control to every account I can think of. The file path is correct.Error: 5123, Severity: 16, State: 1.
2015-11-23 08:43:31.74 spid10s CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file 'E:\MSSQL\DATA'.
that looks like just permissions, should be a fast fix.
go to services, and note what account is used to start the services.
go to the E:\MSSQL folder on the server.
right click properties.
security tab
add the same account with full control.
Lowell
November 23, 2015 at 7:48 am
When I attempted to move the temp db I used the following statement.
use master
go
Alter database tempdb modify file (name = tempdb, filename = 'E:\MSSQL\DATA\tempdb.mdf')
go
Alter database tempdb modify file (name = templog, filename = 'E:\MSSQL\DATA\templog.ldf')
go
It did have an error but I can't remember is it specifically: It said file tempdb name doesn't exit
The second statement completed successfully.
I was able to add the the service account to the folder permissions. It has full control.
Error: 5123, Severity: 16, State: 1.
2015-11-23 09:22:12.17 spid10s CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file 'E:\MSSQL\DATA'.
2015-11-23 09:22:12.18 spid8s 0 transactions rolled back in database 'TCMWorking' (7:0). This is an informational message only. No user action is required.
2015-11-23 09:22:12.19 spid28s 1 transactions rolled forward in database 'TCM_Legacy' (9:0). This is an informational message only. No user action is required.
2015-11-23 09:22:12.22 spid10s Error: 17204, Severity: 16, State: 1.
2015-11-23 09:22:12.22 spid10s FCB::Open failed: Could not open file E:\MSSQL\DATA for file number 2. OS error: 5(Access is denied.).
2015-11-23 09:22:12.22 spid10s Error: 5120, Severity: 16, State: 101.
2015-11-23 09:22:12.22 spid10s Unable to open the physical file "E:\MSSQL\DATA". Operating system error 5: "5(Access is denied.)".
2015-11-23 09:22:12.22 spid10s Error: 1802, Severity: 16, State: 4.
2015-11-23 09:22:12.22 spid10s CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
2015-11-23 09:22:12.22 spid10s Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.
November 23, 2015 at 8:47 am
Bryan.avergonzado (11/23/2015)
When I attempted to move the temp db I used the following statement.use master
go
Alter database tempdb modify file (name = tempdb, filename = 'E:\MSSQL\DATA\tempdb.mdf')
go
The logical name for the tempdb primary file is "tempdev", re run the first query using this
Alter database tempdb modify file (name = tempdev, filename = 'E:\MSSQL\DATA\tempdb.mdf')
go
Also ensure the sql server service account has full permissions on the folder
E:\MSSQL\DATA
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 23, 2015 at 8:53 am
Unfortunately, I can't SQL Server to start now.
November 23, 2015 at 8:58 am
GilaMonster (11/23/2015)
See https://www.simple-talk.com/sql/backup-and-recovery/the-sql-server-instance-that-will-not-start/ for how to start SQL without TempDB.
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
November 23, 2015 at 11:38 am
Thank you for the link to the article Gila. That worked.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply