January 18, 2016 at 2:40 am
Hi
Our databases currently have all the log files and database files (system and customer) on one drive.
I am in the process of setting up a Change request to get the log files and data files separated onto different drives,keeping in line with sql server best practices.
I also am going to move the 4 Tempdb files onto its own separate RAID 1 disc array away from customer database files.
My question is:
Can I keep the transaction log file for Tempdb on the same disc setup as where I will be putting the customer transaction log files, as I'm thinking about cost, and ideally just want to limit the amount of discs we have to pay for with our hosting provider.
January 18, 2016 at 2:51 am
Hi,
Always held tempdb and logs on the same drive and never found it to have an issue. Although it's always a case of depends.....
January 18, 2016 at 6:02 am
When using spinning disk, then ideally the transaction log should be on its own disk. If you have multiple heavy used databases, then ideally have a separate disk for each log.
The reason is that the transaction log is written sequentially, and this is the only write that is not asynchronous - i.e. it has to complete before SQL Server can commit a transaction. When a transaction log has a dedicated drive, the disk head does not move to other places in between writes, so SQL Server does not have to wait for the head to move back in position when writing the next block.
Obviously, the ideal solution can cost a lot of money if you have lots of busy databases. That's where the process of weighing pros (better performance) vs cons (more money) starts.
For recoverability, I do recommend strongly against combining data file and transaction log for the same database on the same (physical) disk. If a disk is lost and you use either data or log file, you can usually recover (almost) all data. If both are lost, you will lose at least everything that has changed since the last backup - and even more if you have the backup on the same drive as well.
January 18, 2016 at 6:15 am
Hugo Kornelis (1/18/2016)
For recoverability, I do recommend strongly against combining data file and transaction log for the same database on the same (physical) disk. If a disk is lost and you use either data or log file, you can usually recover (almost) all data. If both are lost, you will lose at least everything that has changed since the last backup - and even more if you have the backup on the same drive as well.
For user DBs, definitely. For TempDB, I'll often put them on the same drive if I'm short of independent drives, if for nothing else than to keep the TempDB load separate from the user DBs.
That assumes the drives are independent and not just different LUNs of the same array of the same SAN.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 18, 2016 at 7:43 am
For TempDB, I'll often put them on the same drive if I'm short of independent drives, if for nothing else than to keep the TempDB load separate from the user DBs
Gail - By this you mean you keep Tempdev1, Tempdev2,TempdevX.........Templog on the same drive ? If that is the case I can go with that solution rather than keep the TempLog file on the newly created drive for the user LDF files.
Also, regarding RAID levels. I am thinking about putting tempdb on a RAID1 mirrored set and the same for the user database transaction log files (ideally I'll get RAID10 for both if the business will sign it off but I doubt it !!).
Somebody mentioned that putting tempdb on a RAID1 was bad - saying that if one disc goes down we loose the whole instance, which I said was nonsense as RAID1 offers protection against 1 disc failing! (I think there may be some performance degradation as a new disc is placed back into the mirror but we wont loose the instance - is this correct ?)
January 18, 2016 at 8:48 am
Providing TempDB's not under IO contention already.
RAID 1 is a mirror. Two drives, each with full copy of the data. Either can fail with no loss of data. I wouldn't recommend RAID 1 for TempDB though, unless it's two SSDs. Two drives are unlikely to have enough spindles to give good throughput, unless you have minimal load on the server. RAID 10 with multiple drives is better,
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 18, 2016 at 10:18 am
ok thanks very much.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply