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. To 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.

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

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

Hope, this may help someone. Happy Learning

About these ads