T-log backups with transactional replication.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Replication (2005)

T-log backups with transactional replication.

Author

Topic

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2008-02-26 : 13:36:15

We have a system here using transactional replication and we are experiencing quite a bit of latency, I looked into it and ruled out the network. What I did see is that the transaction log is only getting backed up at end of day with thae full backup and it is currently at 33 GB physical and 99% used. I would like to implement more frequent transaction log backups but I am not very familiar with replication and I am not sure the proper way to do this.
Do I need to coordinate the backups of the transction log across both servers, are there any special considerations when setting this up?

Future guru in the making.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-02-26 : 13:43:08

How high is your transaction rate? Latency depends on Network Bandwidth. Transaction log back has no relation with Transactional Replication.It has log reader agent which detects the changes and distribution agents distribute.

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2008-02-26 : 13:45:22

How do I determine the transaction rate? If all of the logical space is used up in the transaction log and it has to autogrow would this not contribute to latency?

Future guru in the making.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-02-26 : 14:02:55

Check transaction log usage with this:

DBCC SQLPERF(logspace)
--
DBCC LOGINFO
--
DBCC OPENTRAN

This procedure will also give info:
CREATE PROC dbo.spSQLPerf
AS
DBCC SQLPERF(logspace)
GO

CREATE TABLE dbo.logSpaceStats
(
id INT IDENTITY (1,1),
logDate datetime DEFAULT GETDATE(),
databaseName sysname,
logSize decimal(18,5),
logUsed decimal(18,5)
)
GO

CREATE PROC dbo.spGetSQLPerfStats
AS
SET NOCOUNT ON

CREATE TABLE #tFileList
(
databaseName sysname,
logSize decimal(18,5),
logUsed decimal(18,5),
status INT
)

INSERT INTO #tFileList
EXEC spSQLPerf

INSERT INTO logSpaceStats (databaseName, logSize, logUsed)
SELECT databasename, logSize, logUsed
FROM #tFileList

DROP TABLE #tFileList
GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-02-26 : 21:13:07

You do not need to coordinate your backups across servers.

We run full backups daily and tlog backups every 15 minutes. We also use transactional replication and database mirroring.

I disagree with sodeep about transactional replication not having anything to do with backups. This is not true. In fact you can't backup the data in the tlog until it has been replicated to the subscriber.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2008-02-27 : 11:37:16

quote:
Originally posted by tkizer

You do not need to coordinate your backups across servers.

We run full backups daily and tlog backups every 15 minutes. We also use transactional replication and database mirroring.

I disagree with sodeep about transactional replication not having anything to do with backups. This is not true. In fact you can't backup the data in the tlog until it has been replicated to the subscriber.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Thanks Tara, this was the info I was looking for.
Cheers.

Future guru in the making.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources