October 12, 2011 at 8:31 pm
I have a situation where a large set of processes occurs once a month and causes the transaction log to consume all available disk space. One consideration for easing this is to create a secondary log file on another drive.
My question is: If a database has 2 ldf files and they need to autogrow how will this occur? Will the active log grow or will both files grow by equal ammounts?
October 12, 2011 at 9:59 pm
SQL Server does not stripe across multiple transaction log files. SQL Server uses the transaction log files sequentially.
It is better to have a single transaction log file in a larger disk. RAID can be used to improve the IO performance.
October 12, 2011 at 10:03 pm
I understand that the files are use serially and that the ideal situation would be to increase disk space, however I need other options to present to the decision makers. Normal activity with the single log is fine but end of month processing blows out the log and causes disk issues. One option is to have a second log file on a different disk - not for any other reason than to provide a larger transaction log. My question is if this log hits autogrowth thresholds how and on whcih files will that growth occur?
October 13, 2011 at 1:44 am
SQL Server uses a proportional fill strategy across all the files within each filegroup and writes an amount of data proportional to the free space in the file. This enables the new file to be used immediately. In this way, all files generally become full at about the same time. However, transaction log files cannot be part of a filegroup; they are separate from one another. As the transaction log grows, the first log file fills, then the second, and so on, by using a fill-and-go strategy instead of a proportional fill strategy. Therefore, when a log file is added, it cannot be used by the transaction log until the other files have been filled first.
Adding and Deleting Data and Transaction Log Files
October 13, 2011 at 1:46 am
That's what I needed. Thanks 🙂
October 13, 2011 at 3:29 am
That's what I needed. Thanks 🙂
Happy to Help! 🙂
October 13, 2011 at 11:17 am
You might want to take a log backup before and after the process.
October 13, 2011 at 2:06 pm
In my experience it's best never to present so-called, "decision makers" with anything but the right decision(s)!
October 13, 2011 at 2:11 pm
tnk (10/13/2011)
In my experience it's best never to present so-called, "decision makers" with anything but the right decision(s)!
And IMNSHO, the right decision would actually be to move the current transaction log to a larger disk, separate from the disk(s) with the data that can accommodate the t-log during all processing times.
October 13, 2011 at 2:19 pm
Lynn Pettis (10/13/2011)
tnk (10/13/2011)
In my experience it's best never to present so-called, "decision makers" with anything but the right decision(s)!And IMNSHO, the right decision would actually be to move the current transaction log to a larger disk, separate from the disk(s) with the data that can accommodate the t-log during all processing times.
🙂
That would be the opton that I would choose too, and is the option I have recommended. However I have been asked, for better or worse, to enumerate any other options - all of which have issues.
-second log file. (this was where I wanted to be sure about controlling any growth that might occur)
-switching to simple before monthly processing, back to full followed by a full backup afterwards.
-increased frequency of log backups during the window.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply