Published on

July 27, 2015

Fixing SQL Server Startup Trouble

Have you ever encountered a situation where you were unable to start the SQL Server service due to a startup trouble? If so, you’re not alone. In this blog post, we will discuss a common issue related to the tempdb database in SQL Server and provide a step-by-step solution to fix it.

When facing a SQL startup trouble, it is always recommended to start by checking the SQL ERRORLOG. This log file contains valuable information about the error and can help in troubleshooting the issue. In our case, the ERRORLOG revealed the following error:

Error: 17066, Severity: 16, State: 1.
SQL Server Assertion: File: "logmgr.cpp", line=14870 Failed Assertion = 'logFileSize <= BlkToByteOffset ((ULONG)-1)'. This error may be timing-related. If the error persists after rerunning the statement, use DBCC CHECKDB to check the database for structural integrity, or restart the server to ensure in-memory data structures are not corrupted.

Upon further investigation, we found that the size of the tempdb transaction log file was 0 KB. This was the root cause of the problem. To fix it, we followed the following steps:

  1. Start SQL Server with the startup parameter /f, which stands for minimal configuration. The command for a named instance called SQL2014 would be:
  2. net start MSSQL$SQL2014 /f
    
  3. Connect to SQL Server using SQLCMD:
  4. SQLCMD -S.\SQL2014
    
  5. Check the file sizes of the tempdb database:
  6. SELECT size FROM sys.master_files WHERE database_id = 2
    
  7. If the size is 0, alter the database and change the size:
  8. ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'templog' , SIZE = 10240KB );
    
  9. Check the size again to ensure it has been updated:
  10. SELECT size FROM sys.master_files WHERE database_id = 2
    
  11. Exit from SQLCMD.
  12. Stop the SQL Server service:
  13. net stop MSSQL$SQL2014
    
  14. Start the SQL Server service normally:
  15. net start MSSQL$SQL2014
    

By following these steps, we were able to fix the SQL Server startup trouble caused by the 0 KB size of the tempdb transaction log file.

It is important to note that the root cause of this problem was a mistake made while modifying the tempdb database. Running the command ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'templog' , SIZE = 0 ); caused the size of the transaction log file to become 0 KB. It is crucial to exercise caution when making changes to the database, especially on a production server.

Thank you for reading this blog post. We hope you found it helpful in understanding and resolving SQL Server startup troubles related to the tempdb database. If you have any questions or comments, please feel free to leave them below.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Click to rate this post!
[Total: 0 Average: 0]

Let's work together

Send us a message or book free introductory meeting with us using button below.