SQL SERVER – Error while starting SQL Server logFileSize <= BlkToByteOffset ((ULONG)-1)

Recently I was playing with tempdb database in SQL Server and made some mistake. Due to that I was then not able to start SQL Service itself. Since it was an interesting problem so worth blogging about it.

Let’s have a look at problem. Whenever there is a SQL startup trouble, always start from SQL ERRORLOG. If you are

SQL SERVER – Where is ERRORLOG? Various Ways to Find its Location

2015-08-08 15:07:16.67 spid10s Clearing tempdb database.
…
2015-08-08 15:07:16.99 spid10s Error: 17066, Severity: 16, State: 1.
2015-08-08 15:07:16.99 <span style="color: #ff0000;">spid10s SQL Server Assertion: File: <"logmgr.cpp"</span>>,<span style="color: #ff0000;"> line=14870 Failed Assertion = '</span>logFileSize<span style="color: #ff0000;"> <= BlkToByteOffset ((ULONG</span>)<span style="color: #ff0000;">-1)'</span>. 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.
2015-08-08 15:07:16.99 spid10s Error: 3624, Severity: 20, State: 1.
2015-08-08 15:07:16.99 spid10s A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a Hotfix from Technical Support.
2015-08-08 15:07:17.00 spid10s Error: 5173, Severity: 16, State: 1.
2015-08-08 15:07:17.00 spid10s One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup.
2015-08-08 15:07:17.00 spid10s Error: 1802, Severity: 16, State: 4.
2015-08-08 15:07:17.00 spid10s CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
2015-08-08 15:07:17.00 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.

Above is snippet of ERRORLOG where I have removed many things to make this clear in the blog. Here is something interesting. If we look at the file size of the tempdb transaction log file, its 0 KB (yes zero KB). I thought its TempDB so it would be recreated anyways, so I deleted them but still no joy. I was having another SQL instance with the same build of SQL Server so I picked files from there and dumped to desired location but SQL Startup automatically replaced those files.

Solution:

A) Start SQL Server with startup parameter /f which stands for minimal configuration. You need to change instance name/server name. For me it is named instance of SQL Server called SQL2014.

net start MSSQL$SQL2014 /f

If you have default instance, then it would be called as MSSQLServer.

B) Connect to SQL via SQLCMD. You need to change instance name/server name

SQLCMD -S.\SQL2014

C) After connection is made, check the file sizes of tempdb database

SELECT size FROM sys.master_files WHERE database_id = 2


In my case I got 0 for LDF file and that was the cause of the whole problem.

D) If you get zero, then alter the database and change the size again

ALTER DATABASE [tempdb] MODIFY FILE(NAME=N'templog',SIZE= 10240KB);

E) Check size again by running the same command as step 3. Then exit from SQLCMD

F) Stop SQL Service

net stop MSSQL$SQL2014

G) Start SQL normally and if you were hitting the same issue, then it should be fixed. The same command as step 1 but without /f parameter.

net start MSSQL$SQL2014

Here is the screenshot of the solution

What caused the problem?

As I said in the beginning, I was playing with TempDB and by mistake I ran below command

ALTER DATABASE [tempdb] MODIFY FILE(NAME=N'templog',SIZE= 0);

That zero in size has caused the whole problem.

Note: Please do not run above command on production server. 

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