Here's my situation: I have a SQL Server 2012 installation with a relatively small (185GB) SSD volume, and a large (3TB) RAID10 spinning disk volume. I currently have tempdb data and log files on the SSD, as well as the transaction logs for all user databases. One database is very large and does massive batch processing; it collects network switch metrics every 10 minutes and loads it into the database. The database is set to SIMPLE logging.
The app owners recently added a second collector node, causing the # of active transactions to skyrocket. This in turn resulted in the transaction logs filling up the SSD drive, causing the user database and tempdb to be unavailable due to insufficent space.
I added a log file to tempdb and the user database on the RAID10 volume to get both databases available again.
I've read Multiple Transaction Log files and performance impact, and especially Mike Fal's answer to it. But, I'm confused about a few things in Mike Fal's answer.
Once it does, it will attempt to return to the first available VLF in the file. If one is not available, the log file will then grow.
I read the answer as "try to re-use a VLF in the current file, and then try to grow the current file, and if the current file is able to grow, don't switch files".
Is there a way to indicate to SQL Server to prefer the re-use of the log files on the SSD, if available, rather than grow the file on the spinning disks? Ideally I'd like to set the files on the SSD to almost-filling-the-disk, and turn auto-growth off, and then only use the files on the spinning disks as overflow when necessary - and let the files on the spinning disks grow as needed, as I have 2.5TB+ free on that volume.
As I read it though, if I set the files on the spinning disks to auto-grow, I won't ever fall back to the files on the SSD - I have to set a hard cap on the files on the spinning disk volume in order to someday fall back to the files on the SSD. Due to the architectural change, I'm not sure how large the transaction logs will need to grow; the current hardware was sized for one collector node, and they didn't inform me they were adding a second collector node until after the disk filled.