SQL SERVER – FIX: Error 5161 – An Unexpected file id was Encountered

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.

2014-12-02 17:03:24.93 spid18s SQL Trace was stopped due to server shutdown. Trace ID = ‘1’. This is an informational message only; no user action is required.

Since error message before shutdown were talking about tempDB database, I knew that I might have done something wrong while doing multi-tasking. I went back to query windows and read it completely. Initially I added files on C drive (first two commands) but later realized that I should not keep them on C as I already have space issues there, so I moved them using MODIFY FILE (last two commands)

USE [master] GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev2',
FILENAME = N'C:\TempDB\tempdev2.ndf', SIZE = 1024MB, FILEGROWTH = 0)
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev3',
FILENAME = N'C:\TempDB\tempdev3.ndf', SIZE = 1024MB, FILEGROWTH = 0)
GO

-- Space issues on C, moving them to D
ALTER DATABASE [tempdb] MODIFY FILE
( NAME = tempdev2, FILENAME = N'D:\TempDB\tempdev3.ndf', SIZE = 10MB, FILEGROWTH = 0)
GO
ALTER DATABASE [tempdb] MODIFY FILE
( NAME = tempdev3, FILENAME = N'D:\TempDB\tempdev3.ndf', SIZE = 10MB, FILEGROWTH = 0)
GO


I called my daughter to check and she told me that she can see tempdev3 many times. Bummer! The second last command should have said tempdev2. mdf

I understood the problem but SQL is not getting started now, how can I modify it? Well, the easiest option is removed and add the file with a proper name

1. Start SQL via trace flag 3608

From the command prompt, we can use

net start mssqlserver /T3608

My machine has a default instance, that’s why I have used MSSQLServer. For named instance, we need to use MSSQL$<InstanceName>

If we open ERRORLOG, we should see below

Recovering only master database because traceflag 3608 was specified. This is an informational message only. No user action is required.

2. Run ALTER Database

Run ALTER DATABASE command as below to remove unwanted files. I have connected via SQLCMD and I am removing Tempdev2 because that has file as tempdev3. This can be done from Management Studio as well.

We can also run below query to find the current mapping. Database ID = 2 is always tempDB database.

SELECT name, physical_name FROM sys.master_files WHERE database_id = 2

In my case I got below output before removal.

NAME                PHYSICAL_NAME

-------------       -------------------------------

tempdev             E:\...\tempdb.mdf

templog             E:\...\templog.ldf

tempdev2            E:\TempDB\tempdev3.ndf

tempdev3            E:\TempDB\tempdev3.ndf

Based on the output you receive, you have to make appropriate changes.

3. Stop SQL Service and start normally.

Once above steps are performed, we should be able to connect to SQL and make changes to TempDB correctly this time.

Have you ever encountered similar tempdb errors? How did you fix them?

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