1

We have tempdb on its own drive array split into several files for performance. We have DB logs files on non-data drives. We question if we should put the tempDB log file on the log drive for all DB's or elsewhere, but separated from the temdb data files.

sysadmin1138
  • 133,124
  • 18
  • 176
  • 300
David
  • 11
  • 1
  • 2

4 Answers4

3

I've never worried about this. Taking a look at MS' site, it says that logging for tempdb is minimized. It may likely be a non-issue for you. And of course, it's not a good idea to prematurely optimize - i.e. before testing your current environment under realistic load. There's no single best-practice for SQL configs for all conditions.

mfinni
  • 36,144
  • 4
  • 53
  • 86
0

Are we talking about a binary transaction log or just a plain text application log file? General best practice for database workloads is to keep your transaction logs on different physical disk from your data store; this keeps sequential writes sequential on the transaction log, and improves cache hit rate on both volumes by minimizing cache churn.

Jeff Albert
  • 1,987
  • 9
  • 14
  • Understood and appreciated. We do keep data and logs on diff drives. I was considering if temdb's log file should reside with the other logs or at least off the tempdb data file drive. – David Mar 24 '11 at 20:28
  • 1
    he's talking about the SQL Transaction Log file for the tempdb. – mfinni Mar 24 '11 at 20:57
0

The traditional view is that user databases' data files should be on one drive, user databases' log files on another drive (this is the drive that really needs to have fast latencies), and TempDB on a separate drive. Aside from isolating them from each other, logs and datafiles have very different IO characteristics.

Things changed with the appearance of SANs, as SAN admins typically gave DBAs carved out parts of large chunks of disc - performance differences weren't so apparent.

Virtualisation has changed things further, as the DBA might have thought he'd separated his datafiles from his logs from his TempDB, but the VM admin put all those different "drives" on the same physical drive - in a VM world, those drives are just files.

My view is that it's still good to keep datafiles, log files, and TempDB's files separate - if one of them bloats, you hopefully get a bit more time to fix it - and it won't take down everything.

If you can do it, the best practice is still to keep your datafiles and log files separate - and if you can keep TempDB separate too, then great. Some applications, especially using Read Committed Snapshot Isolation, use TempDB heavily.

Peter Schofield
  • 1,639
  • 9
  • 11
0

Yes, you would better to put TempDB log and data files in separate drives. as usual we are placing Data files for all DB|'s and log files of all DB's in separate drives as transactions writes in both the files at same time. so the same concept applicable for TempDB files also.

asd
  • 1