You administer a Microsoft SQL Server 2012 instance. After a routine shutdown, the drive
that contains tempdb fails.
You need to be able to start the SQL Server.
What should you do?
A.
Modify tempdb location in startup parameters.
B.
Start SQL Server in minimal configuration mode.
C.
Start SQL Server in single-user mode.
D.
Configure SQL Server to bypass Windows application logging.
Explanation:
http://msdn.microsoft.com/en-us/library/ms186400.aspx
http://msdn.microsoft.com/en-us/library/ms345408.aspx
sqlcmd > start sqlservr -f
0
0
So it’s C
https://www.xtivia.com/start-sql-server-lost-tempdb-data-files/
The -f flag is for single user mode, which is odd because the letter f is not in the phrase “single user mode”
0
0
Nope, the -f is for minimum configuration mode, but then that doesn’t work because you then also have to set trace to 3608
http://www.sqlsoldier.com/wp/sqlserver/day28of31daysofdisasterrecoveryrecoveringsqlifthetempdbdrivedies
http://thedbavault.blogspot.com/2013/05/recovering-sql-server-after-tempdb.html
0
0
If you have configuration problems that prevent the server from starting, you can start an instance of Microsoft SQL Server by using the minimal configuration startup option. This is the startup option -f. Starting an instance of SQL Server with minimal configuration automatically puts the server in single-user mode.
When you start an instance of SQL Server in minimal configuration mode, note the following:
Only a single user can connect, and the CHECKPOINT process is not executed.
Remote access and read-ahead are disabled.
Startup stored procedures do not run.
tempdb is configured at the smallest possible size.
After the server has been started with minimal configuration, you should change the appropriate server option value or values, stop, and then restart the server.
How to Start SQL Server with Minimal Configuration / How to Start SQL Server without TempDB database
1. Open Command Prompt as an administrator and then go to the BINN directory where SQL Server is installed and type sqlservr.exe /f /c. On our Production Server SQL Server is installed on the following drive location “E:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2014\MSSQL\Binn\”.
sqlservr.exe /f /c
2. Open New Command Prompt window as an administrator and then Connect to SQL Server Instance Using SQLCMD.
SQLCMD –S localhost –E
3. Once connected to SQL Server Instance execute the below TSQL Script to move TempDB data and log file to a new location. For detailed information and best practices, see How to Move TempDB Database Files to a New Drive in SQL Server.
USE MASTER
GO
ALTER DATABASE TempDB MODIFY FILE (NAME = tempdev, FILENAME = ‘E:\TempDB\Tempdb.mdf’)
GO
ALTER DATABASE TempDB MODIFY FILE (NAME = templog, FILENAME = ‘E:\TempDB\templog.ldf’)
GO
4. Exit SQLCMD window by typing Quit and Press Enter.
5. In the initial window click CTRL C and enter Y to Stop SQL Server Service.
6. Finally, start SQL Server Database Engine Using SQL Server Configuration Manager.
Ref: https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/start-sql-server-with-minimal-configuration
0
0