Cannot connect to sql instance after changing the filepath for tempdb drive

Question

Cannot connect to sql instance after changing the filepath for tempdb drive

johnmason137

SSC Rookie

Points: 39
More actions
August 31, 2017 at 12:59 pm

#325120

Hi All,
By mistake, I modified the tempdb file to wrong location. I did not specify the file name in there. Now the instance does not come back on.
-----
USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'T:\MSSQL\DATA');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'T:\MSSQL\DATA');
GO
---------------
So I started the instance with the following query: net start mssql$map /f / t3600 so I can correct the filepath and name
It shows it started, however when I try to login with the query: sqlcmd -S .\map -E I get an error "Login failed for user. Reason: Server is in single user mode. Only one administrator can connect at this time"
Anyone help is greatly appreciated. Thank you.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

SQL_Hacker SSCommitted Points: 1728 More actions · Answer 1

Make sure there are no other connections to instance...like applications or SQL Agent (shouldn't be on, but make sure). Something else is grabbing the single connection, which is blocking your access (assuming your login is typed correctly).

Alan H
MCSE - Data Management and Analytics
Senior SQL Server DBA
Best way to ask a question: http://www.sqlservercentral.com/articles/Best+Practices/61537/

johnmason137 SSC Rookie Points: 39 More actions · Answer 2

Thank you for your reply Alan. I tried to login right away after issuing net start mssql$map /f / t3600 this command. I still get single connection error. Is there a way to check if which user is connected or when I restart specify something where I am the only one who can connect.

Sue_H SSC Guru Points: 90860 More actions · Answer 3

johnmason137 - Thursday, August 31, 2017 2:31 PM
Thank you for your reply Alan. I tried to login right away after issuing net start mssql$map /f / t3600 this command. I still get single connection error. Is there a way to check if which user is connected or when I restart specify something where I am the only one who can connect.

Since you are using sqlcmd to connect, try starting it and limit the application that can connect as only sqlcmd. So connect using:
net start mssql$map /f /t3600 /msqlcmd

Sue

Arsh SSCertifiable Points: 6367 More actions · Answer 4

johnmason137 - Thursday, August 31, 2017 12:59 PM
Hi All,
By mistake, I modified the tempdb file to wrong location. I did not specify the file name in there. Now the instance does not come back on.
-----
USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'T:\MSSQL\DATA');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'T:\MSSQL\DATA');
GO
---------------
So I started the instance with the following query: net start mssql$map /f / t3600 so I can correct the filepath and name
It shows it started, however when I try to login with the query: sqlcmd -S .\map -E I get an error "Login failed for user. Reason: Server is in single user mode. Only one administrator can connect at this time"
Anyone help is greatly appreciated. Thank you.

Some application services from the 'middle tier' continuously ping the database server and take the connection immediately . Its difficult to beat them in acquiring the connection.. I faced similar issue (single-user conn) with sharepoint database. Hence advised to completely close the apps services and then take your connection at the database.