CODEWITHSUNDEEP
sql-serverdatabasesql-server-2005file
Vendoran
Vendoran

Reputation: 176

SQL Server 2005 data file inconsistency (maybe 8 data files, maybe 5, who knows)

I have a SQL Server 2005 sp2 box where tempdb has either 8 data files or 5 data files dependingo n where you look. DBCC showfilestates and sys.database_files (both queried in tempdb) show 8 data files (tempdev - tempdev8), however when I query sys.master_files (in master db, which is also what the GUI uses), I only see 5 (tempdev, tempdev5 - tempdev8). I cannot add a tempdev2 - tempdev4 as I get an error that it already exists and I can't restart the SQL Server anytime soon to recreate tempdb.

Anyone know: 1.) how this can happen? 2.) more importantly, how I can correct it?

Thanks,

Upvotes: 0

Views: 471

Answers (3)

Mitch Schroeter
Mitch Schroeter

Reputation: 1001

It's possible that one view is showing outdated information. One scenario is that those files were dropped from tempdb but SQL Server has not been restarted to delete those files our their entries from some of the system catalog. And you can't add new ones until the old ones are really gone. Anything destructive you do to tempdb (moving, dropping files, etc.) usually requires a restart of SQL Server.

My guess is they are showing two different states of the system and will reconcile one the instance restarts.

Upvotes: 1

GilaMonster
GilaMonster

Reputation: 1768

If you look on disk in the appropriate directory, how many files do you see?

Upvotes: 0

Light
Light

Reputation: 144

The first question I have to ask is, why are you messing with tempdb? Are you experiencing bottlenecks because of tempdb?

Upvotes: 0

Related Questions