Back to basic question

And forgive me for asking this over and over again..
My distribution agent states " 1 transaction with 100,000 commands were
delivered "
My default profile has the Commitbatchsize as 100 and CommitBatchThreshold
as 1000.
So in this case, how does distribution agent work ? Does it read/buffer all
the 100,000 commands from msrepl_commands since its 1 transaction before it
fires them off to the subscriber ?
And how does the default profile play a role here ? If it does read/buffers
100,000 commands before it pushes it across, does it commit 1000 commands at
a time on the subscriber due to the commitbatchthreshold ? And if if fails
halfway saying after inserting 50,000 commands, does it just rollback the
last few commands that were not committed or does it rollback all 50,000
commands ?
Using transactional replication
Hassan,
why not do a test of these parameters? If you have Lumigent's logreader, you
can use it on the the live subscriber transaction log to verify the
behaviour for yourself. Create and publish a 100 record test table on the
publisher, modify the Commitbatchsize and CommitBatchThreshold parameters
and make an update of the 100 records. There's no need for an explicit
transaction, just an update of the table that affects all rows is ordinarily
logged as 100 updates with a begin and commit surrounding it. Start the
distribution agent then check Lumigent's output. The same setup can be used
for interrupted commands.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

AWE and the max momory setting

Having read this excellent post on AWE and the max momory setting:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55191

Which states:

"Optional to limit the amount of memory the SQL Server will use, because SQL Server will not dynamically allocate/deallocate memory when AWE is enabled.
Leave about 500 MB available for OS"

Subsequently I have read:

http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1210563,00.html

Which states:

"For instance, SQL Server 2005 will have memory buffer pool problems if the max server memory setting is not set to its default 2147483647 (i.e., all available memory)."

So what should I set my max. memory to on 2K5 when I have AWE enabled.

Hi

I am not sure about the comment in the second article that you have mentioned. That comment is not precise at all.

With regards to MAX Server Memory setting, it depends how much memory does your machine have and if you run other apps (Analysis/Reporting Services, etc) on the server.

Say if your server has 16GB and you only use the server for SQL Server, you should set the MAX Server Memory to 14GB.

regards

Jag

|||

I agree the 2nd article is not precise, which is why I ahve restricted by max memory. I have 8GB and have restricted SQL Server to 7.5GB as only the OS is running on that server.

|||

I would restrict SQL Server to 6.5GB if I had 8GB on my machine. With OS only having 500MB You might experience backup errors if SQL Server 2K5 uses all what it is configured for.

|||Thanks for the advice.