Investigating what makes my log file grow and grow and grow

I need to learn the steps to investigate which
transactions are not getting replicated, thus causing my
log file to grow. I periodically check the dbs I
administer and wait until after hours to force
replacation done using sp_ReplDone, followed by a backup
and shrink, for those dbs with excessively large log
files. I only have this problem on the server that acts
as distributor and subscriber.
I even set up jobs that do the following, in order (by
start time):
1)exec sp_ReplDone @.xactid = NULL, @.xact_segno = NULL,
@.numtrans = 0, @.time = 0, @.reset = 1
2)Backup transaction log (part of maintenance plan)
3)Backup db (part of maintenance plan)
4)DBCC Shrinkfile
I have these jobs spread out every 1/2 hour to give them
plenty of time to complete before the next one begins.
Yet the log file doesn't shrink.
When I run DBCC OPENTRAN on the db, I get the following:
Transaction information for database 'Database1'.
Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (570647:21697:1)
DBCC execution completed.
So, my two requests are:
1) why doesn't my shrink procedure work? (If I do these
steps by hand I can shrink the log file down to 1MB)
2) how do I determine what's causing the hangup that
starts the log file to grow.
Roger.
When you get a value for DBCC Opentran Oldest non-distributed LSN it means
there are transaction in your tlog that the log reader has not read yet, or
has not marked as replicated.
There is a problem with your log reader. I can't tell you what it is,
perhaps there is an error message on it. Perhaps your system is under load
and not enough resources are avaliable for the log reader - this is only
solved by a reboot.
I would never run sp_repldone the way you are. Basically what you are in
effect doing is saying I need all commands replicated for a time period, and
then when I run sp_repldone if there are any commands in the log, throw them
away.
It could also be that you are not dumping your log frequently enough. You
should be dumping it very frequently, perhaps every 20 minutes, or even
every 1-3 minutes if your database is large.
Pruning your tlog this way will improve overall system performance, and mean
your log reader has less log to read, hence its read performance will be
better. You might want to decrease its PollingInterval to 1s.
Also run a dbcc loginfo. Ideally you should have a small number of rows
which correspond to your virtual log files, ie 16 or so. Best of all 4. If
you have a large number you should try to size your tlog to something large
to prevent frequent autogrows.
Another factor which could cause your log to grow is orphaned transactions.
DBCC opentran should show this.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Roger Denison" <anonymous@.discussions.microsoft.com> wrote in message
news:19be01c4bdd2$48a792e0$a401280a@.phx.gbl...
> I need to learn the steps to investigate which
> transactions are not getting replicated, thus causing my
> log file to grow. I periodically check the dbs I
> administer and wait until after hours to force
> replacation done using sp_ReplDone, followed by a backup
> and shrink, for those dbs with excessively large log
> files. I only have this problem on the server that acts
> as distributor and subscriber.
> I even set up jobs that do the following, in order (by
> start time):
> 1)exec sp_ReplDone @.xactid = NULL, @.xact_segno = NULL,
> @.numtrans = 0, @.time = 0, @.reset = 1
> 2)Backup transaction log (part of maintenance plan)
> 3)Backup db (part of maintenance plan)
> 4)DBCC Shrinkfile
> I have these jobs spread out every 1/2 hour to give them
> plenty of time to complete before the next one begins.
> Yet the log file doesn't shrink.
> When I run DBCC OPENTRAN on the db, I get the following:
> Transaction information for database 'Database1'.
> Replicated Transaction Information:
> Oldest distributed LSN : (0:0:0)
> Oldest non-distributed LSN : (570647:21697:1)
> DBCC execution completed.
> So, my two requests are:
> 1) why doesn't my shrink procedure work? (If I do these
> steps by hand I can shrink the log file down to 1MB)
> 2) how do I determine what's causing the hangup that
> starts the log file to grow.
> Roger.

Invalid object name 'tsvw_

Hi Everyone!

Is someone here can help me on this?

I receive a error when inserting or Updating row in a replicated table

the error is Invalid object name 'tsvw_*****

What to do with this

More detail please... What version of SQL server are you using? What type of replication are you using? and etc...|||

Sorry, here some more

I'm using MSDE version of SQL 2000, and 3 layer merge replication, on a WAN

