🚨 Removing Extra TempDB Data Files in SQL Server (Always On – 2 Node Cluster): Lessons from Production

Recently, I ran into an issue where TempDB had extra data files on a SQL Server Always On Availability Group (AG) configured with two nodes. On the surface, removing unused TempDB files looks straightforward. In reality—especially in AG—it’s anything but simple.

Sharing this post so others don’t repeat the same mistakes I almost did.

🔍 Why do TempDB extra files exist?

Extra TempDB files usually come from:

• Incorrect CPU core–based auto‑sizing scripts
• Past performance tuning left unchanged
• Server role change (VM resize, core reduction)
• Manual TempDB configuration copied across servers

Over time, these extra files may:

• Increase startup time
• Add unnecessary I/O overhead
• Complicate troubleshooting
• Create false performance signals

✅ Best practice: TempDB data files = logical CPU cores (up to a reasonable limit), evenly sized.

⚠️ Why is this risky in Always On AG?

Even though TempDB is not part of the AG, the SQL Server instance is.

That means:

• Any restart affects AG health
• Failover timing matters
• User workload may shift nodes unexpectedly
• TempDB is actively used by: Sorts, hashes Version store Worktables Internal AG processes

So careless execution can cause:

• Blocking
• Deadlocks
• Restart failures
• AG synchronization delays
• Performance issues at times due to incorrect configuration of tempdb data files

Many DBAs try this:

USE tempdb;

GO

DBCC SHRINKFILE (tempdev_extra_01, EMPTYFILE);

ALTER DATABASE tempdb REMOVE FILE tempdev_extra_01;

Typical errors you’ll encounter:

Error 2555

Cannot move all contents of file 'tempdev_extra_01' because it is not empty.

Error related to worktables

The file 'tempdev_extra_01' cannot be shrunk because it is being used for internal objects.

Sometimes even worse under load:

• Blocking chains
• RESOURCE DEADLOCK
• Requests stuck on PAGEIOLATCH_UP or PAGELATCH_EX

👉 Why? Because TempDB is continuously used, and SQL Server cannot evacuate the file while internal objects (like worktables, hash spills, version store) are active.

Question: What are the correct methods to handle above situation?

Here are two methods to approach the resolution.

✅ Method 1: Controlled restart + quick execution

This works sometimes but requires discipline.

Steps:

1. Identify the file(s) to be removed
2. Schedule a controlled window
3. Restart SQL Server service
4. Immediately run:

DBCC SHRINKFILE (tempdev_extra_01, EMPTYFILE);

ALTER DATABASE tempdb REMOVE FILE tempdev_extra_01;

Why this works:

• TempDB is nearly empty immediately after restart
• Fewer active sessions
• Less contention

Why it still fails sometimes:

• SQL Server creates worktables very quickly
• AG components and background tasks start using TempDB
• If you’re late by seconds, the file is already in use again

So yes—even this is unreliable in busy systems.

✅ Method 2: Start SQL Server in Single‑User Mode (Safest & Most Reliable Option)

When removing extra TempDB files fails even after a restart, starting SQL Server in single‑user mode is the most reliable and controlled approach.

This method works because it completely eliminates competing TempDB usage, which is the root cause of most FILE IN USE / worktable errors.

🔍 What is Single‑User Mode?

Single‑user mode starts SQL Server with:

• ✅ Only ONE connection allowed
• ❌ No application connections
• ❌ No SQL Agent jobs
• ❌ No parallel background activity

This gives the DBA exclusive control over TempDB.

⚠️ Important AG Considerations (Very Important)

In an Always On Availability Group (2‑node cluster):

• The instance must be planned out of service
• AG databases will temporarily go offline on that node
• Failover behavior must be understood before proceeding
• This should ideally be done on the secondary replica, if possible

TIP: Always perform TempDB extra file deletion on the secondary replica first. Afterward, fail over and repeat the process on the new secondary replica. This helps avoid unforeseen issues on the primary AG.

✅ Coordinate with:

• Application teams
• Other DBAs
• Monitoring teams

🧩 Step‑by‑Step Process

1️⃣ Stop SQL Server Agent (Mandatory)

SQL Agent is one of the most common reasons single‑user mode fails, because it grabs the only allowed connection.

Run this in PowerShell-

Stop-Service SQLSERVERAGENT

Or from SQL Server Configuration Manager- STOP sqlserveragent service.

✅ Do NOT skip this step.

2️⃣ Stop SQL Server Service

Run this in PowerShell-

Stop-Service MSSQLSERVER

(Use the named instance service if applicable.)

3️⃣ Start SQL Server in Single‑User Mode

There are two commonly used methods.

✅ Method A: Startup Parameter (Recommended)

1. Open SQL Server Configuration Manager
2. Go to SQL Server Services
3. Right‑click SQL Server → Properties
4. Go to Startup Parameters
5. Add: -m . Click -Add, Apply ,OK.
6. Start SQL Server service

4️⃣ Connect Using SQLCMD (Not SSMS)

Use Command Prompt (Run as Admin): Run below command

sqlcmd -S localhost

✅ This ensures you get the only connection.

5️⃣ Remove Extra TempDB Files

Now TempDB is quiet and predictable.

Run below commands in command prompt-

USE tempdb;

GO

DBCC SHRINKFILE (tempdev_extra_01, EMPTYFILE);

GO

ALTER DATABASE tempdb REMOVE FILE tempdev_extra_01;

GO

✔ This step works reliably because:

• No worktables exist
• No version store
• No background sessions
• No AG internal usage

6️⃣ Exit Single‑User Mode

1. Exit SQLCMD
2. Stop SQL Server service
3. Remove -m (or -m"SQLCMD") from startup parameters
4. Start SQL Server normally
5. Start SQL Server Agent

✅ Method B: Start SQL Server in Single‑User Mode from Command Line (Advanced)

This method is useful when:

• SQL Server is failing to start normally
• Config Manager access is limited
• You need full manual control
• You want to avoid accidental GUI connections (SSMS, monitoring tools)

This is a more hands‑on approach and should be used carefully.

🔧 Steps to Start SQL Server in Single‑User Mode via Command Prompt

1️⃣ Stop SQL Server Agent (Mandatory)

Again—this is critical.

Run below command in command prompt-

net stop SQLSERVERAGENT

Why we did it? If SQL Agent starts first, it will steal the only allowed connection, locking you out.

2️⃣ Stop SQL Server Service

Run below command in command prompt-

net stop MSSQLSERVER

(For a named instance, use the instance service name.)

3️⃣ Start SQL Server Manually in Single‑User Mode

Open Command Prompt (Run as Administrator) and navigate to the SQL Server Binn directory, for example:

cd "C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Binn"

Now start SQL Server with single‑user switch:

sqlservr.exe -m

✅ Optional (BEST PRACTICE):

sqlservr.exe -m"SQLCMD"

This ensures:

• Only SQLCMD can connect
• SSMS, monitoring agents, and background tools cannot steal the connection

📌 The command window will appear “hung” — this is normal. SQL Server is running in the foreground.

4️⃣ Connect Using SQLCMD (Different Window)

Open another Command Prompt (Admin):

sqlcmd -S localhost

✅ If the connection succeeds, you now own the only SQL Server session.

5️⃣ Remove Extra TempDB Files

Now perform the TempDB cleanup safely: Run below in command prompt

USE tempdb;

GO

DBCC SHRINKFILE (tempdev_extra_01, EMPTYFILE);

GO

ALTER DATABASE tempdb REMOVE FILE tempdev_extra_01;

GO

At this point:

• No worktables exist
• No version store activity
• No AG background sessions
• No contention

This is why this method works when others fail.

6️⃣ Shut Down and Restart Normally

1. Exit SQLCMD
2. Go back to the sqlservr.exe window
3. Press Ctrl + C to stop SQL Server
4. Start SQL Server using normal service start:

✅ Validation After Startup

Run below in ssms:

SELECT name, physical_name FROM tempdb.sys.database_files;

Confirm:

• Extra files are removed
• File count matches best practices
• Files are evenly sized

🚨 Common Pitfalls to Avoid

❌ Forgetting to stop SQL Agent

❌ Connecting via SSMS instead of SQLCMD

❌ Monitoring tools auto‑connecting

❌ Running this during peak business hours

❌ Not informing stakeholders

⚠️ Important Warnings (Please Read)

🚨 DISCLAIMER This post is based on real production experience—but every environment is different.

Before attempting this in PROD:

• Understand your AG architecture
• Assess failover impact
• Test in non‑production
• Coordinate with application teams
• Always have rollback steps

👉 Do more research, test thoroughly, and proceed with extreme caution.

🧠 Final takeaway

Removing extra TempDB files is not just a storage task— in Always On AG, it’s:

• An availability decision
• A restart decision
• A workload timing decision

✅ Choose the approach based on risk appetite, not convenience.

If this saves even one DBA from a production scare—that’s a win.

#SQLServer #AlwaysOn #TempDB #DBAExperience #ProductionLessons #HighAvailability #DataPlatform