SQL Server Error 5161 – An unexpected file id was encountered

sqlserver2014

Have you ever tried to move the TempDB in SQL Server and gotten the settings wrong? In this article by Pinal Dave we find that he made a simple mistake but was able to quickly solve the issue. He was able to start the instance using trace flag 3608 and then correct the error by deleting a incorrectly named file. He then restarted the SQL Server service and the issue was resolved.

Recently I was planning to give a demo about increasing tempdb files and its performance benefit. So while making the demo at home, I was doing multi-tasking. Talking to my daughter, having food and making demo. So, I ran the script and restarted SQL Server service but SQL Service didn’t start.

I looked into the ERRORLOG (and I would suggest you to look at that log in case of any SQL startup issues) and found below errors before SQL shutdown messages.

2014-12-02 17:03:24.42 spid18s     Error: 5161, Severity: 16, State: 1.

2014-12-02 17:03:24.42 spid18s     An unexpected file id was encountered. File id 3 was expected but 4 was read from “D:\TempDB\tempdev3.ndf”. Verify that files are mapped correctly in sys.master_files. ALTER DATABASE can be used to correct the mappings.

2014-12-02 17:03:24.42 spid18s     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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s