<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2008
»
SQL Server 2008 High Availability
»
transactional Replication- Two log files
18 posts, Page 1 of 2
1
2
»»
transactional Replication- Two log files
Rate Topic
Display Mode
Topic Options
Author
Message
muthyala_51
muthyala_51
Posted Friday, December 07, 2012 11:39 AM
SSC Veteran
Group: General Forum Members
Last Login: Today @ 4:00 AM
Points: 229,
Visits: 1,064
For some reason we have two log files on one of our production database. We have transactional replication setup on it. We are experiencing a latency issue during index rebuild/heavy inserts/ heavy deletes operation. I think it's expected. My question is as there will be a increase in the log file size during the above operations, both the logs are growing at the same rate. Not sure why. As log files are written sequentially why both the log files are growing at the same rate? Does the log reader agent tries to search for replicated transactions in both the log files?
Post #1394183
muthyala_51
muthyala_51
Posted Tuesday, December 11, 2012 8:27 AM
SSC Veteran
Group: General Forum Members
Last Login: Today @ 4:00 AM
Points: 229,
Visits: 1,064
Post #1395153
GilaMonster
GilaMonster
Posted Tuesday, December 11, 2012 8:48 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 7:54 AM
Points: 37,660,
Visits: 29,912
SQL will grow one then the other, that's why you see them both growing. They're still used sequentially, the log reader reads sequentially (by LSN), all writes are sequential. There's no advantage to having 2 log files.
Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #1395164
muthyala_51
muthyala_51
Posted Tuesday, December 11, 2012 8:50 AM
SSC Veteran
Group: General Forum Members
Last Login: Today @ 4:00 AM
Points: 229,
Visits: 1,064
But the log is set to grow to 2TB. How come it's not growing to 2TB and then taking over the second log file.
Post #1395166
GilaMonster
GilaMonster
Posted Tuesday, December 11, 2012 9:02 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 7:54 AM
Points: 37,660,
Visits: 29,912
Because that's not the way SQL is designed to work with its files. If there are two log files, both will fill up, that will trigger the autogrow and they'll both grow.
As I said, you don't need that second log file, better to find some time when that log is idle and remove it.
Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #1395179
sankar276
sankar276
Posted Tuesday, December 11, 2012 1:12 PM
SSC Journeyman
Group: General Forum Members
Last Login: Thursday, May 16, 2013 10:05 PM
Points: 99,
Visits: 98
I agree with gail you will not get any gain if the database have more than one log file. Sql server writes and fills transaction log the file sequentially where as in the case of data file it will perform proportional fill.
http://www.sqlskills.com/blogs/jonathan/post/looking-at-multiple-data-files-and-proportional-fill-with-extended-events.aspx
RamaSankar
MCTS,MCITP Sql Server 2008
Post #1395298
muthyala_51
muthyala_51
Posted Tuesday, December 11, 2012 1:40 PM
SSC Veteran
Group: General Forum Members
Last Login: Today @ 4:00 AM
Points: 229,
Visits: 1,064
I also agree with Gila. But the problem here is I am not observing the sequential increase in the file growth.
Post #1395308
GilaMonster
GilaMonster
Posted Tuesday, December 11, 2012 1:50 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 7:54 AM
Points: 37,660,
Visits: 29,912
As I already explained, SQL will fill one file, then fill the other, and if there's no reusable space, both will grow.
Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #1395312
muthyala_51
muthyala_51
Posted Thursday, December 13, 2012 7:59 AM
SSC Veteran
Group: General Forum Members
Last Login: Today @ 4:00 AM
Points: 229,
Visits: 1,064
Gila,
It's not working like that in our environment. Please find the attached document for the log sizes captured at different times.
Thanks,
Post Attachments
Capture.JPG
(
15 views,
23.29 KB
)
Post #1396202
GilaMonster
GilaMonster
Posted Thursday, December 13, 2012 8:08 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 7:54 AM
Points: 37,660,
Visits: 29,912
What am I supposed to be seeing there, other than 2 files of the same size?
You said in your initial post "both the logs are growing at the same rate. " I've explained repeatedly why both log files will grow at the same rate.
p.s. If the files reduced in size you have a shrink job or autoshrink on.
Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #1396211
« Prev Topic
|
Next Topic »
18 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
