Replication and uniqueidentifier

Abdul,
it's better not to use GUIDs for PKs as they are too
large (4x the size of an integer).
Identity columns can be used in merge replication - you
can enable sql server to maintian the ranges to ensure
they never overlap. Have a look at the article properties
of a table containing identity columns before it is
subscriber to, and there is a checkbox on the identity
range tab. The easiest way is tp ensure that the ranges
are so wide that there will never be any need for them to
be updated.
HTH,
Paul Ibison (SQL Server MVP)[vbcol=seagreen]
Thank you Paul for your reply. I used int primary key to avoid using GUID
because of speed issues but did not foresee that we would use replication
later on. Since there is a limit on using a integer key there is a possiblity
that all the numbers could get taken as there are a lot of rows, and many
rows get inserted daily.
Maybe a string key could work but GUID's are guaranteed to be unique
according to Microsoft. Maybe I can alleviate the speed issue by using a
combination of indexes and selecting only those records which need to be
viewed in the front end. This was the plan but I wanted to make sure that
GUID columns would not be affected when implementing merge replication as I
thought it adds its own guid column.
-Abdul Rauf
"Paul Ibison" wrote:

> Abdul,
> it's better not to use GUIDs for PKs as they are too
> large (4x the size of an integer).
> Identity columns can be used in merge replication - you
> can enable sql server to maintian the ranges to ensure
> they never overlap. Have a look at the article properties
> of a table containing identity columns before it is
> subscriber to, and there is a checkbox on the identity
> range tab. The easiest way is tp ensure that the ranges
> are so wide that there will never be any need for them to
> be updated.
> HTH,
> Paul Ibison (SQL Server MVP)
>
|||Abdul, integers go from -2billion to 2 billion so you're only going to have
a problem if the data spans >4billion!
If that is the case, you could partition the data using a composite key,
with one column as a subscriber identifier.
HTH,
Paul Ibison (SQL Server MVP)[vbcol=seagreen]

Replication and Log Size problems

We have our main database which is about 60 GB now. I
typically have the log size at 1 GB.
The DB is replicating two publications to two different
servers.
It appears that re-dexing is causing our log file to grow
too large and then the replication log reader cannot read
it.
In order to get the log down to normal size again, I have
to do the following. Kill one of the publications
because two log readers are reading the log.
1) EXEC sp_repldone
2) BACKUP LOG Prod_iVend
WITH TRUNCATE_ONLY
EXEC sp_repldone
Jay,
to remove the inactive part of the log you can backup the log, assuming the
logreader has read the records. So, to reduce the log size I'd increase the
frequency of your log backup. You shouldn't have to remove the publication
to be able to reduce the log file size. In what way is the log reader
failing. If it is a timeout, you can increase the -QueryTimeOut value. You
might also consider fixing a value for the -ReadBatchThreshold.
HTH,
Paul Ibison
|||It appears that re-indexing is causing the log to grow so
much. We have
broken up reindexing so that only a few large tables are
done each night
to minimize the impact.
What happens is that the log grows to a size that the log
reader cannot
suck it in and then we get these erros.
The process could not
execute 'sp_repldone/sp_replcounters' on '(server
name)'.
Another log reader is replicating the database.
(Source: (server name) (Data source); Error number: 18752)
The process could not set the last distributed transaction.
(Source: (server name) -(job name) (Agent); Error number:
22017)
Because there are two publications coming from the DB, it
can only be
fixed by removing one of them completely. I cannot shrink
the log until
I do that.
Any ideas?

>--Original Message--
>Jay,
>to remove the inactive part of the log you can backup the
log, assuming the
>logreader has read the records. So, to reduce the log
size I'd increase the
>frequency of your log backup. You shouldn't have to
remove the publication
>to be able to reduce the log file size. In what way is
the log reader
>failing. If it is a timeout, you can increase the -
QueryTimeOut value. You
>might also consider fixing a value for the -
ReadBatchThreshold.
>HTH,
>Paul Ibison
>
>.
>
|||Assuming nobody is running sp_replcmds in QA, then the other log reader
being referred to is the other transactional publication. Try stopping one
log reader and set the other one off, so as to stagger the processes. Once
one has finished reading, run the pther then backup and shrink.
Also change the 2 parameters mentioned earlier.
HTH,
Paul Ibison
|||whenever you run sp_replcmds yourself you MUST run a sp_replflush before running it or you will get errors like this.
The only clean way of fixing the multiple log reader agent problem is to stop the SQL Server agent, check task manager to see all the logread.exe's go away. Then restart the SQL Server agent.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Paul Ibison" wrote:

> Assuming nobody is running sp_replcmds in QA, then the other log reader
> being referred to is the other transactional publication. Try stopping one
> log reader and set the other one off, so as to stagger the processes. Once
> one has finished reading, run the pther then backup and shrink.
> Also change the 2 parameters mentioned earlier.
> HTH,
> Paul Ibison
>
>
|||With what I am seeing, that is not possible. If I stop the
log reader agent for one, it stops for log reader for the
other one automatically. I think they are the same log
reader, even though they are two different publications.

>--Original Message--
>Assuming nobody is running sp_replcmds in QA, then the
other log reader
>being referred to is the other transactional publication.
Try stopping one
>log reader and set the other one off, so as to stagger
the processes. Once
>one has finished reading, run the pther then backup and
shrink.
>Also change the 2 parameters mentioned earlier.
>HTH,
>Paul Ibison
>
>.
>
|||I think the real problem is that the Log file is just too
big. I have seen it expand to 14 GB and then the log
reader agent just chews on it forever. It is a 4 GB ram
server with 3 GB allocated for SQL Server.
Is there any BK article on this being an issue? I cannot
find anything.

>--Original Message--
>whenever you run sp_replcmds yourself you MUST run a
sp_replflush before running it or you will get errors like
this.
>The only clean way of fixing the multiple log reader
agent problem is to stop the SQL Server agent, check task
manager to see all the logread.exe's go away. Then restart
the SQL Server agent.[vbcol=seagreen]
>--
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602.html
>
>
>"Paul Ibison" wrote:
other log reader[vbcol=seagreen]
publication. Try stopping one[vbcol=seagreen]
the processes. Once[vbcol=seagreen]
shrink.
>.
>
|||This doesn't sound correct. The log reader agent is intelligent enough to know the LSN (log sequence number) it last read and marked as replicated, and then starts reading from there
Issue a sp_repltrans to get an idea of how many transactions remain unread in the log.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Jay" wrote:

> I think the real problem is that the Log file is just too
> big. I have seen it expand to 14 GB and then the log
> reader agent just chews on it forever. It is a 4 GB ram
> server with 3 GB allocated for SQL Server.
> Is there any BK article on this being an issue? I cannot
> find anything.
>
> sp_replflush before running it or you will get errors like
> this.
> agent problem is to stop the SQL Server agent, check task
> manager to see all the logread.exe's go away. Then restart
> the SQL Server agent.
> other log reader
> publication. Try stopping one
> the processes. Once
> shrink.
>
|||there seems to be another bug here. when you create two publications in the
same database there are actually two log reader agents created. Only one log
reader agent runs though - that is the later one.
However check in the mangement folder and you will see two log reader agent
jobs for this publication database.
The bug is if you run a sp_helppublication for your publications each one
will report the name of the log reader agent which they created.
The problem/bug is, if that sp_helppublication should be reporting the log
reader agent in use.
It is possible that you have started two instances of the same log reader,
or you have started two log readers for two publications in the same
publication database. This is only possibly if you try to start it from the
management folder or start the jobs manually.
Again, bounce SQL Server agent to correct the problem.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Jay" <anonymous@.discussions.microsoft.com> wrote in message
news:208e701c45946$0276f630$a401280a@.phx.gbl...[vbcol=seagreen]
> With what I am seeing, that is not possible. If I stop the
> log reader agent for one, it stops for log reader for the
> other one automatically. I think they are the same log
> reader, even though they are two different publications.
>
> other log reader
> Try stopping one
> the processes. Once
> shrink.

replication and log shrinking

Since I created a replication (snapshot basic - push) when I'm running the backup log ... with truncate_ only....the size of the transaction log don't lessens. why? (sql2005 without sp)I set to simple the recovery mode and back to full....nothing
the usedPages value is very large...estimatedpages is very slow....
but not shrinking....|||

check of any open transaction . DBCC Opentran

Madhu

|||thnx
I run and and this is the result:

Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (4990:4944:1)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

What can i do with this?|||

From BOL

If neither database_name nor database_id is specified, the default is the current database.

Use DBCC OPENTRAN to determine whether an open transaction exists within the log. When using the BACKUP LOG statement, only the inactive portion of the log can be truncated; an open transaction can cause the log to not truncate completely. In earlier versions of Microsoft? SQL Server?, either all users had to log off or the server had to be shut down and restarted to clear uncommitted transactions from the log. With DBCC OPENTRAN, an open transaction can be identified (by obtaining the system process ID from the sp_who output) and terminated, if necessary.

you need to termintate this open transaction

http://msdn2.microsoft.com/en-us/library/ms182792.aspx

http://www.sql-server-performance.com/dbcc_commands.asp

google it you will fine many

Madhu

|||I stopped the replication (snapshot) and after I could shrink the log....
So this is a bug? ( the db running without any sp)

I tried....I re-set the replication and the symptom is occurred again....
so when I set a replication on this db, I can't shrink the transaction log anymore...