Windows OS Hub
  • Windows Server
    • Windows Server 2022
    • Windows Server 2019
    • Windows Server 2016
    • Windows Server 2012 R2
    • Windows Server 2012
    • Windows Server 2008 R2
    • SCCM
  • Active Directory
    • Active Directory Domain Services (AD DS)
    • Group Policies
  • Windows Clients
    • Windows 11
    • Windows 10
    • Windows 8
    • Windows 7
    • Windows XP
    • MS Office
    • Outlook
  • Virtualization
    • VMWare
    • Hyper-V
    • KVM
  • PowerShell
  • Exchange
  • Cloud
    • Azure
    • Microsoft 365
    • Office 365
  • Linux
    • CentOS
    • RHEL
    • Ubuntu
  • Home
  • About

Windows OS Hub

  • Windows Server
    • Windows Server 2022
    • Windows Server 2019
    • Windows Server 2016
    • Windows Server 2012 R2
    • Windows Server 2012
    • Windows Server 2008 R2
    • SCCM
  • Active Directory
    • Active Directory Domain Services (AD DS)
    • Group Policies
  • Windows Clients
    • Windows 11
    • Windows 10
    • Windows 8
    • Windows 7
    • Windows XP
    • MS Office
    • Outlook
  • Virtualization
    • VMWare
    • Hyper-V
    • KVM
  • PowerShell
  • Exchange
  • Cloud
    • Azure
    • Microsoft 365
    • Office 365
  • Linux
    • CentOS
    • RHEL
    • Ubuntu

 Windows OS Hub / SQL Server / Starting SQL Server without TempDB Database

September 7, 2021 SQL Server

Starting SQL Server without TempDB Database

In this article, we will show how to repair the TempDB Database on the Microsoft SQL Server using Single User or Minimal Configuration mode. This guide should help you to repair/move the MSSQL temdb database if it is corrupted or missing.

This method is also used in emergency cases when SQL Server cannot be started or you cannot connect to it due to high load or configuration errors preventing SQL Server from starting up normally.

Contents:
  • How to Start SQL Server in the Minimal Configuration Mode?
  • Recreate (Move) SQL Server TempDB Database in the Single-User Mode

How to Start SQL Server in the Minimal Configuration Mode?

If SQL Server cannot be started due to any configuration errors:

  • Tempdb is missing or damaged
  • A trigger or procedure error prevents you from connecting to an SQL Server instance
  • Any other errors related to SQL Server configuration

Then you have to start SQL Server in the Minimal Configuration mode. To start the MSSQL in Minimal Configuration, add -f option to the instance startup command.

In this mode, MSSQL is started with the specific configuration:

  • A server is started in the single-user (exclusive) mode
  • Stored procedures configured to start after the server startup are not started
  • Tempdb database will be configured to the minimum size allowed
  • Database Checkpoints are disabled
Note. The minimal configuration mode is only used to fix configuration errors. After making the changes, restart your server without the –f option.

In the single-user mode, you can restore the damaged master database and other system databases.

Recreate (Move) SQL Server TempDB Database in the Single-User Mode

As an example, we will take a scenario when tempdb files on a disk are completely lost and SQL Server cannot be started.

If temdb is missing, the following errors will appear in the MSSQL logs:

CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file ‘D:\DB\Tempdb.mdf’
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.
  1. Log in to the host with the MSSQL instance you need (locally or using RDP);
  2. Open the SQL Server Configuration Manager, then go to the instance properties -> Startup Parameters and add the -f option;
    You can also use the NET START MSSQLSERVER /f command to start your SQL Server with minimal configuration.
    start mssql in the minimal configuration mode
  3. Restart the SQL Server instance;
    Tip. After the restart of the instance, the server will accept only a single user connection. To prevent anything from using the connection, disable the SQL Server Agent, restrict access to the SQL Server in your firewall, and make sure that other administrators or local apps do not use the connection. You can also use -m parameter to specify the app you are going to use to connect. For example,-mSQLCMD or -m"SQL Server Management Studio". Since you cannot set quoted parameters in the SQL Server Configuration Manager, like -m"SQL Server Management Studio", you will be able to use it only if you start SQL Server from the command prompt.
  4. Open the SQL Server Management Studio and click the New Query button.

