Change Location for TempDB

Question

I have by mistake changed the location for the tempDB files to

'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\'

rather than

'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\tempdb.mdf'

I am not able to login into SSMS now and i am also not able to start the SQL services. Also, I have tried logging in using net start msqsqlserver /f /t3608 but then when i try SQLCMD i get this error `

Reason: Server is in single user mode. Only one administrator can connect at this time

`

Tripurari Yadav Tripurari Yadav · Accepted Answer · 2017-07-31T07:52:58

staring SQL from command line as a service, My instance name is SQL2K8R2 so below would be the command

Net Start MSSQL$SQL2K8R2 /m”SQLCMD”

If you have default instance than it would be "Net Start MSSQLServer /m”SQLCMD”"

Once you have started SQL Service in Single use mode then only SQLCMD application can connect and other connection would get error message.

2013-12-06 09:13:50.08 Server      Registry startup parameters: 
     -d E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\master.mdf 
     -e E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\ERRORLOG 
     -l E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\mastlog.ldf 
2013-12-06 09:13:50.08 Server      Command Line Startup Parameters: 
     -s "MSSQLSERVER" 
     -m "SQLCMD"

…

2013-12-06 09:13:50.80 spid4s      Warning ****************** 
2013-12-06 09:13:50.80 spid4s      SQL Server started in single-user mode. This an informational message only. No user action is required.

….

2013-12-06 09:14:32.93 Logon       Error: 18461, Severity: 14, State: 1. 
2013-12-06 09:14:32.93 Logon       Login failed for user ‘Contoso\demouser’. Reason: Server is in single user mode. Only one administrator can connect at this time. [CLIENT: <local machine>]

Change Location for TempDB

2 Answers