I have a strange behaviour on one of my SQL Server's tempdb.
It has six tempdb files, according to SSMS and queries, all pointing to S directory. Each of them have ~5gb of initial size.
The weird thing is S is quorum actually with only 2gb of free space. Tempdb files simply don't exist in S nor in its sub directories.
The question is where are the tempdb files? :) How can I physically see them?
Thank you.

  • What query did you use to view the file path? Just for verification. – user507 Jan 12 '15 at 9:55
  • It is possible someone modified the file paths with the wrong drive letter and it has not physically taken effect, if no restart has occurred. – user507 Jan 12 '15 at 9:58
  • Hi. Sys.database_files is the query I run. – Stackoverflowuser Jan 12 '15 at 10:11
  • I would update it to the correct path before a failover occurs on the cluster. – user507 Jan 12 '15 at 10:52
  • 1
    Can you please check SQL Server errorlog( sp_readerrorlog) and see message related to tempdb. If all tempdb files have come online message would be in errorlog file if not there would be some error message which might give you some hint – Shanky Jan 12 '15 at 10:56

Remember that S: is relative to the server, not necessarily where you're running SSMS from. I've seen plenty of people query the location of files and find they're not actually accessible from where SSMS is running. If S: is part of a user profile, then it could be associated with the service account even.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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