connect sqlserver in the single mode using Management Studio

If you see an error like this, somebody is already using the connection:

Cannot connect to mssql1\node2
Login failed for user username. Reason: Server is in single user mode. Only one administrator can connect at this time. (Microsoft SQL Server, Error: 18461)

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

If you did everything right, you will access the server.

Then change the paths to the damaged tempdb. Run the following T-SQL commands:

USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'E:\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'E:\templog.ldf');
GO

It will set a new tempdb configuration. The tempdb files will be re-created from scratch on the E:\ drive.

The file tempdev/templog has been modified in the system catalog. The new path will be used the next time the database is started.
Note. I place tempdb on this disk temporary, since you are likely to change the tempdb configuration. You can split it into several data files and modify the path to it after repairing the MSSQL Server.

alter sql server temdb file path

Remove -f parameter from your instance configuration in SQL Server Configuration Manager and restart the instance.

re-create temdb sql server

Using this mode, you can also fix some other configuration problems, for example, disable a trigger or a procedure.

Also, to start SQL Server in the Minimal Configuration Mode, you can use the command:

"C:\Program Files\Microsoft SQL Server\MSSQL15.NODE2\MSSQL\Binn\sqlservr.exe" –c -f -sNODE2"

It will start the MSSQL NODE2 instance in the Safe Mode. The -c parameter makes SQL Server boot faster when started from the command prompt. You can also use -m"SQL Server Management Studio" to restrict the number of connections from the SQL Studio or -mSQLCMD to connect only from sqlcmd.

sqlservr tun in the minimal configuration

0 comment
1
Facebook Twitter Google + Pinterest
previous post
How to Backup (Export) and Restore Device Drivers on Windows 10?
next post
How to Hide Users and Groups from the Global Address List on Exchange/Office 365?

Related Reading

MS SQL Server Setup Stucks on Install/Uninstall

January 9, 2023

Configure SSL Connection Encryption in MS SQL Server

August 15, 2022

How to Reset SA Password on Microsoft SQL...

December 21, 2021

Upgrade Microsoft SQL Server Evaluation Edition to Standard/Enterprise

December 9, 2021

Configuring Always-On High Availability Groups on SQL Server

December 2, 2021

Leave a Comment Cancel Reply

Categories

  • Active Directory
  • Group Policies
  • Exchange Server
  • Microsoft 365
  • Azure
  • Windows 11
  • Windows 10
  • Windows Server 2022
  • Windows Server 2019
  • Windows Server 2016
  • PowerShell
  • VMWare
  • Hyper-V
  • Linux
  • MS Office

Recent Posts

  • Configure User’s Folder Redirection with Group Policy

    February 3, 2023
  • Using Previous Command History in PowerShell Console

    January 31, 2023
  • How to Install the PowerShell Active Directory Module and Manage AD?

    January 31, 2023
  • Finding Duplicate E-mail (SMTP) Addresses in Exchange

    January 27, 2023
  • How to Delete Old User Profiles in Windows?

    January 25, 2023
  • How to Install Free VMware Hypervisor (ESXi)?

    January 24, 2023
  • How to Enable TLS 1.2 on Windows?

    January 18, 2023
  • Allow or Prevent Non-Admin Users from Reboot/Shutdown Windows

    January 17, 2023
  • Fix: Can’t Extend Volume in Windows

    January 12, 2023
  • Wi-Fi (Internet) Disconnects After Sleep or Hibernation on Windows 10/11

    January 11, 2023

Follow us

woshub.com
  • Facebook
  • Twitter
  • RSS
Popular Posts
  • Configure SSL Connection Encryption in MS SQL Server
  • MS SQL Server 2019 Installation Guide: Basic Settings and Recommendations
  • FAQ on Microsoft SQL Server Licensing
  • Querying Microsoft SQL Server (MSSQL) Database with PowerShell
  • Configuring Always-On High Availability Groups on SQL Server
  • Upgrade Microsoft SQL Server Evaluation Edition to Standard/Enterprise
  • How to Reset SA Password on Microsoft SQL Server?
Footer Logo

@2014 - 2023 - Windows OS Hub. All about operating systems for sysadmins


Back To Top