2

If I have 4 tempdb files split across 2 drives with the log on a third drive, is that necessary/helpful?

I want to persuade my manager to put all 4 tempdb files on one drive. If one of those tempdb files became corrupt, e.g. file D on drive B, would file A and B on drive A and file C on drive B continue to function, would tempdb 'break'? I assume SQL would create another tempdb file, based on the temporary nature of tempdb.

Thanks

  • 1
    It's also relevant to consider the underlying storage. If the drives are virtual and point to the same storage array then different drives are just a management tool. A high volume of read/writes could justify each drive pointing to a separate storage array or LUN. – kevinsky Oct 7 '19 at 15:35
  • How big are the files? How much RAM is on the instance? What SQL version are you running? – James Jenkins Oct 7 '19 at 15:43
2

If I have 4 tempdb files split across 2 drives with the log on a third drive, is that necessary/helpful?

I suggest you to understand why we use multiple files for tempdb. Once you understand that clearly and know your underlying storage system (as suggested by comments) you will be able to answer the question. Things might vary depending on your SQL Server Version and major TempDB enhancements are done in SQL Server 2016, 2017 and 2019.

Tempdb contention refers to a bottleneck for threads trying to access allocation pages that are in-memory; it has nothing to do with I/O.

Read more details about this here by Paul Randal.

Microsoft's official recommendation about reducing tempdb allocation contention is here:

Recommendations to reduce allocation contention in SQL Server tempdb database

If one of those tempdb files became corrupt, e.g. file D on drive B, would file A and B on drive A and file C on drive B continue to function, would tempdb 'break'?

I am sure the answer can be depends on the type of corruption. But I do not see any relevance with this question and splitting your files into two LUNs.

I assume SQL would create another tempdb file, based on the temporary nature of tempdb.

SQL Server will NOT automatically create new files, but you can do it yourself. Will it cause downtime? It depends. Read this question and answer.

Tempdb Add files require Restart Correctly adding data files to tempdb by Paul Randal

| improve this answer | |

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.