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