How to connect two SQL Server using private network.

Hi All:
I have two SQL Servers physically sitting next to each other. These servers
have Public IPs as 10.10.49.123 and 10.10.49.124. These two servers are also
connected through Cross Over Cables for Private Network and private non
routed IPs are 192.168.1.2 and 192.168.1.3. I have SQL Serever Transactional
Replication set up on it and I want to use private network for transferring
data between these two servers. Private network is on 2 Gigabit switch. Non
replication traffic can come vis normal way as it coming currently. Is it
possbile to implement this kind of setup or not? If so, how? Any help or
whitepaper on it will be highly appreciated.
Server 1 is Publisher and Distributor
Server 2 is Subscriber."Mark" <Mark@.discussions.microsoft.com> wrote in message
news:DADD0D4A-5010-4A57-ADDA-DE898FCCE68D@.microsoft.com...
> Hi All:
> I have two SQL Servers physically sitting next to each other. These
> servers
> have Public IPs as 10.10.49.123 and 10.10.49.124. These two servers are
> also
> connected through Cross Over Cables for Private Network and private non
> routed IPs are 192.168.1.2 and 192.168.1.3. I have SQL Serever
> Transactional
> Replication set up on it and I want to use private network for
> transferring
> data between these two servers. Private network is on 2 Gigabit switch.
> Non
> replication traffic can come vis normal way as it coming currently. Is it
> possbile to implement this kind of setup or not? If so, how? Any help or
> whitepaper on it will be highly appreciated.
> Server 1 is Publisher and Distributor
> Server 2 is Subscriber.
>
Just put entries in the hosts file of each server so when Server 1 goes to
connect to Server 2, its name resolves to 192.168.1.2 and not 10.10.49.123.
David|||I tried this and this didnt work. Why is it that from command prompt on
Server 1, I can ping Server 2's private IP address and yet when I add them
into Host File and then try, it fails.
Anything else that I need to do other than adding entries into Host file?
This is what I did so far.
"David Browne" wrote:

> "Mark" <Mark@.discussions.microsoft.com> wrote in message
> news:DADD0D4A-5010-4A57-ADDA-DE898FCCE68D@.microsoft.com...
> Just put entries in the hosts file of each server so when Server 1 goes to
> connect to Server 2, its name resolves to 192.168.1.2 and not 10.10.49.123
.
> David
>
>|||After you add the entry to the host file, if you ping the server by name
which IP is used?
Have you rebooted after changing the host file?
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:F4C36A18-2D24-4A63-96A6-906126167A12@.microsoft.com...
> I tried this and this didnt work. Why is it that from command prompt on
> Server 1, I can ping Server 2's private IP address and yet when I add them
> into Host File and then try, it fails.
> Anything else that I need to do other than adding entries into Host file?
> This is what I did so far.
>
> "David Browne" wrote:
>
are
non
switch.
it
or
to
10.10.49.123.|||Thanks to both for replying to my problem.
It still shows 10.10.49.124. When I try to conenct to SQL Server using
private IP address in Query Analyzer, I get error saying: Unable to connect
to SQL Server. SQL Server doesn't exist or Access denied.
Is it cached or am I missing something.
So, to clearly understand this, all I have to do is add entry in the host
file to the private IP address and it should worlk. Correct' Anything else?
"Jim Underwood" wrote:

> After you add the entry to the host file, if you ping the server by name
> which IP is used?
> Have you rebooted after changing the host file?
> "Mark" <Mark@.discussions.microsoft.com> wrote in message
> news:F4C36A18-2D24-4A63-96A6-906126167A12@.microsoft.com...
> are
> non
> switch.
> it
> or
> to
> 10.10.49.123.
>
>|||I believe adding the IP to the host file will allow you to refernce the
server by name instead of IP. I would expect that the IP address would work
regardless, assuming your network is setup correctly.
Are you even able to ping the server by IP address? If not then your
network is not set up correctly.
Are you running query analyzer on the server, or on a client that is not
part of the private network?
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:E0B6F1EC-FFE5-4E5B-946F-4E34BE507D71@.microsoft.com...
> Thanks to both for replying to my problem.
> It still shows 10.10.49.124. When I try to conenct to SQL Server using
> private IP address in Query Analyzer, I get error saying: Unable to
connect
> to SQL Server. SQL Server doesn't exist or Access denied.
> Is it cached or am I missing something.
> So, to clearly understand this, all I have to do is add entry in the host
> file to the private IP address and it should worlk. Correct' Anything
else?
> "Jim Underwood" wrote:
>
on
them
file?
These
servers
private
currently. Is
help
goes|||Correct, but it sounds like you have a different issue. If you can't
connect to Server 2 from Server 1 using the internal ip address, then
the host name won't work either. Sounds like either a bad cross-over
cable, or the internal network cards are disabled.

>From server1, with an IP Address of 192.168.1.2, get a command prompt
and ping the other internal IP address (192.168.1.3). If you don't get
a connection, then you got network issues.
Stu|||Thanks guys. Let me work on it and I will put my feedback here. I have to us
e
different cables and see what happens next. It could be a hardware issue mor
e
than a software.
Once again thanks a lot for all your help
"Stu" wrote:

> Correct, but it sounds like you have a different issue. If you can't
> connect to Server 2 from Server 1 using the internal ip address, then
> the host name won't work either. Sounds like either a bad cross-over
> cable, or the internal network cards are disabled.
>
> and ping the other internal IP address (192.168.1.3). If you don't get
> a connection, then you got network issues.
> Stu
>

How to connect to a mirrored SQL Server?

