Unable to start SQL Server after relocating TempDB

After I modified the file size of tempdb database yesterday on my SQL Express instance, SQL Server was failed to start. When I checked the SQL Server error log, following entries were recorded.

2012-11-07 11:25:34.99 spid10s Error: 17053, Severity: 16, State: 1.
2012-11-07 11:25:34.99 spid10s C:\SQLServer\TempDB\TempDB_log.LDF: Operating system error 112(There is not enough space on the disk.) encountered.
2012-11-07 11:25:35.06 spid10s Error: 823, Severity: 24, State: 6.
2012-11-07 11:25:35.06 spid10s The operating system returned error 38(Reached the end of the file.) to SQL Server during a read at offset 0000000000000000 in file ‘C:\SQLServer\TempDB\TempDB_log.LDF’. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
2012-11-07 11:25:35.11 spid10s Error: 5149, Severity: 16, State: 3.
2012-11-07 11:25:35.11 spid10s MODIFY FILE encountered operating system error 112(There is not enough space on the disk.) while attempting to expand the physical file ‘C:\SQLServer\TempDB\TempDB_log.LDF’.
2012-11-07 11:25:35.11 spid10s 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.

SQL Server instance was not starting because I had modified the tempDB to a bigger size then the free space available on the disk. imageTo successfully start the SQL Server instance, I need to revert back the tempdb file settings. To do that, I had started the SQL Server instance with minimal configuration, refer the following page from BOL for more details. Open the command prompt with administrative privileges and execute the following command to start the SQL Server instance:

sqlservr.exe –s SQL2008R2 /f /c

The –s switch in the above command, specifies the named instance of SQL Server. Once SQL Server instance was started with minimal configuration, the instance was also running in Single-user mode. Thus, connecting to the instance using SQLCMD, was a good choice. Executing the below t-sql commands using SQLCMD, modified the tempdb configuration. Stop the SQL server instance by pressing Ctrl + C.image

use [master];
alter database [tempdb]
	modify file (name='tempdev', filename='C:\SQLServer\TempDB\TempDB.MDF', size = 1GB);
alter database [tempdb]
	modify file(name='templog', filename='C:\SQLServer\TempDB\TempDB_log.LDF', size = 1GB);

SQL Server service was started from SQL Server Configuration Manager, issue fixed !

Hope, this may help someone. Happy Learning Smile


About Sudeepta Ganguly

A SQL Server User... Still Learning
This entry was posted in How To, Just Learned, SQL Server, SQL Server Configuration, SQLCMD and tagged , , , . Bookmark the permalink.

7 Responses to Unable to start SQL Server after relocating TempDB

  1. blakhani says:

    Good one!

  2. Vinod Kumar says:

    Nice … You need to start sharing more of these.

  3. Anand Kumaran says:

    Hi I have used the above Scenario, but SQLCMD is failing to connect due to HResult 0x27D error. Please help i got stuck in this at production server

  4. Anand Kumaran says:

    Please Share your Number this is bit urgent, i can call you and get Help..

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s