SQL Server Not Starting After Re-Configuration of TempDB Database

Tempdb is typically configured after the
installation of SQL Server for better performance. The tempdb database is rebuilt
by SQL Server at startup time.  In case, it is not able to rebuild the tempdb
SQL Server will not be restarted.  In this event there are two steps
involved which are troubleshooting and recovering steps.

We don’t have the luxury of using SQL
Server tools as SQL Server service will not have started. However, you can view the
error log from notepad or other relevant tools.

Error log file is available at
<System
Drive>:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log

MSSQL11 is for SQL Server 2012 and
MSSQLServer is for the default instance.  In case you have a named instance
called QA in SQL Server 2008, this folder will be MSSQL10.QA.  In the relevant
folder, open the latest error log file using Notepad. This is the error log,
you will see from the SQL Server Management Studio as well. The below error can
be seen in the error log file.

        2015-12-08 12:46:44.99 spid10s Error:
        17053, Severity: 16, State: 1.        

        2015-12-08 12:46:44.99 spid10s C:\Program
        Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\tempdb.MDF: Operating
        system error 112(There is not enough space on the disk.) encountered.   
     
        2015-12-08 12:46:45.06 spid10s Error:
        823, Severity: 24, State: 6.
    

From the above error, it is
obvious that there is not enough disk space for tempdb to rebuild. Now the
question is, how to relocate the tempdb data file since SQL Server is not
starting?

There is an option in SQL Server to start
SQL Server with minimum configuration. Open the command line preferably with
administrator permissions or run as administrator.  Locate the SQLServer exe in
the folder. In my case of <System Drive>:\
Program
Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn

Following are the options available for SQL
Server.

              [-c] (not as a service)

[-d file] (alternative master
data file)

[-l file] (alternative master
log file)

[-e file] (alternate errorlog
file)

[-f] (minimal configuration
mode)

              [-m] (single user admin mode)

[-g number] (stack MB to
reserve)

[-k <decimal number>]
(checkpoint speed in MB/sec)

[-n] (do not use event
logging)

[-s name] (alternate registry
key name)

[-T <number>] (trace
flag turned on at startup)

              [-x] (no statistics tracking)

[-y number] (stack dump on
this error)

[-B] (breakpoint on error
(used with -y))

[-K] (force regeneration of
service master key (if exists))

              [-v] (list version information)

-f and –c are the option we need to
consider. In case you have a named instance, you need to specify the named
instance with the –s option.

sqlservr -s MSSQLSERVER -f –c

The below screen will be seen after
executing the above command,

Since we have specified the –c option SQL
Server started and is not running as a service.  Let us try to connect to SQL
Server Instance using SSMS. You will end up with following error.

        Login failed for user XXX. Reason:
        Server is in single user mode. Only one administrator can connect at this time.
        (Microsoft SQL Server, Error: 18461)
    

This means that we are not able to connect
from SSMS as the instance is running in single user mode. Thus, connecting to
the instance using SQLCMD, is an available option.  Then drop tempdb secondary
files or reduce tempdb size by running necessary T-SQL commands.

Then stop the above sql server instance by
pressing Ctrl+C. Now you should be able to start the SQL Server service without
any issue.




Array

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |