Whenever, there is not enough disk space to create the tempdb during the sql server start up, we will encounter the 17053 and 5149 error. The error message in the error log is quite self explanatory.
2013-07-02 19:41:33.39 spid10s Error: 17053, Severity: 16, State: 1.
2013-07-02 19:41:33.39 spid10s C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\tempdb.mdf: Operating system error 112(There is not enough space on the disk.) encountered.
2013-07-02 19:41:33.41 spid10s Error: 823, Severity: 24, State: 6.
2013-07-02 19:41:33.41 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:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\tempdb.mdf'. 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.
2013-07-02 19:41:33.42 spid10s Error: 5149, Severity: 16, State: 3.
2013-07-02 19:41:33.42 spid10s MODIFY FILE encountered operating system error 112(There is not enough space on the disk.) while attempting to expand the physical file 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\tempdb.mdf'.
2013-07-02 19:41:33.42 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.
2013-07-02 19:41:33.42 spid10s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
Here are the steps to reproduce the error:
-- In my Test env , C drive has 77GB free out of 99GB
alter database tempdb modify file( name=tempdev,size=40GB);
Shutdown sql server and rename the tempdb file to somehitng else. Now, C drive only has 37GB free .
When restart SQL Server, SQL server can not find the tempdev file so it needs to recreate new 40GB file but there is not enough disk space to create one.
start the sql server with minimal configuration.
net start mssqlserver /f
-- current size
-- Actual size
select name, (convert(bigint, size)*8192)/(1024*1024) as size_in_mb
from sys.master_files where database_id = 2;
If we use dbcc loginfo to check the log size of tempdb, there is only 2 VLFs.
Now, we can modify the size with proper size and restart the SQL server
alter database tempdb modify file ( name=tempdev ,size=1000MB);