SQL Configuration: Two 2003 Servers (OS Std ver) with SQL 2005 (Std ver) configured for db mirroring. (The servers aren't clustered.)

Web.config configuration string is using "ServerA".

If we fail Server A, then Server B will change roles to "primary" in about 20 seconds (we have confirmed this via SQL Mgt Console). BUT...our web app is still pointing to Server A and doesn't seem to know there is a fail over. SO - how can I make the web app aware of the failure?

Server A and Server B aren't in a cluster - and I understand a cluster's virtual server would be referenced in the connection string. Is there a way to make the web app automatically switch to Server B (without a cluster configuration)?

From what I understand, the cluster configuration would require Ent Ed. of OS and SQL and a big fat check!!

Thanks.

-Kevin

One problem Database Mirroring did not make it to SQL Server 2005 shipping but expected in a service pack in the future. In the mean time Cluster is the way to go. Hope this helps.|||

I understand the mirror feature is for "testing only" - which is what I am doing. I have read that SQL 2005 SP1 would be released in early 2006 and MS would fully support the mirror capability.

From what I have read about mirroring, it seems that server clustering isn't necessary - that SQL can mirror the data. From a ASP.NET perspective, I just didn't know how it would know if a server is down and "switch" the connection to the failover server. Cluster's Vitual server makes that seamless - maybe that is the only way for ASP.NET apps to work with the failover server??

So, to clarify what I intended...how can ASP.Net use the failover server unless you are a cluster or unless you modify the connection string when Server A fails?

-Kevin

|||

SQL Server 2000 came with Log Shipping which is what Microsoft improved to get database Mirroring but you still need the Enterprise edition to get all fancy components of SQL Server. And for how to know when the server is down you will need monitoring tools like MOM (Microsoft Operation Management ) server or database specific tools like Embarcadero, I have used both and the cost is almost the same with basic cluster hardware without the System admin requirement. I would add the MOM implementation was enterprise wide so the cost was very high. Hope this helps.

How to configure SQL Server with SAN

We have 5 SQL servers and plan to use Dell SAN data storage. We try to
figure out how to configure the data, log, temp db drivers so that we
have a better performance.
1. Should we seperate the data, log, and temp db into three different
drivers?
2. What RAID level will be best for each of them?
3. Also, we use linked servers among different SQL Servers, the
performance using the linked servers is bad. Are there any better way
to configure it or avoid using linked server to improve the performance
when we use SAN?
Thanks
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Data and log should be on separate LUN and RAID sets, preferably on
different processor engines by default. Tempdb is just another database for
this purpose. I disagree with Joe and suggest maximum RAM in the SAN with
much of it configured as write cache. My experience suggests that you will
maximize overall performance that way, but that does depend on the nature of
your application and data.
RAID-10 is best, but expensive. If you must lower costs, use RAID5 for data
and RAID10 for logs.
Linked server performance is bad because the remote server often has to dump
entire tables across the link. Try using OPENQUERY with search arguments so
the result set coming across the link is smaller. Another option is to
purchase larger host servers and consolidate your databases onto fewer
systems, thus turning your remote query into a simple cross-database query.
--
Geoff N. Hiten
SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"cindy huang" <cindyxqhuang@.hotmail.com> wrote in message
news:uERj2GmbDHA.616@.TK2MSFTNGP11.phx.gbl...
> We have 5 SQL servers and plan to use Dell SAN data storage. We try to
> figure out how to configure the data, log, temp db drivers so that we
> have a better performance.
> 1. Should we seperate the data, log, and temp db into three different
> drivers?
> 2. What RAID level will be best for each of them?
> 3. Also, we use linked servers among different SQL Servers, the
> performance using the linked servers is bad. Are there any better way
> to configure it or avoid using linked server to improve the performance
> when we use SAN?
> Thanks
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||test the disk system performance with IOmeter from
http://sourceforge.net/
opinions are many, actual results are better
>--Original Message--
>We have 5 SQL servers and plan to use Dell SAN data
storage. We try to
>figure out how to configure the data, log, temp db
drivers so that we
>have a better performance.
>1. Should we seperate the data, log, and temp db into
three different
>drivers?
>2. What RAID level will be best for each of them?
>3. Also, we use linked servers among different SQL
Servers, the
>performance using the linked servers is bad. Are there
any better way
>to configure it or avoid using linked server to improve
the performance
>when we use SAN?
>Thanks
>
>*** Sent via Developersdex http://www.developersdex.com
***
>Don't just participate in USENET...get rewarded for it!
>.
>

How to configure SQL Server with SAN

We have 5 SQL servers and plan to use Dell SAN data storage. We try to
figure out how to configure the data, log, temp db drivers so that we
have a better performance.
1. Should we seperate the data, log, and temp db into three different
drivers?
2. What RAID level will be best for each of them?
3. Also, we use linked servers among different SQL Servers, the
performance using the linked servers is bad. Are there any better way
to configure it or avoid using linked server to improve the performance
when we use SAN?
Thanks
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Data and log should be on separate LUN and RAID sets, preferably on
different processor engines by default. Tempdb is just another database for
this purpose. I disagree with Joe and suggest maximum RAM in the SAN with
much of it configured as write cache. My experience suggests that you will
maximize overall performance that way, but that does depend on the nature of
your application and data.
RAID-10 is best, but expensive. If you must lower costs, use RAID5 for data
and RAID10 for logs.
Linked server performance is bad because the remote server often has to dump
entire tables across the link. Try using OPENQUERY with search arguments so
the result set coming across the link is smaller. Another option is to
purchase larger host servers and consolidate your databases onto fewer
systems, thus turning your remote query into a simple cross-database query.
--
Geoff N. Hiten
SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"cindy huang" <cindyxqhuang@.hotmail.com> wrote in message
news:uERj2GmbDHA.616@.TK2MSFTNGP11.phx.gbl...
> We have 5 SQL servers and plan to use Dell SAN data storage. We try to
> figure out how to configure the data, log, temp db drivers so that we
> have a better performance.
> 1. Should we seperate the data, log, and temp db into three different
> drivers?
> 2. What RAID level will be best for each of them?
> 3. Also, we use linked servers among different SQL Servers, the
> performance using the linked servers is bad. Are there any better way
> to configure it or avoid using linked server to improve the performance
> when we use SAN?
> Thanks
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||test the disk system performance with IOmeter from
http://sourceforge.net/
opinions are many, actual results are better
>--Original Message--
>We have 5 SQL servers and plan to use Dell SAN data
storage. We try to
>figure out how to configure the data, log, temp db
drivers so that we
>have a better performance.
>1. Should we seperate the data, log, and temp db into
three different
>drivers?
>2. What RAID level will be best for each of them?
>3. Also, we use linked servers among different SQL
Servers, the
>performance using the linked servers is bad. Are there
any better way
>to configure it or avoid using linked server to improve
the performance
>when we use SAN?
>Thanks
>
>*** Sent via Developersdex http://www.developersdex.com
***
>Don't just participate in USENET...get rewarded for it!
>.
>

How to configure MemToLeave memory option?

I have been encountering errors on a couple of my SQL Servers and in
researching the problem I think that I may need to configure more
MemToLeave space. I have not been able to find any articles that
suggest how to go about calculating how much MemToLeave I need though.
I use AWE to a fixed 5.5 GB of memory dedicated to SQL Server. Does the
MemToLeave come from that 5.5 GB? How does one go about figuring out
how much MemToLeave to assign?
ThanksIt would help if you posted what those errors were so we could answer a
little more intelligently. How much memory do you have overall?
This should get you started.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqldev/html/sqldev_01262004.asp
--
Andrew J. Kelly SQL MVP
<pshroads@.gmail.com> wrote in message
news:1142979152.765275.3730@.e56g2000cwe.googlegroups.com...
>I have been encountering errors on a couple of my SQL Servers and in
> researching the problem I think that I may need to configure more
> MemToLeave space. I have not been able to find any articles that
> suggest how to go about calculating how much MemToLeave I need though.
> I use AWE to a fixed 5.5 GB of memory dedicated to SQL Server. Does the
> MemToLeave come from that 5.5 GB? How does one go about figuring out
> how much MemToLeave to assign?
> Thanks
>|||If you are sure it is the MemToLeave problem, you may use the -g384 server
parameter to increase from the 256M default (SQL2K). As far as I know, -g384
is max allowed for SQL2K.|||There is no limit as such that I know of.
--
Andrew J. Kelly SQL MVP
"sqlfan" <sqlfan@.discussions.microsoft.com> wrote in message
news:C68D4540-26D9-42F6-97C9-8B0CA436F75C@.microsoft.com...
> If you are sure it is the MemToLeave problem, you may use the -g384 server
> parameter to increase from the 256M default (SQL2K). As far as I
> know, -g384
> is max allowed for SQL2K.|||Thanks for your reply. Sorry for the delay in responding.
I get errors in the error log like the one below. I've researched this
errror and some common causes are a network packet size that is too
large or the use of the sql_variant datatype -- neither of which
applies to us.
I remember reading (can't find the article now) that another
possibility is large query plans that can't fit in the regualar buffer
pool. We do have some large query plans.
The error I see is:
2006-03-27 20:29:08.07 spid391 WARNING: Failed to reserve contiguous
memory of Size= 10420224.
2006-03-27 20:29:08.12 spid391 Buffer Distribution: Stolen=7693
Free=6 Procedures=56173
Inram=0 Dirty=14091 Kept=0
I/O=0, Latched=225, Other=561812
2006-03-27 20:29:08.13 spid391 Buffer Counts: Commited=640000
Target=640000 Hashed=576128
InternalReservation=756 ExternalReservation=0 Min Free=512 Visible=334272
2006-03-27 20:29:08.13 spid391 Procedure Cache: TotalProcs=15340
TotalPages=56173 InUsePages=23115
2006-03-27 20:29:08.13 spid391 Dynamic Memory Manager: Stolen=63866
OS Reserved=5184
OS Committed=5154
OS In Use=3749
Query Plan=56067 Optimizer=0
General=6156
Utilities=83 Connection=5036
2006-03-27 20:29:08.13 spid391 Global Memory Objects: Resource=1452
Locks=116
SQLCache=3800 Replication=2
LockBytes=2 ServerGlobal=475
Xact=43
2006-03-27 20:29:08.13 spid391 Query Memory Manager: Grants=0
Waiting=0 Maximum=228540 Available=228540
2006-03-27 20:29:08.30 spid391 WARNING: Failed to reserve contiguous
memory of Size= 10420224.
2006-03-27 20:29:08.32 spid391 Buffer Distribution: Stolen=16802
Free=7088 Procedures=39982
Inram=0 Dirty=14091 Kept=0
I/O=0, Latched=225, Other=561812
2006-03-27 20:29:08.32 spid391 Buffer Counts: Commited=640000
Target=640000 Hashed=576128
InternalReservation=756 ExternalReservation=0 Min Free=512 Visible=334272
2006-03-27 20:29:08.32 spid391 Procedure Cache: TotalProcs=14310
TotalPages=39982 InUsePages=14128
2006-03-27 20:29:08.32 spid391 Dynamic Memory Manager: Stolen=56784
OS Reserved=4120
OS Committed=4094
OS In Use=2696
Query Plan=48530 Optimizer=0
General=5562
Utilities=83 Connection=5032
2006-03-27 20:29:08.32 spid391 Global Memory Objects: Resource=1452
Locks=116
SQLCache=3206 Replication=2
LockBytes=2 ServerGlobal=475
Xact=43
Thanks!|||Thanks for your reply. Sorry for the delay in responding.
I get errors in the error log like the one below. I've researched this
errror and some common causes are a network packet size that is too
large or the use of the sql_variant datatype -- neither of which
applies to us.
I remember reading (can't find the article now) that another
possibility is large query plans that can't fit in the regualar buffer
pool. We do have some large query plans.
The error I see is:
2006-03-27 20:29:08.07 spid391 WARNING: Failed to reserve contiguous
memory of Size= 10420224.
2006-03-27 20:29:08.12 spid391 Buffer Distribution: Stolen=7693
Free=6 Procedures=56173
Inram=0 Dirty=14091 Kept=0
I/O=0, Latched=225, Other=561812
2006-03-27 20:29:08.13 spid391 Buffer Counts: Commited=640000
Target=640000 Hashed=576128
InternalReservation=756 ExternalReservation=0 Min Free=512 Visible=334272
2006-03-27 20:29:08.13 spid391 Procedure Cache: TotalProcs=15340
TotalPages=56173 InUsePages=23115
2006-03-27 20:29:08.13 spid391 Dynamic Memory Manager: Stolen=63866
OS Reserved=5184
OS Committed=5154
OS In Use=3749
Query Plan=56067 Optimizer=0
General=6156
Utilities=83 Connection=5036
2006-03-27 20:29:08.13 spid391 Global Memory Objects: Resource=1452
Locks=116
SQLCache=3800 Replication=2
LockBytes=2 ServerGlobal=475
Xact=43
2006-03-27 20:29:08.13 spid391 Query Memory Manager: Grants=0
Waiting=0 Maximum=228540 Available=228540
2006-03-27 20:29:08.30 spid391 WARNING: Failed to reserve contiguous
memory of Size= 10420224.
2006-03-27 20:29:08.32 spid391 Buffer Distribution: Stolen=16802
Free=7088 Procedures=39982
Inram=0 Dirty=14091 Kept=0
I/O=0, Latched=225, Other=561812
2006-03-27 20:29:08.32 spid391 Buffer Counts: Commited=640000
Target=640000 Hashed=576128
InternalReservation=756 ExternalReservation=0 Min Free=512 Visible=334272
2006-03-27 20:29:08.32 spid391 Procedure Cache: TotalProcs=14310
TotalPages=39982 InUsePages=14128
2006-03-27 20:29:08.32 spid391 Dynamic Memory Manager: Stolen=56784
OS Reserved=4120
OS Committed=4094
OS In Use=2696
Query Plan=48530 Optimizer=0
General=5562
Utilities=83 Connection=5032
2006-03-27 20:29:08.32 spid391 Global Memory Objects: Resource=1452
Locks=116
SQLCache=3206 Replication=2
LockBytes=2 ServerGlobal=475
Xact=43
Thanks!|||Some things that can contribute to this are extended stored procedures that
fragment the memtoleave Are you using any XP's? Using a lot of Blobs can
also tax the memtoleave.
--
Andrew J. Kelly SQL MVP
<pshroads@.gmail.com> wrote in message
news:1143656215.037080.65060@.i40g2000cwc.googlegroups.com...
> Thanks for your reply. Sorry for the delay in responding.
> I get errors in the error log like the one below. I've researched this
> errror and some common causes are a network packet size that is too
> large or the use of the sql_variant datatype -- neither of which
> applies to us.
> I remember reading (can't find the article now) that another
> possibility is large query plans that can't fit in the regualar buffer
> pool. We do have some large query plans.
> The error I see is:
> 2006-03-27 20:29:08.07 spid391 WARNING: Failed to reserve contiguous
> memory of Size= 10420224.
> 2006-03-27 20:29:08.12 spid391 Buffer Distribution: Stolen=7693
> Free=6 Procedures=56173
> Inram=0 Dirty=14091 Kept=0
> I/O=0, Latched=225, Other=561812
> 2006-03-27 20:29:08.13 spid391 Buffer Counts: Commited=640000
> Target=640000 Hashed=576128
> InternalReservation=756 ExternalReservation=0 Min Free=512 Visible=> 334272
> 2006-03-27 20:29:08.13 spid391 Procedure Cache: TotalProcs=15340
> TotalPages=56173 InUsePages=23115
> 2006-03-27 20:29:08.13 spid391 Dynamic Memory Manager: Stolen=63866
> OS Reserved=5184
> OS Committed=5154
> OS In Use=3749
> Query Plan=56067 Optimizer=0
> General=6156
> Utilities=83 Connection=5036
> 2006-03-27 20:29:08.13 spid391 Global Memory Objects: Resource=1452
> Locks=116
> SQLCache=3800 Replication=2
> LockBytes=2 ServerGlobal=475
> Xact=43
> 2006-03-27 20:29:08.13 spid391 Query Memory Manager: Grants=0
> Waiting=0 Maximum=228540 Available=228540
> 2006-03-27 20:29:08.30 spid391 WARNING: Failed to reserve contiguous
> memory of Size= 10420224.
> 2006-03-27 20:29:08.32 spid391 Buffer Distribution: Stolen=16802
> Free=7088 Procedures=39982
> Inram=0 Dirty=14091 Kept=0
> I/O=0, Latched=225, Other=561812
> 2006-03-27 20:29:08.32 spid391 Buffer Counts: Commited=640000
> Target=640000 Hashed=576128
> InternalReservation=756 ExternalReservation=0 Min Free=512 Visible=> 334272
> 2006-03-27 20:29:08.32 spid391 Procedure Cache: TotalProcs=14310
> TotalPages=39982 InUsePages=14128
> 2006-03-27 20:29:08.32 spid391 Dynamic Memory Manager: Stolen=56784
> OS Reserved=4120
> OS Committed=4094
> OS In Use=2696
> Query Plan=48530 Optimizer=0
> General=5562
> Utilities=83 Connection=5032
> 2006-03-27 20:29:08.32 spid391 Global Memory Objects: Resource=1452
> Locks=116
> SQLCache=3206 Replication=2
> LockBytes=2 ServerGlobal=475
> Xact=43
>
> Thanks!
>|||The only XPs that I can think of would be 3rd party XPs for the SQL
LiteSpeed backup compression utility. We take transaction log backups
every 5 minutes for 3 different databases so that XP is called 3 times
every 5 minutes. I don't think we use many BLOBs but I'll look in to
that.
Does increasing the memtoleave help with fragmentation?|||The only XPs that I can think of would be 3rd party XPs for the SQL
LiteSpeed backup compression utility. We take transaction log backups
every 5 minutes for 3 different databases so that XP is called 3 times
every 5 minutes. I don't think we use many BLOBs but I'll look in to
that.
Does increasing the memtoleave help with fragmentation?|||What version of litespeed? Some of the earlier versions were know to
fragment the memtoleave and cause these issues. If you don't have the
latest version I suggest you get it and see if that helps.
--
Andrew J. Kelly SQL MVP
<pshroads@.gmail.com> wrote in message
news:1143736360.517465.194290@.u72g2000cwu.googlegroups.com...
> The only XPs that I can think of would be 3rd party XPs for the SQL
> LiteSpeed backup compression utility. We take transaction log backups
> every 5 minutes for 3 different databases so that XP is called 3 times
> every 5 minutes. I don't think we use many BLOBs but I'll look in to
> that.
> Does increasing the memtoleave help with fragmentation?
>

How to configure MemToLeave memory option?

I have been encountering errors on a couple of my SQL Servers and in
researching the problem I think that I may need to configure more
MemToLeave space. I have not been able to find any articles that
suggest how to go about calculating how much MemToLeave I need though.
I use AWE to a fixed 5.5 GB of memory dedicated to SQL Server. Does the
MemToLeave come from that 5.5 GB? How does one go about figuring out
how much MemToLeave to assign?
ThanksIt would help if you posted what those errors were so we could answer a
little more intelligently. How much memory do you have overall?
This should get you started.
http://msdn.microsoft.com/library/d...ev_01262004.asp
Andrew J. Kelly SQL MVP
<pshroads@.gmail.com> wrote in message
news:1142979152.765275.3730@.e56g2000cwe.googlegroups.com...
>I have been encountering errors on a couple of my SQL Servers and in
> researching the problem I think that I may need to configure more
> MemToLeave space. I have not been able to find any articles that
> suggest how to go about calculating how much MemToLeave I need though.
> I use AWE to a fixed 5.5 GB of memory dedicated to SQL Server. Does the
> MemToLeave come from that 5.5 GB? How does one go about figuring out
> how much MemToLeave to assign?
> Thanks
>|||If you are sure it is the MemToLeave problem, you may use the -g384 server
parameter to increase from the 256M default (SQL2K). As far as I know, -g38
4
is max allowed for SQL2K.|||There is no limit as such that I know of.
Andrew J. Kelly SQL MVP
"sqlfan" <sqlfan@.discussions.microsoft.com> wrote in message
news:C68D4540-26D9-42F6-97C9-8B0CA436F75C@.microsoft.com...
> If you are sure it is the MemToLeave problem, you may use the -g384 server
> parameter to increase from the 256M default (SQL2K). As far as I
> know, -g384
> is max allowed for SQL2K.|||Thanks for your reply. Sorry for the delay in responding.
I get errors in the error log like the one below. I've researched this
errror and some common causes are a network packet size that is too
large or the use of the sql_variant datatype -- neither of which
applies to us.
I remember reading (can't find the article now) that another
possibility is large query plans that can't fit in the regualar buffer
pool. We do have some large query plans.
The error I see is:
2006-03-27 20:29:08.07 spid391 WARNING: Failed to reserve contiguous
memory of Size= 10420224.
2006-03-27 20:29:08.12 spid391 Buffer Distribution: Stolen=7693
Free=6 Procedures=56173
Inram=0 Dirty=14091 Kept=0
I/O=0, Latched=225, Other=561812
2006-03-27 20:29:08.13 spid391 Buffer Counts: Commited=640000
Target=640000 Hashed=576128
InternalReservation=756 ExternalReservation=0 Min Free=512 Visible=
334272
2006-03-27 20:29:08.13 spid391 Procedure Cache: TotalProcs=15340
TotalPages=56173 InUsePages=23115
2006-03-27 20:29:08.13 spid391 Dynamic Memory Manager: Stolen=63866
OS Reserved=5184
OS Committed=5154
OS In Use=3749
Query Plan=56067 Optimizer=0
General=6156
Utilities=83 Connection=5036
2006-03-27 20:29:08.13 spid391 Global Memory Objects: Resource=1452
Locks=116
SQLCache=3800 Replication=2
LockBytes=2 ServerGlobal=475
Xact=43
2006-03-27 20:29:08.13 spid391 Query Memory Manager: Grants=0
Waiting=0 Maximum=228540 Available=228540
2006-03-27 20:29:08.30 spid391 WARNING: Failed to reserve contiguous
memory of Size= 10420224.
2006-03-27 20:29:08.32 spid391 Buffer Distribution: Stolen=16802
Free=7088 Procedures=39982
Inram=0 Dirty=14091 Kept=0
I/O=0, Latched=225, Other=561812
2006-03-27 20:29:08.32 spid391 Buffer Counts: Commited=640000
Target=640000 Hashed=576128
InternalReservation=756 ExternalReservation=0 Min Free=512 Visible=
334272
2006-03-27 20:29:08.32 spid391 Procedure Cache: TotalProcs=14310
TotalPages=39982 InUsePages=14128
2006-03-27 20:29:08.32 spid391 Dynamic Memory Manager: Stolen=56784
OS Reserved=4120
OS Committed=4094
OS In Use=2696
Query Plan=48530 Optimizer=0
General=5562
Utilities=83 Connection=5032
2006-03-27 20:29:08.32 spid391 Global Memory Objects: Resource=1452
Locks=116
SQLCache=3206 Replication=2
LockBytes=2 ServerGlobal=475
Xact=43
Thanks!|||Thanks for your reply. Sorry for the delay in responding.
I get errors in the error log like the one below. I've researched this
errror and some common causes are a network packet size that is too
large or the use of the sql_variant datatype -- neither of which
applies to us.
I remember reading (can't find the article now) that another
possibility is large query plans that can't fit in the regualar buffer
pool. We do have some large query plans.
The error I see is:
2006-03-27 20:29:08.07 spid391 WARNING: Failed to reserve contiguous
memory of Size= 10420224.
2006-03-27 20:29:08.12 spid391 Buffer Distribution: Stolen=7693
Free=6 Procedures=56173
Inram=0 Dirty=14091 Kept=0
I/O=0, Latched=225, Other=561812
2006-03-27 20:29:08.13 spid391 Buffer Counts: Commited=640000
Target=640000 Hashed=576128
InternalReservation=756 ExternalReservation=0 Min Free=512 Visible=
334272
2006-03-27 20:29:08.13 spid391 Procedure Cache: TotalProcs=15340
TotalPages=56173 InUsePages=23115
2006-03-27 20:29:08.13 spid391 Dynamic Memory Manager: Stolen=63866
OS Reserved=5184
OS Committed=5154
OS In Use=3749
Query Plan=56067 Optimizer=0
General=6156
Utilities=83 Connection=5036
2006-03-27 20:29:08.13 spid391 Global Memory Objects: Resource=1452
Locks=116
SQLCache=3800 Replication=2
LockBytes=2 ServerGlobal=475
Xact=43
2006-03-27 20:29:08.13 spid391 Query Memory Manager: Grants=0
Waiting=0 Maximum=228540 Available=228540
2006-03-27 20:29:08.30 spid391 WARNING: Failed to reserve contiguous
memory of Size= 10420224.
2006-03-27 20:29:08.32 spid391 Buffer Distribution: Stolen=16802
Free=7088 Procedures=39982
Inram=0 Dirty=14091 Kept=0
I/O=0, Latched=225, Other=561812
2006-03-27 20:29:08.32 spid391 Buffer Counts: Commited=640000
Target=640000 Hashed=576128
InternalReservation=756 ExternalReservation=0 Min Free=512 Visible=
334272
2006-03-27 20:29:08.32 spid391 Procedure Cache: TotalProcs=14310
TotalPages=39982 InUsePages=14128
2006-03-27 20:29:08.32 spid391 Dynamic Memory Manager: Stolen=56784
OS Reserved=4120
OS Committed=4094
OS In Use=2696
Query Plan=48530 Optimizer=0
General=5562
Utilities=83 Connection=5032
2006-03-27 20:29:08.32 spid391 Global Memory Objects: Resource=1452
Locks=116
SQLCache=3206 Replication=2
LockBytes=2 ServerGlobal=475
Xact=43
Thanks!|||Some things that can contribute to this are extended stored procedures that
fragment the memtoleave Are you using any XP's? Using a lot of Blobs can
also tax the memtoleave.
Andrew J. Kelly SQL MVP
<pshroads@.gmail.com> wrote in message
news:1143656215.037080.65060@.i40g2000cwc.googlegroups.com...
> Thanks for your reply. Sorry for the delay in responding.
> I get errors in the error log like the one below. I've researched this
> errror and some common causes are a network packet size that is too
> large or the use of the sql_variant datatype -- neither of which
> applies to us.
> I remember reading (can't find the article now) that another
> possibility is large query plans that can't fit in the regualar buffer
> pool. We do have some large query plans.
> The error I see is:
> 2006-03-27 20:29:08.07 spid391 WARNING: Failed to reserve contiguous
> memory of Size= 10420224.
> 2006-03-27 20:29:08.12 spid391 Buffer Distribution: Stolen=7693
> Free=6 Procedures=56173
> Inram=0 Dirty=14091 Kept=0
> I/O=0, Latched=225, Other=561812
> 2006-03-27 20:29:08.13 spid391 Buffer Counts: Commited=640000
> Target=640000 Hashed=576128
> InternalReservation=756 ExternalReservation=0 Min Free=512 Visible=
> 334272
> 2006-03-27 20:29:08.13 spid391 Procedure Cache: TotalProcs=15340
> TotalPages=56173 InUsePages=23115
> 2006-03-27 20:29:08.13 spid391 Dynamic Memory Manager: Stolen=63866
> OS Reserved=5184
> OS Committed=5154
> OS In Use=3749
> Query Plan=56067 Optimizer=0
> General=6156
> Utilities=83 Connection=5036
> 2006-03-27 20:29:08.13 spid391 Global Memory Objects: Resource=1452
> Locks=116
> SQLCache=3800 Replication=2
> LockBytes=2 ServerGlobal=475
> Xact=43
> 2006-03-27 20:29:08.13 spid391 Query Memory Manager: Grants=0
> Waiting=0 Maximum=228540 Available=228540
> 2006-03-27 20:29:08.30 spid391 WARNING: Failed to reserve contiguous
> memory of Size= 10420224.
> 2006-03-27 20:29:08.32 spid391 Buffer Distribution: Stolen=16802
> Free=7088 Procedures=39982
> Inram=0 Dirty=14091 Kept=0
> I/O=0, Latched=225, Other=561812
> 2006-03-27 20:29:08.32 spid391 Buffer Counts: Commited=640000
> Target=640000 Hashed=576128
> InternalReservation=756 ExternalReservation=0 Min Free=512 Visible=
> 334272
> 2006-03-27 20:29:08.32 spid391 Procedure Cache: TotalProcs=14310
> TotalPages=39982 InUsePages=14128
> 2006-03-27 20:29:08.32 spid391 Dynamic Memory Manager: Stolen=56784
> OS Reserved=4120
> OS Committed=4094
> OS In Use=2696
> Query Plan=48530 Optimizer=0
> General=5562
> Utilities=83 Connection=5032
> 2006-03-27 20:29:08.32 spid391 Global Memory Objects: Resource=1452
> Locks=116
> SQLCache=3206 Replication=2
> LockBytes=2 ServerGlobal=475
> Xact=43
>
> Thanks!
>|||The only XPs that I can think of would be 3rd party XPs for the SQL
LiteSpeed backup compression utility. We take transaction log backups
every 5 minutes for 3 different databases so that XP is called 3 times
every 5 minutes. I don't think we use many BLOBs but I'll look in to
that.
Does increasing the memtoleave help with fragmentation?|||The only XPs that I can think of would be 3rd party XPs for the SQL
LiteSpeed backup compression utility. We take transaction log backups
every 5 minutes for 3 different databases so that XP is called 3 times
every 5 minutes. I don't think we use many BLOBs but I'll look in to
that.
Does increasing the memtoleave help with fragmentation?|||What version of litespeed? Some of the earlier versions were know to
fragment the memtoleave and cause these issues. If you don't have the
latest version I suggest you get it and see if that helps.
Andrew J. Kelly SQL MVP
<pshroads@.gmail.com> wrote in message
news:1143736360.517465.194290@.u72g2000cwu.googlegroups.com...
> The only XPs that I can think of would be 3rd party XPs for the SQL
> LiteSpeed backup compression utility. We take transaction log backups
> every 5 minutes for 3 different databases so that XP is called 3 times
> every 5 minutes. I don't think we use many BLOBs but I'll look in to
> that.
> Does increasing the memtoleave help with fragmentation?
>

How to configure MemToLeave memory option?

I have been encountering errors on a couple of my SQL Servers and in
researching the problem I think that I may need to configure more
MemToLeave space. I have not been able to find any articles that
suggest how to go about calculating how much MemToLeave I need though.
I use AWE to a fixed 5.5 GB of memory dedicated to SQL Server. Does the
MemToLeave come from that 5.5 GB? How does one go about figuring out
how much MemToLeave to assign?
Thanks
It would help if you posted what those errors were so we could answer a
little more intelligently. How much memory do you have overall?
This should get you started.
http://msdn.microsoft.com/library/de...v_01262004.asp
Andrew J. Kelly SQL MVP
<pshroads@.gmail.com> wrote in message
news:1142979152.765275.3730@.e56g2000cwe.googlegrou ps.com...
>I have been encountering errors on a couple of my SQL Servers and in
> researching the problem I think that I may need to configure more
> MemToLeave space. I have not been able to find any articles that
> suggest how to go about calculating how much MemToLeave I need though.
> I use AWE to a fixed 5.5 GB of memory dedicated to SQL Server. Does the
> MemToLeave come from that 5.5 GB? How does one go about figuring out
> how much MemToLeave to assign?
> Thanks
>
|||If you are sure it is the MemToLeave problem, you may use the -g384 server
parameter to increase from the 256M default (SQL2K). As far as I know, -g384
is max allowed for SQL2K.
|||There is no limit as such that I know of.
Andrew J. Kelly SQL MVP
"sqlfan" <sqlfan@.discussions.microsoft.com> wrote in message
news:C68D4540-26D9-42F6-97C9-8B0CA436F75C@.microsoft.com...
> If you are sure it is the MemToLeave problem, you may use the -g384 server
> parameter to increase from the 256M default (SQL2K). As far as I
> know, -g384
> is max allowed for SQL2K.
|||Thanks for your reply. Sorry for the delay in responding.
I get errors in the error log like the one below. I've researched this
errror and some common causes are a network packet size that is too
large or the use of the sql_variant datatype -- neither of which
applies to us.
I remember reading (can't find the article now) that another
possibility is large query plans that can't fit in the regualar buffer
pool. We do have some large query plans.
The error I see is:
2006-03-27 20:29:08.07 spid391 WARNING: Failed to reserve contiguous
memory of Size= 10420224.
2006-03-27 20:29:08.12 spid391 Buffer Distribution: Stolen=7693
Free=6 Procedures=56173
Inram=0 Dirty=14091 Kept=0
I/O=0, Latched=225, Other=561812
2006-03-27 20:29:08.13 spid391 Buffer Counts: Commited=640000
Target=640000 Hashed=576128
InternalReservation=756 ExternalReservation=0 Min Free=512 Visible=
334272
2006-03-27 20:29:08.13 spid391 Procedure Cache: TotalProcs=15340
TotalPages=56173 InUsePages=23115
2006-03-27 20:29:08.13 spid391 Dynamic Memory Manager: Stolen=63866
OS Reserved=5184
OS Committed=5154
OS In Use=3749
Query Plan=56067 Optimizer=0
General=6156
Utilities=83 Connection=5036
2006-03-27 20:29:08.13 spid391 Global Memory Objects: Resource=1452
Locks=116
SQLCache=3800 Replication=2
LockBytes=2 ServerGlobal=475
Xact=43
2006-03-27 20:29:08.13 spid391 Query Memory Manager: Grants=0
Waiting=0 Maximum=228540 Available=228540
2006-03-27 20:29:08.30 spid391 WARNING: Failed to reserve contiguous
memory of Size= 10420224.
2006-03-27 20:29:08.32 spid391 Buffer Distribution: Stolen=16802
Free=7088 Procedures=39982
Inram=0 Dirty=14091 Kept=0
I/O=0, Latched=225, Other=561812
2006-03-27 20:29:08.32 spid391 Buffer Counts: Commited=640000
Target=640000 Hashed=576128
InternalReservation=756 ExternalReservation=0 Min Free=512 Visible=
334272
2006-03-27 20:29:08.32 spid391 Procedure Cache: TotalProcs=14310
TotalPages=39982 InUsePages=14128
2006-03-27 20:29:08.32 spid391 Dynamic Memory Manager: Stolen=56784
OS Reserved=4120
OS Committed=4094
OS In Use=2696
Query Plan=48530 Optimizer=0
General=5562
Utilities=83 Connection=5032
2006-03-27 20:29:08.32 spid391 Global Memory Objects: Resource=1452
Locks=116
SQLCache=3206 Replication=2
LockBytes=2 ServerGlobal=475
Xact=43
Thanks!
|||Thanks for your reply. Sorry for the delay in responding.
I get errors in the error log like the one below. I've researched this
errror and some common causes are a network packet size that is too
large or the use of the sql_variant datatype -- neither of which
applies to us.
I remember reading (can't find the article now) that another
possibility is large query plans that can't fit in the regualar buffer
pool. We do have some large query plans.
The error I see is:
2006-03-27 20:29:08.07 spid391 WARNING: Failed to reserve contiguous
memory of Size= 10420224.
2006-03-27 20:29:08.12 spid391 Buffer Distribution: Stolen=7693
Free=6 Procedures=56173
Inram=0 Dirty=14091 Kept=0
I/O=0, Latched=225, Other=561812
2006-03-27 20:29:08.13 spid391 Buffer Counts: Commited=640000
Target=640000 Hashed=576128
InternalReservation=756 ExternalReservation=0 Min Free=512 Visible=
334272
2006-03-27 20:29:08.13 spid391 Procedure Cache: TotalProcs=15340
TotalPages=56173 InUsePages=23115
2006-03-27 20:29:08.13 spid391 Dynamic Memory Manager: Stolen=63866
OS Reserved=5184
OS Committed=5154
OS In Use=3749
Query Plan=56067 Optimizer=0
General=6156
Utilities=83 Connection=5036
2006-03-27 20:29:08.13 spid391 Global Memory Objects: Resource=1452
Locks=116
SQLCache=3800 Replication=2
LockBytes=2 ServerGlobal=475
Xact=43
2006-03-27 20:29:08.13 spid391 Query Memory Manager: Grants=0
Waiting=0 Maximum=228540 Available=228540
2006-03-27 20:29:08.30 spid391 WARNING: Failed to reserve contiguous
memory of Size= 10420224.
2006-03-27 20:29:08.32 spid391 Buffer Distribution: Stolen=16802
Free=7088 Procedures=39982
Inram=0 Dirty=14091 Kept=0
I/O=0, Latched=225, Other=561812
2006-03-27 20:29:08.32 spid391 Buffer Counts: Commited=640000
Target=640000 Hashed=576128
InternalReservation=756 ExternalReservation=0 Min Free=512 Visible=
334272
2006-03-27 20:29:08.32 spid391 Procedure Cache: TotalProcs=14310
TotalPages=39982 InUsePages=14128
2006-03-27 20:29:08.32 spid391 Dynamic Memory Manager: Stolen=56784
OS Reserved=4120
OS Committed=4094
OS In Use=2696
Query Plan=48530 Optimizer=0
General=5562
Utilities=83 Connection=5032
2006-03-27 20:29:08.32 spid391 Global Memory Objects: Resource=1452
Locks=116
SQLCache=3206 Replication=2
LockBytes=2 ServerGlobal=475
Xact=43
Thanks!
|||Some things that can contribute to this are extended stored procedures that
fragment the memtoleave Are you using any XP's? Using a lot of Blobs can
also tax the memtoleave.
Andrew J. Kelly SQL MVP
<pshroads@.gmail.com> wrote in message
news:1143656215.037080.65060@.i40g2000cwc.googlegro ups.com...
> Thanks for your reply. Sorry for the delay in responding.
> I get errors in the error log like the one below. I've researched this
> errror and some common causes are a network packet size that is too
> large or the use of the sql_variant datatype -- neither of which
> applies to us.
> I remember reading (can't find the article now) that another
> possibility is large query plans that can't fit in the regualar buffer
> pool. We do have some large query plans.
> The error I see is:
> 2006-03-27 20:29:08.07 spid391 WARNING: Failed to reserve contiguous
> memory of Size= 10420224.
> 2006-03-27 20:29:08.12 spid391 Buffer Distribution: Stolen=7693
> Free=6 Procedures=56173
> Inram=0 Dirty=14091 Kept=0
> I/O=0, Latched=225, Other=561812
> 2006-03-27 20:29:08.13 spid391 Buffer Counts: Commited=640000
> Target=640000 Hashed=576128
> InternalReservation=756 ExternalReservation=0 Min Free=512 Visible=
> 334272
> 2006-03-27 20:29:08.13 spid391 Procedure Cache: TotalProcs=15340
> TotalPages=56173 InUsePages=23115
> 2006-03-27 20:29:08.13 spid391 Dynamic Memory Manager: Stolen=63866
> OS Reserved=5184
> OS Committed=5154
> OS In Use=3749
> Query Plan=56067 Optimizer=0
> General=6156
> Utilities=83 Connection=5036
> 2006-03-27 20:29:08.13 spid391 Global Memory Objects: Resource=1452
> Locks=116
> SQLCache=3800 Replication=2
> LockBytes=2 ServerGlobal=475
> Xact=43
> 2006-03-27 20:29:08.13 spid391 Query Memory Manager: Grants=0
> Waiting=0 Maximum=228540 Available=228540
> 2006-03-27 20:29:08.30 spid391 WARNING: Failed to reserve contiguous
> memory of Size= 10420224.
> 2006-03-27 20:29:08.32 spid391 Buffer Distribution: Stolen=16802
> Free=7088 Procedures=39982
> Inram=0 Dirty=14091 Kept=0
> I/O=0, Latched=225, Other=561812
> 2006-03-27 20:29:08.32 spid391 Buffer Counts: Commited=640000
> Target=640000 Hashed=576128
> InternalReservation=756 ExternalReservation=0 Min Free=512 Visible=
> 334272
> 2006-03-27 20:29:08.32 spid391 Procedure Cache: TotalProcs=14310
> TotalPages=39982 InUsePages=14128
> 2006-03-27 20:29:08.32 spid391 Dynamic Memory Manager: Stolen=56784
> OS Reserved=4120
> OS Committed=4094
> OS In Use=2696
> Query Plan=48530 Optimizer=0
> General=5562
> Utilities=83 Connection=5032
> 2006-03-27 20:29:08.32 spid391 Global Memory Objects: Resource=1452
> Locks=116
> SQLCache=3206 Replication=2
> LockBytes=2 ServerGlobal=475
> Xact=43
>
> Thanks!
>
|||The only XPs that I can think of would be 3rd party XPs for the SQL
LiteSpeed backup compression utility. We take transaction log backups
every 5 minutes for 3 different databases so that XP is called 3 times
every 5 minutes. I don't think we use many BLOBs but I'll look in to
that.
Does increasing the memtoleave help with fragmentation?
|||The only XPs that I can think of would be 3rd party XPs for the SQL
LiteSpeed backup compression utility. We take transaction log backups
every 5 minutes for 3 different databases so that XP is called 3 times
every 5 minutes. I don't think we use many BLOBs but I'll look in to
that.
Does increasing the memtoleave help with fragmentation?
|||What version of litespeed? Some of the earlier versions were know to
fragment the memtoleave and cause these issues. If you don't have the
latest version I suggest you get it and see if that helps.
Andrew J. Kelly SQL MVP
<pshroads@.gmail.com> wrote in message
news:1143736360.517465.194290@.u72g2000cwu.googlegr oups.com...
> The only XPs that I can think of would be 3rd party XPs for the SQL
> LiteSpeed backup compression utility. We take transaction log backups
> every 5 minutes for 3 different databases so that XP is called 3 times
> every 5 minutes. I don't think we use many BLOBs but I'll look in to
> that.
> Does increasing the memtoleave help with fragmentation?
>

How to configure MemToLeave memory option?

I have been encountering errors on a couple of my SQL Servers and in
researching the problem I think that I may need to configure more
MemToLeave space. I have not been able to find any articles that
suggest how to go about calculating how much MemToLeave I need though.
I use AWE to a fixed 5.5 GB of memory dedicated to SQL Server. Does the
MemToLeave come from that 5.5 GB? How does one go about figuring out
how much MemToLeave to assign?
ThanksIt would help if you posted what those errors were so we could answer a
little more intelligently. How much memory do you have overall?
This should get you started.
http://msdn.microsoft.com/library/d...ev_01262004.asp
Andrew J. Kelly SQL MVP
<pshroads@.gmail.com> wrote in message
news:1142979152.765275.3730@.e56g2000cwe.googlegroups.com...
>I have been encountering errors on a couple of my SQL Servers and in
> researching the problem I think that I may need to configure more
> MemToLeave space. I have not been able to find any articles that
> suggest how to go about calculating how much MemToLeave I need though.
> I use AWE to a fixed 5.5 GB of memory dedicated to SQL Server. Does the
> MemToLeave come from that 5.5 GB? How does one go about figuring out
> how much MemToLeave to assign?
> Thanks
>|||If you are sure it is the MemToLeave problem, you may use the -g384 server
parameter to increase from the 256M default (SQL2K). As far as I know, -g38
4
is max allowed for SQL2K.|||There is no limit as such that I know of.
Andrew J. Kelly SQL MVP
"sqlfan" <sqlfan@.discussions.microsoft.com> wrote in message
news:C68D4540-26D9-42F6-97C9-8B0CA436F75C@.microsoft.com...
> If you are sure it is the MemToLeave problem, you may use the -g384 server
> parameter to increase from the 256M default (SQL2K). As far as I
> know, -g384
> is max allowed for SQL2K.|||Thanks for your reply. Sorry for the delay in responding.
I get errors in the error log like the one below. I've researched this
errror and some common causes are a network packet size that is too
large or the use of the sql_variant datatype -- neither of which
applies to us.
I remember reading (can't find the article now) that another
possibility is large query plans that can't fit in the regualar buffer
pool. We do have some large query plans.
The error I see is:
2006-03-27 20:29:08.07 spid391 WARNING: Failed to reserve contiguous
memory of Size= 10420224.
2006-03-27 20:29:08.12 spid391 Buffer Distribution: Stolen=7693
Free=6 Procedures=56173
Inram=0 Dirty=14091 Kept=0
I/O=0, Latched=225, Other=561812
2006-03-27 20:29:08.13 spid391 Buffer Counts: Commited=640000
Target=640000 Hashed=576128
InternalReservation=756 ExternalReservation=0 Min Free=512 Visible=
334272
2006-03-27 20:29:08.13 spid391 Procedure Cache: TotalProcs=15340
TotalPages=56173 InUsePages=23115
2006-03-27 20:29:08.13 spid391 Dynamic Memory Manager: Stolen=63866
OS Reserved=5184
OS Committed=5154
OS In Use=3749
Query Plan=56067 Optimizer=0
General=6156
Utilities=83 Connection=5036
2006-03-27 20:29:08.13 spid391 Global Memory Objects: Resource=1452
Locks=116
SQLCache=3800 Replication=2
LockBytes=2 ServerGlobal=475
Xact=43
2006-03-27 20:29:08.13 spid391 Query Memory Manager: Grants=0
Waiting=0 Maximum=228540 Available=228540
2006-03-27 20:29:08.30 spid391 WARNING: Failed to reserve contiguous
memory of Size= 10420224.
2006-03-27 20:29:08.32 spid391 Buffer Distribution: Stolen=16802
Free=7088 Procedures=39982
Inram=0 Dirty=14091 Kept=0
I/O=0, Latched=225, Other=561812
2006-03-27 20:29:08.32 spid391 Buffer Counts: Commited=640000
Target=640000 Hashed=576128
InternalReservation=756 ExternalReservation=0 Min Free=512 Visible=
334272
2006-03-27 20:29:08.32 spid391 Procedure Cache: TotalProcs=14310
TotalPages=39982 InUsePages=14128
2006-03-27 20:29:08.32 spid391 Dynamic Memory Manager: Stolen=56784
OS Reserved=4120
OS Committed=4094
OS In Use=2696
Query Plan=48530 Optimizer=0
General=5562
Utilities=83 Connection=5032
2006-03-27 20:29:08.32 spid391 Global Memory Objects: Resource=1452
Locks=116
SQLCache=3206 Replication=2
LockBytes=2 ServerGlobal=475
Xact=43
Thanks!|||Thanks for your reply. Sorry for the delay in responding.
I get errors in the error log like the one below. I've researched this
errror and some common causes are a network packet size that is too
large or the use of the sql_variant datatype -- neither of which
applies to us.
I remember reading (can't find the article now) that another
possibility is large query plans that can't fit in the regualar buffer
pool. We do have some large query plans.
The error I see is:
2006-03-27 20:29:08.07 spid391 WARNING: Failed to reserve contiguous
memory of Size= 10420224.
2006-03-27 20:29:08.12 spid391 Buffer Distribution: Stolen=7693
Free=6 Procedures=56173
Inram=0 Dirty=14091 Kept=0
I/O=0, Latched=225, Other=561812
2006-03-27 20:29:08.13 spid391 Buffer Counts: Commited=640000
Target=640000 Hashed=576128
InternalReservation=756 ExternalReservation=0 Min Free=512 Visible=
334272
2006-03-27 20:29:08.13 spid391 Procedure Cache: TotalProcs=15340
TotalPages=56173 InUsePages=23115
2006-03-27 20:29:08.13 spid391 Dynamic Memory Manager: Stolen=63866
OS Reserved=5184
OS Committed=5154
OS In Use=3749
Query Plan=56067 Optimizer=0
General=6156
Utilities=83 Connection=5036
2006-03-27 20:29:08.13 spid391 Global Memory Objects: Resource=1452
Locks=116
SQLCache=3800 Replication=2
LockBytes=2 ServerGlobal=475
Xact=43
2006-03-27 20:29:08.13 spid391 Query Memory Manager: Grants=0
Waiting=0 Maximum=228540 Available=228540
2006-03-27 20:29:08.30 spid391 WARNING: Failed to reserve contiguous
memory of Size= 10420224.
2006-03-27 20:29:08.32 spid391 Buffer Distribution: Stolen=16802
Free=7088 Procedures=39982
Inram=0 Dirty=14091 Kept=0
I/O=0, Latched=225, Other=561812
2006-03-27 20:29:08.32 spid391 Buffer Counts: Commited=640000
Target=640000 Hashed=576128
InternalReservation=756 ExternalReservation=0 Min Free=512 Visible=
334272
2006-03-27 20:29:08.32 spid391 Procedure Cache: TotalProcs=14310
TotalPages=39982 InUsePages=14128
2006-03-27 20:29:08.32 spid391 Dynamic Memory Manager: Stolen=56784
OS Reserved=4120
OS Committed=4094
OS In Use=2696
Query Plan=48530 Optimizer=0
General=5562
Utilities=83 Connection=5032
2006-03-27 20:29:08.32 spid391 Global Memory Objects: Resource=1452
Locks=116
SQLCache=3206 Replication=2
LockBytes=2 ServerGlobal=475
Xact=43
Thanks!|||Some things that can contribute to this are extended stored procedures that
fragment the memtoleave Are you using any XP's? Using a lot of Blobs can
also tax the memtoleave.
Andrew J. Kelly SQL MVP
<pshroads@.gmail.com> wrote in message
news:1143656215.037080.65060@.i40g2000cwc.googlegroups.com...
> Thanks for your reply. Sorry for the delay in responding.
> I get errors in the error log like the one below. I've researched this
> errror and some common causes are a network packet size that is too
> large or the use of the sql_variant datatype -- neither of which
> applies to us.
> I remember reading (can't find the article now) that another
> possibility is large query plans that can't fit in the regualar buffer
> pool. We do have some large query plans.
> The error I see is:
> 2006-03-27 20:29:08.07 spid391 WARNING: Failed to reserve contiguous
> memory of Size= 10420224.
> 2006-03-27 20:29:08.12 spid391 Buffer Distribution: Stolen=7693
> Free=6 Procedures=56173
> Inram=0 Dirty=14091 Kept=0
> I/O=0, Latched=225, Other=561812
> 2006-03-27 20:29:08.13 spid391 Buffer Counts: Commited=640000
> Target=640000 Hashed=576128
> InternalReservation=756 ExternalReservation=0 Min Free=512 Visible=
> 334272
> 2006-03-27 20:29:08.13 spid391 Procedure Cache: TotalProcs=15340
> TotalPages=56173 InUsePages=23115
> 2006-03-27 20:29:08.13 spid391 Dynamic Memory Manager: Stolen=63866
> OS Reserved=5184
> OS Committed=5154
> OS In Use=3749
> Query Plan=56067 Optimizer=0
> General=6156
> Utilities=83 Connection=5036
> 2006-03-27 20:29:08.13 spid391 Global Memory Objects: Resource=1452
> Locks=116
> SQLCache=3800 Replication=2
> LockBytes=2 ServerGlobal=475
> Xact=43
> 2006-03-27 20:29:08.13 spid391 Query Memory Manager: Grants=0
> Waiting=0 Maximum=228540 Available=228540
> 2006-03-27 20:29:08.30 spid391 WARNING: Failed to reserve contiguous
> memory of Size= 10420224.
> 2006-03-27 20:29:08.32 spid391 Buffer Distribution: Stolen=16802
> Free=7088 Procedures=39982
> Inram=0 Dirty=14091 Kept=0
> I/O=0, Latched=225, Other=561812
> 2006-03-27 20:29:08.32 spid391 Buffer Counts: Commited=640000
> Target=640000 Hashed=576128
> InternalReservation=756 ExternalReservation=0 Min Free=512 Visible=
> 334272
> 2006-03-27 20:29:08.32 spid391 Procedure Cache: TotalProcs=14310
> TotalPages=39982 InUsePages=14128
> 2006-03-27 20:29:08.32 spid391 Dynamic Memory Manager: Stolen=56784
> OS Reserved=4120
> OS Committed=4094
> OS In Use=2696
> Query Plan=48530 Optimizer=0
> General=5562
> Utilities=83 Connection=5032
> 2006-03-27 20:29:08.32 spid391 Global Memory Objects: Resource=1452
> Locks=116
> SQLCache=3206 Replication=2
> LockBytes=2 ServerGlobal=475
> Xact=43
>
> Thanks!
>|||The only XPs that I can think of would be 3rd party XPs for the SQL
LiteSpeed backup compression utility. We take transaction log backups
every 5 minutes for 3 different databases so that XP is called 3 times
every 5 minutes. I don't think we use many BLOBs but I'll look in to
that.
Does increasing the memtoleave help with fragmentation?|||The only XPs that I can think of would be 3rd party XPs for the SQL
LiteSpeed backup compression utility. We take transaction log backups
every 5 minutes for 3 different databases so that XP is called 3 times
every 5 minutes. I don't think we use many BLOBs but I'll look in to
that.
Does increasing the memtoleave help with fragmentation?|||What version of litespeed? Some of the earlier versions were know to
fragment the memtoleave and cause these issues. If you don't have the
latest version I suggest you get it and see if that helps.
Andrew J. Kelly SQL MVP
<pshroads@.gmail.com> wrote in message
news:1143736360.517465.194290@.u72g2000cwu.googlegroups.com...
> The only XPs that I can think of would be 3rd party XPs for the SQL
> LiteSpeed backup compression utility. We take transaction log backups
> every 5 minutes for 3 different databases so that XP is called 3 times
> every 5 minutes. I don't think we use many BLOBs but I'll look in to
> that.
> Does increasing the memtoleave help with fragmentation?
>

How to cluster SQL 2005?

Hey everyone,
We are about to try to cluster SQL 2005 for HA on two windows 2003 ent.
servers. Anyone know of a step-by-step white paper? Do I need a "Quorum"
disk like our Exchange cluster?
Are the steps for clustering Windows the same as when we did our Exchange
2003 cluster? If so, then I only need the SQL part of the steps.
TIA,
Clayton
The steps for clustering Windows are the same, regardless of what app you
will be running. As for SQL Server 2005 cluster installs, check out:
http://msdn2.microsoft.com/en-us/library/ms179530.aspx
Also, check out www.clusterhelp.com for resources and training.
HTH
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
..
"Clayton Sutton" <none@.none.com> wrote in message
news:trl6h.9238$8d4.2542@.fe01.news.easynews.com...
Hey everyone,
We are about to try to cluster SQL 2005 for HA on two windows 2003 ent.
servers. Anyone know of a step-by-step white paper? Do I need a "Quorum"
disk like our Exchange cluster?
Are the steps for clustering Windows the same as when we did our Exchange
2003 cluster? If so, then I only need the SQL part of the steps.
TIA,
Clayton
|||Hello clayton
it is important to have quorum disk because it is a resource for clustering
in windows environment.
U could find step by step installation on http://msdn2.microsoft.com
Khwaja Arshaduddin
"Clayton Sutton" wrote:

> Hey everyone,
> We are about to try to cluster SQL 2005 for HA on two windows 2003 ent.
> servers. Anyone know of a step-by-step white paper? Do I need a "Quorum"
> disk like our Exchange cluster?
> Are the steps for clustering Windows the same as when we did our Exchange
> 2003 cluster? If so, then I only need the SQL part of the steps.
> TIA,
>
> Clayton
>
>
|||Thanks guys!
Clayton
P.S.: I just wrote an iTunes podcast tutorial and just want to publicize
it. You can find it at: http://www.nikoli.net/itunepod
*************************************
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eHmYWhACHHA.5064@.TK2MSFTNGP02.phx.gbl...
> The steps for clustering Windows are the same, regardless of what app you
> will be running. As for SQL Server 2005 cluster installs, check out:
> http://msdn2.microsoft.com/en-us/library/ms179530.aspx
> Also, check out www.clusterhelp.com for resources and training.
> HTH
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> .
> "Clayton Sutton" <none@.none.com> wrote in message
> news:trl6h.9238$8d4.2542@.fe01.news.easynews.com...
> Hey everyone,
> We are about to try to cluster SQL 2005 for HA on two windows 2003 ent.
> servers. Anyone know of a step-by-step white paper? Do I need a "Quorum"
> disk like our Exchange cluster?
> Are the steps for clustering Windows the same as when we did our Exchange
> 2003 cluster? If so, then I only need the SQL part of the steps.
> TIA,
>
> Clayton
>