Tuesday, July 2, 2013

0 Not enough disk space to create the tempdb during the startup sql server

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.

image

image

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:

sp_helpdb tempdb;
-- 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.

Solution

start the sql server with minimal configuration. 

net start mssqlserver /f

image

 

--  current size
sp_helpdb tempdb;
-- Actual size
select name, (convert(bigint, size)*8192)/(1024*1024) as size_in_mb
from sys.master_files where database_id = 2;

 

image

If we use dbcc loginfo to check the log size of tempdb, there is only 2 VLFs.

 

dbcc loginfo(tempdb)

image

Now, we can modify the size with proper size and restart the SQL server

alter database tempdb modify file ( name=tempdev ,size=1000MB);

image

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates