Hi,
In my merge replication scenario I use Push subscriptions. The
distributor is SQL server 2000 Sp3a, and subscribers are MSDE SP3a.
Everything works fine but recently merge agents started to "hang"
reporting no errors at random phases of replication session. They stop
sending or receiving any data between distributor and subscriber
(there is no network traffic). SQL Profiles show no activity on a
subscriber server. Last message generated by agent is "The process is
running and is waiting for a response from one of the backend
connections".
If there is a problem with communication between servers one would
expect returnig an error code and retrying operation. In my case the
merge agent "hangs" and waits for nothing. After 1 or 2 hours it
reports a communication link failure (waits for a random period of
time).
To continue a Merge process I have to Stop and Start the merge agent
manually.
Since my replication sends a lot of data from subscribers
(MSMerge_genhistory has approx 50000 entries when retention period is
set to 2 days.) after uploading data changes to the publisher (the
procedure sp_MScheckexistsgeneration is executed for every one of
50000 generations - am I right about this?) it takes a long time (40
minutes) to complete. If merge agent hangs, the process must be run
from the beginning. In this situation it is almost impossible to
complete the replication session.
What can be the cause of such behavior of merge agents?
Last lines of output (level 3) generated by agent are as follows:
{call sp_MScheckexistsgeneration (?, ?) }
{call sp_MScheckexistsgeneration (?, ?) }
Percent Complete: 0
The process is running and is waiting for a response from one of the
backend connections.
Repl Agent Status: 3
Tomek
Tomek,
it's difficult to say, but in some sense this looks like a connectivity
issue. However, when I've seen such a situation before, the error was
'General network error'. You might like to verify is this is/is not the
case, by using a Network Monitor tool:
http://support.microsoft.com/default...48942&sd=tech.
Alternatively you could open a window in QA on the publisher, with the
window connected to the subscriber SQL Server. In the window do a simple
select command in a loop and return the date. This should tell you if/when
the connectivity had problems.
Can you check in current activity or sp_who2/sp_lock/sp_blocker to identify
if there is any blocking involved. If the problem is not connectivity then
this would be my next test.
I'd also like to know what happens when you restart the agent - does it work
quickly or still take a long time but is successful.
No doubt you have considered this, but if possible can you synchronize more
often to shorten the batchsize?
You might also optimize performance by running
sp_mergemetadataretentioncleanup manually and optimize
he -DownloadGenerationsPerBatch parameter.
HTH,
Paul Ibison
|||Paul,
Thank you for your response. I hope my answers to your questions will
help us to solve the problem.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message news:<#EzzqyDQEHA.1312@.TK2MSFTNGP12.phx.gbl>...
> it's difficult to say, but in some sense this looks like a connectivity
> issue. However, when I've seen such a situation before, the error was
> 'General network error'. You might like to verify is this is/is not the
> case, by using a Network Monitor tool:
> http://support.microsoft.com/default...48942&sd=tech.
It can be related to connectivity because merge agents connect to
subscribers that not always use reliable network connection. But the
problem is that instead of reporting General network error and
retrying operation agent waits (hangs) doing nothing.
> Alternatively you could open a window in QA on the publisher, with the
> window connected to the subscriber SQL Server. In the window do a simple
> select command in a loop and return the date. This should tell you if/when
> the connectivity had problems.
QA in loop worked fine (no connectivity problems), but in the same
time agent assigned to the same subscriber hanged.
> Can you check in current activity or sp_who2/sp_lock/sp_blocker to identify
> if there is any blocking involved. If the problem is not connectivity then
> this would be my next test.
I've checked this ne too. Blocking is not the reason.
> I'd also like to know what happens when you restart the agent - does it work
> quickly or still take a long time but is successful.
I will describe how my agent works. Please tell me is it correct
behavior.
1) After connecting to subscriber agent uploads all the data changes
made by subscriber to the publisher. This works very fast and i do not
see any problem here (agent has never stopped at this phase).
2) Merge agent executes sp_MScheckexistsgeneration for each of 50000
generations stored in msmerge_genhistory at subscriber side. If there
is some data to download, it is downloaded to subscriber, and then
merge agent checks next generation.
Now let's assume that merge agent "hanged" ot generation 39000. After
restarting the agent it starts the process from the beginning checking
each of 39000 generations that where checked and merged in previus
session.
The merge agent is succesfull only if it is able to go through 50000
generations without loosing connection to subscriber. That is wy it is
so difficult to complete merge session.
IMHO the merge agent should start from generation 39000 making
completing session possible even on not reliable connections. Is it
"by design" behaviour?
> No doubt you have considered this, but if possible can you synchronize more
> often to shorten the batchsize?
It is not possible. subscribers can not synchronize on saturday and
sunday. So 2 days retention perod is minumum.
> You might also optimize performance by running
> sp_mergemetadataretentioncleanup manually and optimize
> he -DownloadGenerationsPerBatch parameter.
I think the problem is not metadata at publisher but metadata at
subscribers. It is not deleted after succesfull replication session (i
think it must work this way).
DownloadGenerationsPerBatch does not help because most of the time
merge agent executes sp_MScheckexistsgeneration without downloading
any data changes. Or maybe I am misunderstanding this parameter?.
Tomek
|||Tomek,
Slow and unreliable connections can result in more retries by the Merge
Agent, none of which you've seen. Your QA test also indicates the same
thing, so we can cross this off the list.
Answers inline...
> IMHO the merge agent should start from generation 39000 making
> completing session possible even on not reliable connections. Is it
> "by design" behaviour?
>
I guess it is not processed this way to try to avoid potential
non-convergence as inserts and child records could be in separate batches
(http://support.microsoft.com/default...b;enus;Q308266)
>
Having MetadataRetentionCleanup 1 should sort this out, but you could run
manually sp_mergemetadataretentioncleanup on the publisher and subscriber to
see if things are improved. It won't remove the 50000 entries, but what I am
hoping is that the search for new generations will be noticeably quicker
after old ones are removed.
> DownloadGenerationsPerBatch does not help because most of the time
> merge agent executes sp_MScheckexistsgeneration without downloading
> any data changes. Or maybe I am misunderstanding this parameter?.
Increasing the -DownloadGenerationsPerBatch Merge Agent parameters (even to
its maximum value of 2000) is a recommendation where a Subscriber has not
merged data with the Publisher for an extended period time, during which the
Publisher or other Subscribers have made numerous changes
(http://www.microsoft.com/technet/pro...n/mergperf.msp
x). So, I'd definitley test altering this parameter's value.
(Powodzenia)
Paul
Showing posts with label errors. Show all posts
Showing posts with label errors. Show all posts
Monday, March 26, 2012
Merge agent fails creating snapshot in FTP directory.
Hello all.
Me again. I'm finding some of these replication errors very hard to track
down. With your great guidance I'm working on replicating via the internet. I
think I know what has to be done now, but when configuring the publisher to
create ftp snapshots, the snapshot agent is throwing an error of:
The process could not create file
'D:\snapshots\ftp\DB_Test_DB_Test\20050411145407'.
Cannot create a file when that file already exists.
(Source: (OS); Error number: 183)
I have no idea why this is occuring. The directory is empty when I start,
that numbered directory is created by the snapshot agent fine. Why the heck
won't it continue and generate the snapshots inside the directory? The error
isn't making any sense at all.
Any help would be greatly appreciated.
Very strange. Can you check that you're just using the alternative snapshot
location only - this is recommended but shouldn't cause any problem. Also,
can you delete the directory and rerun the snapshot agent. Actually, it
might be due to an identically named article - I know this isn't possible,
but if you are using owner prefixes to distinguish objects it might be
possible. Perhaps you can enable logging and we'll find out the object name
involved (http://support.microsoft.com/?id=312292).
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul your a genius, I would of never thought of that being the problem. It
seems as if it was trying to generate snapshots in both locations, but
failed. I'm finding theres lots of tid bits about SQL 2000 replication that
you have to know because it doesn't all work as you expect sometimes.
Great that you guys are around though. I greatly appreciate your help
throughout. I did replication via handheld devices with msdn's help and this
technology can be awesome once you get a hang of it.
Now lets hook up my subscribers and hope all goes well!
Thanks again and take care.
"Paul Ibison" wrote:
> Very strange. Can you check that you're just using the alternative snapshot
> location only - this is recommended but shouldn't cause any problem. Also,
> can you delete the directory and rerun the snapshot agent. Actually, it
> might be due to an identically named article - I know this isn't possible,
> but if you are using owner prefixes to distinguish objects it might be
> possible. Perhaps you can enable logging and we'll find out the object name
> involved (http://support.microsoft.com/?id=312292).
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Hmm one question I do have. Now that the snapshots are generated, I don't
recall the subscriber configuration ever asking for a FTP login/password.
How does the FTP login info work? Do I have to setup the FTP login
information to match an SQL login?
"Synced" wrote:
[vbcol=seagreen]
> Paul your a genius, I would of never thought of that being the problem. It
> seems as if it was trying to generate snapshots in both locations, but
> failed. I'm finding theres lots of tid bits about SQL 2000 replication that
> you have to know because it doesn't all work as you expect sometimes.
> Great that you guys are around though. I greatly appreciate your help
> throughout. I did replication via handheld devices with msdn's help and this
> technology can be awesome once you get a hang of it.
> Now lets hook up my subscribers and hope all goes well!
> Thanks again and take care.
> "Paul Ibison" wrote:
|||AFAIR in SQL Server 7.0 the ftp login info was set up on the subscriber
side, but not so in SQL Server 2000. Here it is part of the publication
properties. If you have successfully synchronized, this is because the
anonymous FTP has been enabled.
Rgds,
Paul Ibison
|||Hmm looks like I have a dilemma. Some of my subscribers are going to be
behind a firewall. Is there a way to tell the subscription FTP client to use
PASSIVE mode?
I don't think microsoft would have left this out but I can't seem to find
any info.
Thanks again guys!
"Synced" wrote:
[vbcol=seagreen]
> Hmm one question I do have. Now that the snapshots are generated, I don't
> recall the subscriber configuration ever asking for a FTP login/password.
> How does the FTP login info work? Do I have to setup the FTP login
> information to match an SQL login?
> "Synced" wrote:
|||Synced,
what is PASSIVE mode?
If you can't open port 21 then there is another option. Create the snapshot,
get it to the subscriber, then subscribe and select to use an alternative
snapshot location - on the properties of the distribution agent.
Rgds,
Paul Ibison
"Synced" <Synced@.discussions.microsoft.com> wrote in message
news:93709910-3C8E-4148-8BFA-090FE78C219F@.microsoft.com...[vbcol=seagreen]
> Hmm looks like I have a dilemma. Some of my subscribers are going to be
> behind a firewall. Is there a way to tell the subscription FTP client to
> use
> PASSIVE mode?
> I don't think microsoft would have left this out but I can't seem to find
> any info.
> Thanks again guys!
> "Synced" wrote:
|||Hey Paul,
I'm not sure how familiar you are with the FTP protocol, but the FTP
protocol has 2 modes, active and passive. Active is very difficult for
systems behind a firewall because of the flawed design in the FTP protocol.
Although FTP was designed way before firewalls were required for security.
Basically FTP by default runs in active mode. I log into an FTP, when I do a
dir, or GET, my client chooses a random port to transfer data on, tells the
FTP server, the FTP server actually does the connecting to the client. So
port 21 is only really the "control" port, not the data transfer port.
In passive mode, its the other way around. Passive FTP is automatically used
in IE etc I believe because so many people are behind NAT's and firewalls.
Right now my subscriber is behind a firewall so allowing port 21 is not
solving this issue. If I ftp in a FTP client, in default mode (active) I
cannot do a dir, or get command, but if I enable passive, everything works
fine.
My question is. Is there a way to enable passive mode for whatever
mechanism replication uses for a FTP client?
Does replication agent use the command like ftp command? If so the command
line ftp.exe doesn't support passive mode. If it does use ftp.exe, can I
swap ftp.exe with a 3rd party one that supports passive mode.
I really hope we can find a solution for this. Basically when I run my
subscription, it just says connecting to server... and sits there. But when I
FTP in a ftp client with the same info it logs in fine. just can't transfer
any data unless you enable passive mode.
I really hope I'm not totally stuck on this. I can't see this being not
possible since many many servers are behind firewalls these days.
Thanks a ton Paul,
Take care.
"Paul Ibison" wrote:
> Synced,
> what is PASSIVE mode?
> If you can't open port 21 then there is another option. Create the snapshot,
> get it to the subscriber, then subscribe and select to use an alternative
> snapshot location - on the properties of the distribution agent.
> Rgds,
> Paul Ibison
>
> "Synced" <Synced@.discussions.microsoft.com> wrote in message
> news:93709910-3C8E-4148-8BFA-090FE78C219F@.microsoft.com...
>
>
|||Synced,
I'm not going to be able to help much more as this is way, way beyond my
knowledge/experience of FTP. In my case I go through 2 firewalls and I just
asked the network guys to open up port 21 on both of them which worked fine.
Perhaps someone else can contribute here for us....
Rgds,
Paul
"Synced" <Synced@.discussions.microsoft.com> wrote in message
news:732B58F3-73D9-4D38-AC7D-68E527AC2274@.microsoft.com...[vbcol=seagreen]
> Hey Paul,
> I'm not sure how familiar you are with the FTP protocol, but the FTP
> protocol has 2 modes, active and passive. Active is very difficult for
> systems behind a firewall because of the flawed design in the FTP
> protocol.
> Although FTP was designed way before firewalls were required for security.
> Basically FTP by default runs in active mode. I log into an FTP, when I do
> a
> dir, or GET, my client chooses a random port to transfer data on, tells
> the
> FTP server, the FTP server actually does the connecting to the client. So
> port 21 is only really the "control" port, not the data transfer port.
> In passive mode, its the other way around. Passive FTP is automatically
> used
> in IE etc I believe because so many people are behind NAT's and firewalls.
> Right now my subscriber is behind a firewall so allowing port 21 is not
> solving this issue. If I ftp in a FTP client, in default mode (active) I
> cannot do a dir, or get command, but if I enable passive, everything works
> fine.
> My question is. Is there a way to enable passive mode for whatever
> mechanism replication uses for a FTP client?
> Does replication agent use the command like ftp command? If so the command
> line ftp.exe doesn't support passive mode. If it does use ftp.exe, can I
> swap ftp.exe with a 3rd party one that supports passive mode.
> I really hope we can find a solution for this. Basically when I run my
> subscription, it just says connecting to server... and sits there. But
> when I
> FTP in a ftp client with the same info it logs in fine. just can't
> transfer
> any data unless you enable passive mode.
> I really hope I'm not totally stuck on this. I can't see this being not
> possible since many many servers are behind firewalls these days.
> Thanks a ton Paul,
> Take care.
> "Paul Ibison" wrote:
Me again. I'm finding some of these replication errors very hard to track
down. With your great guidance I'm working on replicating via the internet. I
think I know what has to be done now, but when configuring the publisher to
create ftp snapshots, the snapshot agent is throwing an error of:
The process could not create file
'D:\snapshots\ftp\DB_Test_DB_Test\20050411145407'.
Cannot create a file when that file already exists.
(Source: (OS); Error number: 183)
I have no idea why this is occuring. The directory is empty when I start,
that numbered directory is created by the snapshot agent fine. Why the heck
won't it continue and generate the snapshots inside the directory? The error
isn't making any sense at all.
Any help would be greatly appreciated.
Very strange. Can you check that you're just using the alternative snapshot
location only - this is recommended but shouldn't cause any problem. Also,
can you delete the directory and rerun the snapshot agent. Actually, it
might be due to an identically named article - I know this isn't possible,
but if you are using owner prefixes to distinguish objects it might be
possible. Perhaps you can enable logging and we'll find out the object name
involved (http://support.microsoft.com/?id=312292).
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul your a genius, I would of never thought of that being the problem. It
seems as if it was trying to generate snapshots in both locations, but
failed. I'm finding theres lots of tid bits about SQL 2000 replication that
you have to know because it doesn't all work as you expect sometimes.
Great that you guys are around though. I greatly appreciate your help
throughout. I did replication via handheld devices with msdn's help and this
technology can be awesome once you get a hang of it.
Now lets hook up my subscribers and hope all goes well!
Thanks again and take care.
"Paul Ibison" wrote:
> Very strange. Can you check that you're just using the alternative snapshot
> location only - this is recommended but shouldn't cause any problem. Also,
> can you delete the directory and rerun the snapshot agent. Actually, it
> might be due to an identically named article - I know this isn't possible,
> but if you are using owner prefixes to distinguish objects it might be
> possible. Perhaps you can enable logging and we'll find out the object name
> involved (http://support.microsoft.com/?id=312292).
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Hmm one question I do have. Now that the snapshots are generated, I don't
recall the subscriber configuration ever asking for a FTP login/password.
How does the FTP login info work? Do I have to setup the FTP login
information to match an SQL login?
"Synced" wrote:
[vbcol=seagreen]
> Paul your a genius, I would of never thought of that being the problem. It
> seems as if it was trying to generate snapshots in both locations, but
> failed. I'm finding theres lots of tid bits about SQL 2000 replication that
> you have to know because it doesn't all work as you expect sometimes.
> Great that you guys are around though. I greatly appreciate your help
> throughout. I did replication via handheld devices with msdn's help and this
> technology can be awesome once you get a hang of it.
> Now lets hook up my subscribers and hope all goes well!
> Thanks again and take care.
> "Paul Ibison" wrote:
|||AFAIR in SQL Server 7.0 the ftp login info was set up on the subscriber
side, but not so in SQL Server 2000. Here it is part of the publication
properties. If you have successfully synchronized, this is because the
anonymous FTP has been enabled.
Rgds,
Paul Ibison
|||Hmm looks like I have a dilemma. Some of my subscribers are going to be
behind a firewall. Is there a way to tell the subscription FTP client to use
PASSIVE mode?
I don't think microsoft would have left this out but I can't seem to find
any info.
Thanks again guys!
"Synced" wrote:
[vbcol=seagreen]
> Hmm one question I do have. Now that the snapshots are generated, I don't
> recall the subscriber configuration ever asking for a FTP login/password.
> How does the FTP login info work? Do I have to setup the FTP login
> information to match an SQL login?
> "Synced" wrote:
|||Synced,
what is PASSIVE mode?
If you can't open port 21 then there is another option. Create the snapshot,
get it to the subscriber, then subscribe and select to use an alternative
snapshot location - on the properties of the distribution agent.
Rgds,
Paul Ibison
"Synced" <Synced@.discussions.microsoft.com> wrote in message
news:93709910-3C8E-4148-8BFA-090FE78C219F@.microsoft.com...[vbcol=seagreen]
> Hmm looks like I have a dilemma. Some of my subscribers are going to be
> behind a firewall. Is there a way to tell the subscription FTP client to
> use
> PASSIVE mode?
> I don't think microsoft would have left this out but I can't seem to find
> any info.
> Thanks again guys!
> "Synced" wrote:
|||Hey Paul,
I'm not sure how familiar you are with the FTP protocol, but the FTP
protocol has 2 modes, active and passive. Active is very difficult for
systems behind a firewall because of the flawed design in the FTP protocol.
Although FTP was designed way before firewalls were required for security.
Basically FTP by default runs in active mode. I log into an FTP, when I do a
dir, or GET, my client chooses a random port to transfer data on, tells the
FTP server, the FTP server actually does the connecting to the client. So
port 21 is only really the "control" port, not the data transfer port.
In passive mode, its the other way around. Passive FTP is automatically used
in IE etc I believe because so many people are behind NAT's and firewalls.
Right now my subscriber is behind a firewall so allowing port 21 is not
solving this issue. If I ftp in a FTP client, in default mode (active) I
cannot do a dir, or get command, but if I enable passive, everything works
fine.
My question is. Is there a way to enable passive mode for whatever
mechanism replication uses for a FTP client?
Does replication agent use the command like ftp command? If so the command
line ftp.exe doesn't support passive mode. If it does use ftp.exe, can I
swap ftp.exe with a 3rd party one that supports passive mode.
I really hope we can find a solution for this. Basically when I run my
subscription, it just says connecting to server... and sits there. But when I
FTP in a ftp client with the same info it logs in fine. just can't transfer
any data unless you enable passive mode.
I really hope I'm not totally stuck on this. I can't see this being not
possible since many many servers are behind firewalls these days.
Thanks a ton Paul,
Take care.
"Paul Ibison" wrote:
> Synced,
> what is PASSIVE mode?
> If you can't open port 21 then there is another option. Create the snapshot,
> get it to the subscriber, then subscribe and select to use an alternative
> snapshot location - on the properties of the distribution agent.
> Rgds,
> Paul Ibison
>
> "Synced" <Synced@.discussions.microsoft.com> wrote in message
> news:93709910-3C8E-4148-8BFA-090FE78C219F@.microsoft.com...
>
>
|||Synced,
I'm not going to be able to help much more as this is way, way beyond my
knowledge/experience of FTP. In my case I go through 2 firewalls and I just
asked the network guys to open up port 21 on both of them which worked fine.
Perhaps someone else can contribute here for us....
Rgds,
Paul
"Synced" <Synced@.discussions.microsoft.com> wrote in message
news:732B58F3-73D9-4D38-AC7D-68E527AC2274@.microsoft.com...[vbcol=seagreen]
> Hey Paul,
> I'm not sure how familiar you are with the FTP protocol, but the FTP
> protocol has 2 modes, active and passive. Active is very difficult for
> systems behind a firewall because of the flawed design in the FTP
> protocol.
> Although FTP was designed way before firewalls were required for security.
> Basically FTP by default runs in active mode. I log into an FTP, when I do
> a
> dir, or GET, my client chooses a random port to transfer data on, tells
> the
> FTP server, the FTP server actually does the connecting to the client. So
> port 21 is only really the "control" port, not the data transfer port.
> In passive mode, its the other way around. Passive FTP is automatically
> used
> in IE etc I believe because so many people are behind NAT's and firewalls.
> Right now my subscriber is behind a firewall so allowing port 21 is not
> solving this issue. If I ftp in a FTP client, in default mode (active) I
> cannot do a dir, or get command, but if I enable passive, everything works
> fine.
> My question is. Is there a way to enable passive mode for whatever
> mechanism replication uses for a FTP client?
> Does replication agent use the command like ftp command? If so the command
> line ftp.exe doesn't support passive mode. If it does use ftp.exe, can I
> swap ftp.exe with a 3rd party one that supports passive mode.
> I really hope we can find a solution for this. Basically when I run my
> subscription, it just says connecting to server... and sits there. But
> when I
> FTP in a ftp client with the same info it logs in fine. just can't
> transfer
> any data unless you enable passive mode.
> I really hope I'm not totally stuck on this. I can't see this being not
> possible since many many servers are behind firewalls these days.
> Thanks a ton Paul,
> Take care.
> "Paul Ibison" wrote:
Wednesday, March 7, 2012
Memory problems
Hi,
I'm seeing some errors in the SQL server error logs:
- -Downgrading backup log buffers from 1024K to 64K -AppDomain 14 (MVXEDU.JBNET\dbryan[runtime].13) is marked for unload due to memory pressure. -Not enough memory was available for trace when trying to run profiler
My server is Win 2003 R2 Enterprise Edition SP1, in Task Manager I see: Physical Memory (k) Total: 33549308 Avail:30361340 System Cache:7235528
I ran some System Monitor counters in two 15 min intervals and get the following:
- Memory: Avail Bytes 3.1050E+010 3.1041E+010 Memory: Pages/Sec 1.592 1.355 Process: Working Set 2639784438 2641416110 SQL Server: Buffer Cache Hit Ratio 99.853 99.846 SQL Server: Buffer Manager Total Pages 194472 194472 SQL Server: Memory Manager Total Server Memory (KB) 1555776 1555776 In the server properties, we do not have AWE enabled and min server memory = 0, max server memory = 2147483647, Index creation Memory = 0 and min memory per query = 1024 (which I think is all the defaults)
I'm new to SQL and Windows so can anyone take a look at my numbers and tell me if they look okay or if anything is giving an inidication of the memory issues I'm having. They look okay to me, but as I said I am new to this so I would love some extra opinions!
Thanks,
Isabelle
What version of SQL Server are you running?
Code Snippet
SELECT @.@.VERSIONAlso run
Code Snippet
xp_msverand post the values from Index 7, 8, 16 and 19
|||What version of SQL Server are you running?
-
Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
xp_msver
7 FileDescription NULL SQL Server Windows NT
8 FileVersion NULL 2005.090.1399.00
16 ProcessorCount 16 16
19 PhysicalMemory 32763 32763 (34354491392)
Thanks!
Isabelle
|||One thing I notice is that you are running the RTM build of SQL Server 2005 (Build 1399), along with SP1 of Windows Server 2003.
If you can, I would really try to get SP2 for SQL Server 2005 installed (Build 3042 or higher), along with SP2 for Windows Server 2003. SP2 for SQL Server 2005 has a lot of fixes related to memory management that may help some.
Since you have 32GB of RAM, you would be much better off running the 64-bit version of Windows Server 2003 along with the 64-bit version of SQL Server 2005. On 32-bit SQL Server 2005, even with AWE enabled, only certain things can use that extra memory above 4GB. Pretty much any CPU made in the last 18-24 months will support 64-bit.
|||You are running the 32bit verion of SQL on a 32bit OS is appears.
You need to enable AWE in order to use more than 2 Gigs of memory. You also need to add the /3GB and /PAE flags to the boot.ini and reboot Windows in order to address over 2 Gigs of Memory per process.
You should limit your memory to 24572 Megs. (Microsoft's new thing is 75% of memory for SQL, 25% for Windows). You can increase that number as needed up to 30715 but no higher than that.
Start there and report back if the issue is still happening.
|||Great! Thanks for the info. I will look into making those changes and see what happens. I am planning on applying the SQL 2005 SP2 to this server this Sunday, so hopefully that will help too.
Subscribe to:
Posts (Atom)