Multiple Transaction log files, Pros & Cons…


There is no performance benefit having multiple log files…We just need to make sure, that growth is set correctly. 

However, there is no negative impact as well, sometime due to space issue, we may have to add another log file to different location, just analyse in case if any high availability is implemented, specially for DB Mirroring. 

I would even prefer to reduce the number of VLFs (virtual log files) DBCC LOGINFO, that exist within transaction log file, then resizing the log file to something reasonable intial size, understanding the overall need. 

The another important key thing is to configure Tempdb data files based on number of CPU, no matter how MAXDOP is configured. 

When we talk about database’s data file, each file has its own independent NT thread. Hence, SQL server writes the data in parallel and allocate the extents proportionately unless restricted, considering a fact that data files must be on same filegroup.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: