transactional Replication- Two log files

Question

Post reply

transactional Replication- Two log files

Dhruva_51

SSChampion

Points: 10521
More actions
December 7, 2012 at 11:39 am

#285975

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?

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Dhruva_51 SSChampion Points: 10521 More actions · Answer 1

Dhruva_51

SSChampion

Points: 10521

December 11, 2012 at 8:27 am

#1567468

:Whistling:

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

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, MVP, M.Sc (Comp Sci)
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

Dhruva_51 SSChampion Points: 10521 More actions · Answer 3

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.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

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, MVP, M.Sc (Comp Sci)
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

Ramasankar Molleti Ten Centuries Points: 1044 More actions · Answer 5

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

Dhruva_51 SSChampion Points: 10521 More actions · Answer 6

I also agree with Gila. But the problem here is I am not observing the sequential increase in the file growth.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

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, MVP, M.Sc (Comp Sci)
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

Dhruva_51 SSChampion Points: 10521 More actions · Answer 8

Gila,

It's not working like that in our environment. Please find the attached document for the log sizes captured at different times.

Thanks,

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

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, MVP, M.Sc (Comp Sci)
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

Dhruva_51 SSChampion Points: 10521 More actions · Answer 10

I am sorry. There's no auto shrink enabled or no shrink job. There's log shipping setup on this database. The values captured at 9:53 AM clearly says both the files have almost 148 GB of free space. But the values which captured at 9:55 AM clearly states both the files have a short of 1 GB. Now when you said log files grow one after the other, why we are seeing a reduce of 1 GB in both the files???????????

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

When the log gets full, they will both grow. If the log is not full, then the files aren't going to grow. If you have 148 GB free in the file, the log files are not going to grow, SQL reuses the space in the file.

If the log files on disk have got smaller, then there is a shrink job, autoshrink or someone manually shrinking, as there is no other way that files get smaller.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Dhruva_51 SSChampion Points: 10521 More actions · Answer 12

As far as I know backup log truncates the log and we can reuse it. Log truncation cannot occur during a backup or restore operation or if we have replication configured on the database and if there are transactions that need to be transferred to the subscriber, then it will not truncate the log.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

muthyala_51 (12/14/2012)
As far as I know backup log truncates the log and we can reuse it.

Correct. Truncation meaning 'mark 0 or more portions of the log reusable', it does not reduce the file size.

Log truncation cannot occur during a backup or restore operation or if we have replication configured on the database and if there are transactions that need to be transferred to the subscriber, then it will not truncate the log.

Not quite. Log records that still need to be replicated will prevent the VLF they are in from being truncated, but other VLFs may be truncated. That's why I say '0 or more VLFs get marked as reusable'

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Dhruva_51 SSChampion Points: 10521 More actions · Answer 14

I am really sorry if I missed something or I might not be able to convey the exact point. I never said there's a reduction in the file size. My question was related to the growing of two log files. Even it has the free space available in the first log file, why the second log file is also growing simultaneously. Please check the attached document. When compared to yesterday's both the files are of same size(149 GB). But if you check the free space available it's 146 now (it was 147 before). The free space varies as there will be truncation of the log size because of log backup as discussed previously. I added space used column also now. Why almost the same numbers are appearing in both the log files.