Starting SQL Service with minimal configuration for issue with Tempdb location
If a SQL instance starting up as a service cannot find the storage device/ Disk location where the tempdb files are stored then the instance will fail to startup.
Since the service cannot be started, the tempdb location can not be reconfigured from SSMS in a normal way. The only way to Reconfigure TempDb File locations is through command-prompt window by Connecting to SQLCMD with minimal configuration.
Steps involved in this activity are:
1.Connecting to SQLCMD with minimal configuration.
2.Reconfigure the tempdb location
3.Stop the command-prompt instance
4.start the service from SQL Configuration manager.
To start a SQL instance in a command-prompt window, cd to the instance’s Binn directory (eg.cd ‘C:Program FilesMicrosoft SQL ServerMSSQL10.SQL_INSTANCEMSSQLBinn’), and enter the following command:
sqlcmd -S "MY_SERVERSQL_INSTANCE"
1> USE master
2> go
Changed database context to 'master'.
1> ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'R:temptempdb.mdf')
2> go
The file "tempdev" has been modified in the system catalog. The new path will be used the next time the database is started.
1> ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'R:temptemplog.ldf')
2> go
The file "templog" has been modified in the system catalog. The new path will be used the next time the database is started.
1> quit
Learn about SQL Server detecting logical consistency based issues and how to resolve them.
Learn about SQL Server error 1222 and how to resolve the lock request time out…
Discover the new features of Microsoft SQL Server 2022 and how they compare to previous…
SQL Server Error 1222 lock request time out period exceeded Lock request time out…
SQL Server Error : 427, Severity: 20. Could not load the definition for constraint ID…
SQL Server Error : 204, Severity: 20. Normalization error in node %ls.
This website uses cookies.