Replication causing huge memory issues

I posted this yesterday, but I couldn't change the Subject Line so I thought
I would repost it with a better description.
We currently have repliation running and it seems to be consuming all of our
memory. If I look at the spid that is consuming all of the memory(which is
related to replication), all I see in the Input buffer is: IF @.@.TRANCOUNT >
0 COMMIT TRAN. If I run a trace, I see that the spid is sending transactions
such as updates and deletes related to replication. For each transaction
that this spid replicates, it takes a piece of memory but doesn't release it
back. Before I know it, this spid has consumed all of the computers memory.
Patrick
Hi Patrick
"Patrick" wrote:

> I posted this yesterday, but I couldn't change the Subject Line so I thought
> I would repost it with a better description.
> We currently have repliation running and it seems to be consuming all of our
> memory. If I look at the spid that is consuming all of the memory(which is
> related to replication), all I see in the Input buffer is: IF @.@.TRANCOUNT >
> 0 COMMIT TRAN. If I run a trace, I see that the spid is sending transactions
> such as updates and deletes related to replication. For each transaction
> that this spid replicates, it takes a piece of memory but doesn't release it
> back. Before I know it, this spid has consumed all of the computers memory.
> Patrick
You haven't said which version of SQL Server you are using and if you have
service packed/hotfixed it. You also don't say how much memory the system has
and how much is allocated to SQL Server or if you require other programs to
run on this machine.
In general SQL Server does not release memory unless it has to, if the
memory is all used does it cause any issues? Have you tried setting a maximum
amount of memory for SQL Server as a test?
John
|||"John Bell" wrote:
> You haven't said which version of SQL Server you are using and if you have
> service packed/hotfixed it.
SQL Server Standard Edition 2000; Product Version: 8.00.760(SP3); I dont
know about any hotfixes as this server isn't well documented, unless there is
another way within sql to find what hotfixes have been applied.

>You also don't say how much memory the system has
> and how much is allocated to SQL Server or if you require other programs to
> run on this machine.
3 gigs of Memory running on Windows 2000 Server. Sql is set to use memory
dynamically.

> In general SQL Server does not release memory unless it has to, if the
> memory is all used does it cause any issues? Have you tried setting a maximum
> amount of memory for SQL Server as a test?
This does cause issues, everything else on that server comes to a halt and
the CPU spikes to 100%. I haven't set the Maximum memory as I am scared that
it will consume that memory and bring the server to a halt.
Issue Describe a little better:
This issue started about a month ago when the sql services would not restart
after the Weekly Sunday Reboot. I researched the issue and was unable to
find a resolution, but then again I am not an OS expert, so I created a
ticket and asked for assistances from the Network Guy here. Unfortunately,
we were unable to resolve the issue related to sql services not restarting
after a reboot, so we discontinued the weekly reboot. Then last Thursday
(03/29/2007) sql server experienced such poor performance that we had to
reboot MIDB02(Titan) during business hours; but before I rebooted, I found
the cause of the poor performance. The SPID related to replication was
taking memory from the buffer for every transaction that it was replicating,
but not releasing memory back to the buffer. This caused the replication
SPID to consume more than two 2 gigs of memory and the only way to release
the memory back to the buffer was to reboot.
When I first questioned about the reboot of MIDB02(Titan), I was informed
that is required to resolve some performance issues. On Thursday 03/29/2007
I found the answer to my question of why the server is rebooted on a weekly
basis… to resolve the issue with the replication SPID not releasing memory
back to the buffer. Hopefully after resolving this issue, a weekly reboot of
MIDB02 will not be needed, as Microsoft doesn’t recommend rebooting servers
on a regularly scheduled basis.
|||Hi Patrick
"Patrick" wrote:

>
> "John Bell" wrote:
> SQL Server Standard Edition 2000; Product Version: 8.00.760(SP3); I dont
> know about any hotfixes as this server isn't well documented, unless there is
> another way within sql to find what hotfixes have been applied.
>
Hotfixes should update the server version (SELECT @.@.VERSION to get this).
You are on SP3 or SP3a. You should be on SP3a as SP3 has issues, check the
version of Ssnetlib.dll which should be 2000.80.766.0 (see
http://sqlserver2000.databases.aspfaq.com/how-do-i-know-which-version-of-sql-server-i-m-running.html)
This is still an old version and you should make consider installing SP4 and
moving up to hotfix version 8.00.2187
http://support.microsoft.com/default.aspx/kb/916287
Also make sure that your OS service packed and patched. Hopefully you are at
least on Windows 2003 SP1

>
> 3 gigs of Memory running on Windows 2000 Server. Sql is set to use memory
> dynamically.
>
> This does cause issues, everything else on that server comes to a halt and
> the CPU spikes to 100%. I haven't set the Maximum memory as I am scared that
> it will consume that memory and bring the server to a halt.
>
> Issue Describe a little better:
> This issue started about a month ago when the sql services would not restart
> after the Weekly Sunday Reboot. I researched the issue and was unable to
> find a resolution, but then again I am not an OS expert, so I created a
> ticket and asked for assistances from the Network Guy here. Unfortunately,
> we were unable to resolve the issue related to sql services not restarting
> after a reboot, so we discontinued the weekly reboot. Then last Thursday
> (03/29/2007) sql server experienced such poor performance that we had to
> reboot MIDB02(Titan) during business hours; but before I rebooted, I found
> the cause of the poor performance. The SPID related to replication was
> taking memory from the buffer for every transaction that it was replicating,
> but not releasing memory back to the buffer. This caused the replication
> SPID to consume more than two 2 gigs of memory and the only way to release
> the memory back to the buffer was to reboot.
> When I first questioned about the reboot of MIDB02(Titan), I was informed
> that is required to resolve some performance issues. On Thursday 03/29/2007
> I found the answer to my question of why the server is rebooted on a weekly
> basis… to resolve the issue with the replication SPID not releasing memory
> back to the buffer. Hopefully after resolving this issue, a weekly reboot of
> MIDB02 will not be needed, as Microsoft doesn’t recommend rebooting servers
> on a regularly scheduled basis.
>
In general servers should not need rebooting! As there are many replication
sites I would surprised if this is an outstanding problem if the OS and SQL
Server were patched, although I have not found a KB article that would
describe your situation. If this persists after patching you should raise an
issue with PSS.
John
|||Already Opened a case with PSS and they have no clue. Currently I am dealing
with someone in India, anyway I can get my case transferred to the states?
We can't update to SP4 as we would have to redevelop a process for one of our
apps. I will keep truckin along and see what I can come up with.
Patrick.
|||Hi Patrick
"Patrick" wrote:

> Already Opened a case with PSS and they have no clue. Currently I am dealing
> with someone in India, anyway I can get my case transferred to the states?
> We can't update to SP4 as we would have to redevelop a process for one of our
> apps. I will keep truckin along and see what I can come up with.
> Patrick.
I am not sure why you need to redevelop any process just because of SP4?
Have you checked that you are on SP3a and not SP3?
John
|||How do you tell the difference from SP3 adn SP3a?
|||Hi Patrick
"Patrick" wrote:

> How do you tell the difference from SP3 adn SP3a?
See
http://sqlserver2000.databases.aspfaq.com/how-do-i-know-which-version-of-sql-server-i-m-running.html
Martin
sql