patternsqlMinor
How bad is a tempdb drive failure (with multiple tempdb files on different drives)?
Viewed 0 times
tempdbwithbaddifferentfilesmultiplehowdrivedrivesfailure
Problem
I'm getting a new 16 core server for my SQL Server 2005 database. I would like to create 16 files for my temp DB, and put each one on a separate drive. If one of these drives fails, can SQL Server continue to function using the other 15 files?
Solution
Nope, I'm fairly confident SQL Server will fall over - I've had a few SAN attached drives disappear with SQL Server 2005. It's possible that SQL Server may not realise until it accesses TempDB on the drive that's failed.... in pre-prod, you should be able to simulate this by dropping the drive.
Unless you've carefully assessed this, I would also suggest you carefully reconsider your 16 TempDB datafile approach. The smart advice these days is to add TempDB datafiles only when you see TempDB contention (wait stats etc). If you really want to, start with 2 datafiles on the same drive.
The most recent TempDB recommendation I saw - it's Paul Randal, so essentially Mr SQL Server himself:
http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(1230)-tempdb-should-always-have-one-data-file-per-processor-core.aspx
Unless you've carefully assessed this, I would also suggest you carefully reconsider your 16 TempDB datafile approach. The smart advice these days is to add TempDB datafiles only when you see TempDB contention (wait stats etc). If you really want to, start with 2 datafiles on the same drive.
The most recent TempDB recommendation I saw - it's Paul Randal, so essentially Mr SQL Server himself:
http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(1230)-tempdb-should-always-have-one-data-file-per-processor-core.aspx
Context
StackExchange Database Administrators Q#2599, answer score: 4
Revisions (0)
No revisions yet.