|||when you say three layers, do you mean you have a republishing scenario? "tsvw_*" is a view used by merge replication, my guess is that it may have been dropped somewhere during your setup. If you're doing a republishing scenario, make sure you set up A to B first, then republish B to C.

Invalid object name 'dbo.MSreplication_queue'.

I am attemption to update a record in a SQL 2005 table that is trans.
replicated with updates and I am receiving the following message...
Msg 208, Level 16, State 1, Procedure sp_MSsendtosqlqueue, Line 40
Invalid object name 'dbo.MSreplication_queue'.
Any ideas?
AHIA,
Larry...
Does the table dbo.MSreplication_queue exist in the subscribing database ?
In updatable scription scenerio, SQL Server fires ins/upd/del triggers which
will insert information into that table when a DML command is executed on the
replicated tables. So this table is one of the critical tables for the queue
replication to work.
I can think of only 2 reasons why you might be running into this error :
1. The table dbo.MSreplication_queue has been deleted. To see if the table
was dropped, take a look at the default trace report for the database "Schema
Changes Histroy" in the Management Studio.
or
2. The snapshot did not get applied to the subscriber successfully. Check
the distribution agents histroy to see if there has been any failures.
"LPR-3rd" wrote:

> I am attemption to update a record in a SQL 2005 table that is trans.
> replicated with updates and I am receiving the following message...
> Msg 208, Level 16, State 1, Procedure sp_MSsendtosqlqueue, Line 40
> Invalid object name 'dbo.MSreplication_queue'.
>
> Any ideas?
> AHIA,
> Larry...
>
|||What is the compatibility level of this database?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"LPR-3rd" <lreames@.gmail.com> wrote in message
news:1168541651.977581.286190@.i56g2000hsf.googlegr oups.com...
>I am attemption to update a record in a SQL 2005 table that is trans.
> replicated with updates and I am receiving the following message...
> Msg 208, Level 16, State 1, Procedure sp_MSsendtosqlqueue, Line 40
> Invalid object name 'dbo.MSreplication_queue'.
>
> Any ideas?
> AHIA,
> Larry...
>

Invalid Object Name ctsv_####...

DB Gurus,

I have a VB 6 utility that is using ADO to connect to replicated tables and I get the following error when I try to update the recordset:

Invalid Object Name 'ctsv_18C3929C22...'

This code worked great until the team that owns one of our SQL servers decided to turn of Replication. After getting replication back up and running, we set up a push subscription rather than a pull as before.

The util is running against the subscriber's tables...not the source tables.

What the heck have I gotten into here?

What does ctsv mean?

Thanks,
GregGood question, what the heck is it? Also make sure you fully qualify table names with schema owner. Maybe that's what it's barking at...

Invalid descriptor index

Good day
I have a simple transactional replication set up between two SQL2000
servers. A single table is replicated with columns of type int, bit and
varchar. The server with the subscription has had SP3 for quite some time,
however after we installed SP3 on the publishing server, we started receiving
an "Invalid descriptor index" error when trying to start the distribution
agent on the publishing server.
Does anyone have an idea why this would happen and how to fix this problem?
Thanks
I have removed the replication and set it up again, however I am still
receiving the invalid descriptor index error...?
"Pieter" wrote:

> Good day
> I have a simple transactional replication set up between two SQL2000
> servers. A single table is replicated with columns of type int, bit and
> varchar. The server with the subscription has had SP3 for quite some time,
> however after we installed SP3 on the publishing server, we started receiving
> an "Invalid descriptor index" error when trying to start the distribution
> agent on the publishing server.
> Does anyone have an idea why this would happen and how to fix this problem?
> Thanks

Invalid character value for cast specification error

I'm using Access 2K via ODBC to replicated SQL Server 2K. In some tables (not all) when I try to add a record either with a form or directly in the datasheet I get this error message and all form controls/table cells display '#Name?'. The record is added
and displays correctly if I close and reopen the form/table. I can add records with no problem in Enterprise Manager.
Any help gratefully received.
Jonathan Attree
Hi, I am getting exactly the same problem although this problem has only occurred since I implemented merge replication. Does anyone have an answer?
Amanda