CODEWITHSUNDEEP
sql-server
user7488971
user7488971

Reputation:

How to Start SQL Server without TempDB

After the scheduled maintenance when the DBA tried to start the SQL Server; it failed due to some corruption issue with storage subsystem.

Later on, we identified that the drive on which we had our TempDB's data and log files was corrupt and it was preventing SQL Server from starting successfully. (Drive was corrupt, so I am unable to read anything from that drive)

So basically we did not have Tempdb database on the server.

And we had to start SQL Server without TempDB

So how do we start the SQL Server without TempDB and how do we fix this?

Upvotes: 5

Views: 12693

Answers (4)

Robert Sawyer
Robert Sawyer

Reputation: 1

In SQL Server 2016 If you remove the tempdb physical files, on startup it will see they are missing and rebuild them on the fly in the location they are supposed to be in sysdatabases.

Upvotes: 0

user7488971
user7488971

Reputation:

How to Start SQL Server without TempDB database?

Step 1: Start the SQL Server in minimal configuration mode.

Click here to see, "How to start the SQL Server in minimal mode using command prompt".

Step 2: Once SQL Server has started with minimum configuration mode; connect to SQL Server instance and move TempDB data and log file to a new location.

See, move TempDB data and log files to new location

Step 3: Once you have performed the troubleshooting steps; exit SQLCMD window by typing Quit and Press Enter.

Step 4: . In the initial window click CTRL C and enter Y to Stop SQL Server Service.

Step 5 : Eventually, start the SQL Server Database Engine by Using SQL Server Configuration Manager.

Upvotes: 1

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131722

Before you try anything make sure you backup your data. If one drive failed, another one might fail and leave you without your data. Drives that are purchases at around the same time tend to fail around the same time too.

You need to do that even if some of the data is stored in a RAID array - RAID isn't the same as a backup. If something happens to the array, your best case scenario is that you'll wait for a few hours to recover the data. Worst case, you could lose it all.

The process is described in The SQL Server Instance That Will not Start in the TempDB location does not exist section, and other sites like Start SQL Server without tempdb.

You'll have to start SQL Server with Minimal Configuration. In that state, tempdb isn't used. You can do this with the -f command-line parameter. You can specify this parameter in the service's property page, or by calling sqlservr.exe -f from the command line, eg:

sqlservr -f

Another option is to use the -t3608 trace flag which starts only the master database.

sqlservr -t3608

After that, you need to connect to the server with the sqlcmd utility, eg :

sqlcmd -S myservername -E

to connect using Windows authentication.

Once you do this, you can go to the master database and change the file location of the tempdb files:

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

After that, remove the parameters from the service (if you set them there) and restart the service.

Finally, you may have to reconsider the placement of TempDB. TempDB is used heavily for sorting, calculating window functions or in situations where the available RAM isn't enough. Some operations require creating intermediate results, which get stored in TempDB. In general, you should have multiple tempdb files, although the exact number depends on the server's workload.

Upvotes: 7

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13969

What version of SQL Server it is? One simple solution is to move the tempdb.* files from that location and restart the SQL Server it will create new tempdb files. If you keep those files in that same location it will fail to start.

Upvotes: 0

Related Questions