Agents and thread limits from SQL 2005 Distributor machine

Hi DBA's
We have one machime with 4 core processors and 2 GB of RAM. This machine have
3 sql 2005 instances and It's responsable form many replications. More of
them, have pull distribution agent, so we can controll these number over
project growing, but one instance don't.
All distribution agents were configured like "PUSH" and I don't intend to
use "PULL" with these because probably we'll affect subscribers with many
jobs (and thread distribution agents), that the same way, more difficult to
administrate.
How do we see these limits? If so, we can scale to another instance and
controll them!
Plese, if anyone has any article or "better pratical", we appreciate!
Numbers is the focus. We need numbers resgiters like x distrib.exe y logread.
exe and thus for ahead.
Thanks!
Krisnamourt
Message posted via http://www.droptable.com
select logreader=sum(case when program_name like '%logreader%'then 1 else 0
end),distrib=sum(case when program_name like '%'+rtrim(@.@.servername)+'%-%'
then 1 else 0 end) from sys.sysprocesses
http://www.zetainteractive.com - Shift Happens!
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
"Krisnamourt via droptable.com" <u21487@.uwe> wrote in message
news:7bfea23d5f150@.uwe...
> Hi DBA's
> We have one machime with 4 core processors and 2 GB of RAM. This machine
> have
> 3 sql 2005 instances and It's responsable form many replications. More of
> them, have pull distribution agent, so we can controll these number over
> project growing, but one instance don't.
> All distribution agents were configured like "PUSH" and I don't intend to
> use "PULL" with these because probably we'll affect subscribers with many
> jobs (and thread distribution agents), that the same way, more difficult
> to
> administrate.
> How do we see these limits? If so, we can scale to another instance and
> controll them!
> Plese, if anyone has any article or "better pratical", we appreciate!
> Numbers is the focus. We need numbers resgiters like x distrib.exe y
> logread.
> exe and thus for ahead.
> Thanks!
> Krisnamourt
> --
> Message posted via http://www.droptable.com
>
|||Thanks Hilary,
This article below show us some limitation from sql 2000 about log reader
threads. I did some research and didn't find any limitation about sql 2005.
So, is it true 100 log readers or what is the limitation of this agent?
http://support.microsoft.com/kb/306457/en-us
Hilary Cotter wrote:[vbcol=seagreen]
>select logreader=sum(case when program_name like '%logreader%'then 1 else 0
>end),distrib=sum(case when program_name like '%'+rtrim(@.@.servername)+'%-%'
>then 1 else 0 end) from sys.sysprocesses
>[quoted text clipped - 22 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server-replication/200712/1
|||I am unaware of any such limitation for Standard and Enterprise Editions.
Note that in the SQL 2000 article it refers to the number of simultaneous
agents which could be simultaneously running, its not necessarily a hard
limit as to the number of log reader agents/distribution agents that can be
on your system.
http://www.zetainteractive.com - Shift Happens!
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
"Krisnamourt via droptable.com" <u21487@.uwe> wrote in message
news:7c2e263b3461b@.uwe...
> Thanks Hilary,
> This article below show us some limitation from sql 2000 about log reader
> threads. I did some research and didn't find any limitation about sql
> 2005.
> So, is it true 100 log readers or what is the limitation of this agent?
> http://support.microsoft.com/kb/306457/en-us
> Hilary Cotter wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server-replication/200712/1
>

After password change, cannot connect to SQL servers.

We have 4 instances of SQL running on 4 different servers. We recently
had to change our administrator password. After doing that, we cannot
connect from our computers (Windows XP SP2) to any of our SQL servers
(1 Windows Server 2000, 3 Windows Server 2003). We see the following
error:
A connection could not be established to "servername"
Reason: Cannot generate SSPI context.
Please verify that SQL Server is running and check your SQL Server
registration properties and try again.
Any Ideas?
Check this article and see if it helps.
http://support.microsoft.com/default...b;en-us;811889
VM
"jgrubbs@.pgsd.org" wrote:

> We have 4 instances of SQL running on 4 different servers. We recently
> had to change our administrator password. After doing that, we cannot
> connect from our computers (Windows XP SP2) to any of our SQL servers
> (1 Windows Server 2000, 3 Windows Server 2003). We see the following
> error:
> A connection could not be established to "servername"
> Reason: Cannot generate SSPI context.
> Please verify that SQL Server is running and check your SQL Server
> registration properties and try again.
> Any Ideas?
>

After password change, cannot connect to SQL servers.

We have 4 instances of SQL running on 4 different servers. We recently
had to change our administrator password. After doing that, we cannot
connect from our computers (Windows XP SP2) to any of our SQL servers
(1 Windows Server 2000, 3 Windows Server 2003). We see the following
error:
A connection could not be established to "servername"
Reason: Cannot generate SSPI context.
Please verify that SQL Server is running and check your SQL Server
registration properties and try again.
Any Ideas?Could be an issue with your SPN - you can find more info and
troubleshooting steps for the error in the following
article:
How to troubleshoot the "Cannot generate SSPI context" error
message
http://support.microsoft.com/?id=811889
-Sue
On 17 Jan 2006 05:43:12 -0800, "jgrubbs@.pgsd.org"
<jgrubbs@.pgsd.org> wrote:

>We have 4 instances of SQL running on 4 different servers. We recently
>had to change our administrator password. After doing that, we cannot
>connect from our computers (Windows XP SP2) to any of our SQL servers
>(1 Windows Server 2000, 3 Windows Server 2003). We see the following
>error:
>A connection could not be established to "servername"
>Reason: Cannot generate SSPI context.
>Please verify that SQL Server is running and check your SQL Server
>registration properties and try again.
>Any Ideas?|||Thanks, we figured out the issue. The SQLSERVER and the SQLSERVERAGENT
service were set to use the old password. Once we changed this, we
were able to connect.

After password change, cannot connect to SQL servers.

We have 4 instances of SQL running on 4 different servers. We recently
had to change our administrator password. After doing that, we cannot
connect from our computers (Windows XP SP2) to any of our SQL servers
(1 Windows Server 2000, 3 Windows Server 2003). We see the following
error:
A connection could not be established to "servername"
Reason: Cannot generate SSPI context.
Please verify that SQL Server is running and check your SQL Server
registration properties and try again.
Any Ideas?
Could be an issue with your SPN - you can find more info and
troubleshooting steps for the error in the following
article:
How to troubleshoot the "Cannot generate SSPI context" error
message
http://support.microsoft.com/?id=811889
-Sue
On 17 Jan 2006 05:43:12 -0800, "jgrubbs@.pgsd.org"
<jgrubbs@.pgsd.org> wrote:

>We have 4 instances of SQL running on 4 different servers. We recently
>had to change our administrator password. After doing that, we cannot
>connect from our computers (Windows XP SP2) to any of our SQL servers
>(1 Windows Server 2000, 3 Windows Server 2003). We see the following
>error:
>A connection could not be established to "servername"
>Reason: Cannot generate SSPI context.
>Please verify that SQL Server is running and check your SQL Server
>registration properties and try again.
>Any Ideas?
|||Thanks, we figured out the issue. The SQLSERVER and the SQLSERVERAGENT
service were set to use the old password. Once we changed this, we
were able to connect.

After password change, cannot connect to SQL servers.

We have 4 instances of SQL running on 4 different servers. We recently
had to change our administrator password. After doing that, we cannot
connect from our computers (Windows XP SP2) to any of our SQL servers
(1 Windows Server 2000, 3 Windows Server 2003). We see the following
error:
A connection could not be established to "servername"
Reason: Cannot generate SSPI context.
Please verify that SQL Server is running and check your SQL Server
registration properties and try again.
Any Ideas?Check this article and see if it helps.
http://support.microsoft.com/default.aspx?scid=kb;en-us;811889
VM
"jgrubbs@.pgsd.org" wrote:
> We have 4 instances of SQL running on 4 different servers. We recently
> had to change our administrator password. After doing that, we cannot
> connect from our computers (Windows XP SP2) to any of our SQL servers
> (1 Windows Server 2000, 3 Windows Server 2003). We see the following
> error:
> A connection could not be established to "servername"
> Reason: Cannot generate SSPI context.
> Please verify that SQL Server is running and check your SQL Server
> registration properties and try again.
> Any Ideas?
>

After password change, cannot connect to SQL servers.

We have 4 instances of SQL running on 4 different servers. We recently
had to change our administrator password. After doing that, we cannot
connect from our computers (Windows XP SP2) to any of our SQL servers
(1 Windows Server 2000, 3 Windows Server 2003). We see the following
error:
A connection could not be established to "servername"
Reason: Cannot generate SSPI context.
Please verify that SQL Server is running and check your SQL Server
registration properties and try again.
Any Ideas?Check this article and see if it helps.
http://support.microsoft.com/defaul...kb;en-us;811889
VM
"jgrubbs@.pgsd.org" wrote:

> We have 4 instances of SQL running on 4 different servers. We recently
> had to change our administrator password. After doing that, we cannot
> connect from our computers (Windows XP SP2) to any of our SQL servers
> (1 Windows Server 2000, 3 Windows Server 2003). We see the following
> error:
> A connection could not be established to "servername"
> Reason: Cannot generate SSPI context.
> Please verify that SQL Server is running and check your SQL Server
> registration properties and try again.
> Any Ideas?
>

Affinity mask setting in Yukon.

Hello,
Current setup: 3 instances of SQL server 2005 on a 2 CPU box.
How do I use IO/affinity to ensure that all SQL Server related I/O goes to
the first CPU? I'm aware that sp_configure is the command to be used.
But how do I exactly specify that the first CPU is to be exclusively used
for SQLServer I/O? Can someone give me an example OR point me to an page tha
t
has more info on this?
Cheers!
SQLCatzThe easiest way is to set the masks graphically in SSMS (Server Properties,
Processors).
There are more technical details about the whole process at
http://msdn2.microsoft.com/en-us/library/ms187104.aspx
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Hello Paul,
Unfortunately I cannot use the GUI for this.
It has to be part of a script.
Which means that I have to use sp_configure.
But how do I specify which CPU gets the SQL Server related I/O?
Cheers!
SQLCatz|||For the first proc only, it'll be
sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
sp_configure 'affinity mask', 1;
RECONFIGURE;
GO
For the second proc only, it'll be
sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
sp_configure 'affinity mask', 2;
RECONFIGURE;
GO
For the both procs, it'll be
sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
sp_configure 'affinity mask', 3;
RECONFIGURE;
GO
Similarly for the affinity I/O mask.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Hello Paul,
Thank you for that!
Recall reading on a web site that the setting was to be done using a bitmap
mask, where each bit specified the CPU number. Is this correct? And if it is
- how do I go about using this option? Just curious.
Cheers!
SQLCatz|||Please have a look at the link in my first post - htere's a good explanation
of the conversion needed for bitmasks and decimal values. The only tricky
thing to remember is that the processors are zero-indexed ie the first
processor is number 0 and the second is number 1 etc.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

Affinity mask setting in Yukon.

Hello,
Current setup: 3 instances of SQL server 2005 on a 2 CPU box.
How do I use IO/affinity to ensure that all SQL Server related I/O goes to
the first CPU? I'm aware that sp_configure is the command to be used.
But how do I exactly specify that the first CPU is to be exclusively used
for SQLServer I/O? Can someone give me an example OR point me to an page that
has more info on this?
Cheers!
SQLCatzThe easiest way is to set the masks graphically in SSMS (Server Properties,
Processors).
There are more technical details about the whole process at
http://msdn2.microsoft.com/en-us/library/ms187104.aspx
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Hello Paul,
Unfortunately I cannot use the GUI for this.
It has to be part of a script.
Which means that I have to use sp_configure.
But how do I specify which CPU gets the SQL Server related I/O?
Cheers!
SQLCatz|||For the first proc only, it'll be
sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
sp_configure 'affinity mask', 1;
RECONFIGURE;
GO
For the second proc only, it'll be
sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
sp_configure 'affinity mask', 2;
RECONFIGURE;
GO
For the both procs, it'll be
sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
sp_configure 'affinity mask', 3;
RECONFIGURE;
GO
Similarly for the affinity I/O mask.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Hello Paul,
Thank you for that!
Recall reading on a web site that the setting was to be done using a bitmap
mask, where each bit specified the CPU number. Is this correct? And if it is
- how do I go about using this option? Just curious.
Cheers!
SQLCatz|||Please have a look at the link in my first post - htere's a good explanation
of the conversion needed for bitmasks and decimal values. The only tricky
thing to remember is that the processors are zero-indexed ie the first
processor is number 0 and the second is number 1 etc.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com