2

I am using SQL server 2019 AG.

I am facing an issue after moving temp DB files from one drive to another after locating the file paths by this script:

    Use master
    GO
    
    SELECT 
    name AS [LogicalName]
    ,physical_name AS [Location]
    ,state_desc AS [Status]
    FROM sys.master_files
    WHERE database_id = DB_ID(N'tempdb');
    GO

there were 9 files

then I use to move them :

 USE master;
    GO
    
    ALTER DATABASE tempdb 
    MODIFY FILE (NAME = tempdev, FILENAME = 'T:\MSSQL\DATA\tempdb.mdf');
    GO
    
    ALTER DATABASE tempdb 
    MODIFY FILE (NAME = templog, FILENAME = 'T:\MSSQL\DATA\templog.ldf');
    GO
 

   ALTER DATABASE tempdb
MODIFY FILE (NAME = temp2, FILENAME = ‘T:\MSSQL\DATA\tempdb_mssql_2.ndf’);
GO

I run this for all 8 files, after that I run the first script again I did not find the rest of the files I just found tempdev, and templog . and I run the moving script again :

"MODIFY FILE failed. File 'temp2' does not exist" 

so I lost all the rest of the 9 files !! 7 files are missing

  • is there any way I can reattach these files?
  • is my SQL server will work fine with only 2 files log and DB?
3
  • 2
    Did you restart the instance after the move script in order to create the new tempdb files?
    – Dan Guzman
    May 3, 2022 at 12:07
  • Agree with Dan. Not doing a re-start after the move script would perfectly explain the posted behaviour. May 3, 2022 at 13:10
  • I did, I restarted the SQL service
    – Tala
    May 8, 2022 at 7:26

1 Answer 1

3

You also need to check tempdb.sys.database_files. It's possible for there to be metadata mismatches between it and sys.master_files.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.