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?
>
Showing posts with label couple. Show all posts
Showing posts with label couple. Show all posts
Monday, March 12, 2012
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?
>
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?
>
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?
>
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?
>
Friday, February 24, 2012
How to comair DB objects in Development server with Production server
I have couple of Stored procedures and UDFs and Tables which I generated an
script from our development server and our database admin has run that
script on the production server.Unfortunately because of some changes I had
to generate the script two more times and now I'd like to make sure that the
objects in the producation are same as development.Is there a way to compaur
tham and to see what the potential differences are?
ThanksThere are many database comparison tools in the market. For example:
redgate.com
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"J-T" <J-T@.nospam.com> wrote in message
news:O3FEPJ8jFHA.1204@.TK2MSFTNGP12.phx.gbl...
>I have couple of Stored procedures and UDFs and Tables which I generated an
>script from our development server and our database admin has run that
>script on the production server.Unfortunately because of some changes I had
>to generate the script two more times and now I'd like to make sure that
>the objects in the producation are same as development.Is there a way to
>compaur tham and to see what the potential differences are?
>
> Thanks
>|||http://www.aspfaq.com/2209
Also, think about WinDiff if you have Visual Studio installed...
"J-T" <J-T@.nospam.com> wrote in message
news:O3FEPJ8jFHA.1204@.TK2MSFTNGP12.phx.gbl...
>I have couple of Stored procedures and UDFs and Tables which I generated an
>script from our development server and our database admin has run that
>script on the production server.Unfortunately because of some changes I had
>to generate the script two more times and now I'd like to make sure that
>the objects in the producation are same as development.Is there a way to
>compaur tham and to see what the potential differences are?
script from our development server and our database admin has run that
script on the production server.Unfortunately because of some changes I had
to generate the script two more times and now I'd like to make sure that the
objects in the producation are same as development.Is there a way to compaur
tham and to see what the potential differences are?
ThanksThere are many database comparison tools in the market. For example:
redgate.com
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"J-T" <J-T@.nospam.com> wrote in message
news:O3FEPJ8jFHA.1204@.TK2MSFTNGP12.phx.gbl...
>I have couple of Stored procedures and UDFs and Tables which I generated an
>script from our development server and our database admin has run that
>script on the production server.Unfortunately because of some changes I had
>to generate the script two more times and now I'd like to make sure that
>the objects in the producation are same as development.Is there a way to
>compaur tham and to see what the potential differences are?
>
> Thanks
>|||http://www.aspfaq.com/2209
Also, think about WinDiff if you have Visual Studio installed...
"J-T" <J-T@.nospam.com> wrote in message
news:O3FEPJ8jFHA.1204@.TK2MSFTNGP12.phx.gbl...
>I have couple of Stored procedures and UDFs and Tables which I generated an
>script from our development server and our database admin has run that
>script on the production server.Unfortunately because of some changes I had
>to generate the script two more times and now I'd like to make sure that
>the objects in the producation are same as development.Is there a way to
>compaur tham and to see what the potential differences are?
How to comair Database objects
I have couple of Stored procedures and UDFs and Tables which I generated an
script from our development server and our database admin has run that
script on the production server.Unfortunately because of some changes I had
to generate the script two more times and now I'd like to make sure that the
objects in the producation are same as development.Is there a way to compaur
tham and to see what the potential differences are?
Thanks
Hi
Using a version control system should stop this occuring. You can use dmo to
compare objects or possibly a third party application such as dbghost
www.dbghost.com or red gate compare www.red-gate.com .
John
"J-T" <J-T@.nospam.com> wrote in message
news:%23Jr%238I8jFHA.3048@.TK2MSFTNGP10.phx.gbl...
>I have couple of Stored procedures and UDFs and Tables which I generated an
>script from our development server and our database admin has run that
>script on the production server.Unfortunately because of some changes I had
>to generate the script two more times and now I'd like to make sure that
>the objects in the producation are same as development.Is there a way to
>compaur tham and to see what the potential differences are?
>
> Thanks
>
|||The red-gate is awesome.
Thanks a lot
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:u6Z%23yP8jFHA.3144@.TK2MSFTNGP12.phx.gbl...
> Hi
> Using a version control system should stop this occuring. You can use dmo
> to compare objects or possibly a third party application such as dbghost
> www.dbghost.com or red gate compare www.red-gate.com .
> John
> "J-T" <J-T@.nospam.com> wrote in message
> news:%23Jr%238I8jFHA.3048@.TK2MSFTNGP10.phx.gbl...
>
script from our development server and our database admin has run that
script on the production server.Unfortunately because of some changes I had
to generate the script two more times and now I'd like to make sure that the
objects in the producation are same as development.Is there a way to compaur
tham and to see what the potential differences are?
Thanks
Hi
Using a version control system should stop this occuring. You can use dmo to
compare objects or possibly a third party application such as dbghost
www.dbghost.com or red gate compare www.red-gate.com .
John
"J-T" <J-T@.nospam.com> wrote in message
news:%23Jr%238I8jFHA.3048@.TK2MSFTNGP10.phx.gbl...
>I have couple of Stored procedures and UDFs and Tables which I generated an
>script from our development server and our database admin has run that
>script on the production server.Unfortunately because of some changes I had
>to generate the script two more times and now I'd like to make sure that
>the objects in the producation are same as development.Is there a way to
>compaur tham and to see what the potential differences are?
>
> Thanks
>
|||The red-gate is awesome.
Thanks a lot
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:u6Z%23yP8jFHA.3144@.TK2MSFTNGP12.phx.gbl...
> Hi
> Using a version control system should stop this occuring. You can use dmo
> to compare objects or possibly a third party application such as dbghost
> www.dbghost.com or red gate compare www.red-gate.com .
> John
> "J-T" <J-T@.nospam.com> wrote in message
> news:%23Jr%238I8jFHA.3048@.TK2MSFTNGP10.phx.gbl...
>
How to comair Database objects
I have couple of Stored procedures and UDFs and Tables which I generated an
script from our development server and our database admin has run that
script on the production server.Unfortunately because of some changes I had
to generate the script two more times and now I'd like to make sure that the
objects in the producation are same as development.Is there a way to compaur
tham and to see what the potential differences are?
ThanksHi
Using a version control system should stop this occuring. You can use dmo to
compare objects or possibly a third party application such as dbghost
www.dbghost.com or red gate compare www.red-gate.com .
John
"J-T" <J-T@.nospam.com> wrote in message
news:%23Jr%238I8jFHA.3048@.TK2MSFTNGP10.phx.gbl...
>I have couple of Stored procedures and UDFs and Tables which I generated an
>script from our development server and our database admin has run that
>script on the production server.Unfortunately because of some changes I had
>to generate the script two more times and now I'd like to make sure that
>the objects in the producation are same as development.Is there a way to
>compaur tham and to see what the potential differences are?
>
> Thanks
>|||The red-gate is awesome.
Thanks a lot
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:u6Z%23yP8jFHA.3144@.TK2MSFTNGP12.phx.gbl...
> Hi
> Using a version control system should stop this occuring. You can use dmo
> to compare objects or possibly a third party application such as dbghost
> www.dbghost.com or red gate compare www.red-gate.com .
> John
> "J-T" <J-T@.nospam.com> wrote in message
> news:%23Jr%238I8jFHA.3048@.TK2MSFTNGP10.phx.gbl...
>
script from our development server and our database admin has run that
script on the production server.Unfortunately because of some changes I had
to generate the script two more times and now I'd like to make sure that the
objects in the producation are same as development.Is there a way to compaur
tham and to see what the potential differences are?
ThanksHi
Using a version control system should stop this occuring. You can use dmo to
compare objects or possibly a third party application such as dbghost
www.dbghost.com or red gate compare www.red-gate.com .
John
"J-T" <J-T@.nospam.com> wrote in message
news:%23Jr%238I8jFHA.3048@.TK2MSFTNGP10.phx.gbl...
>I have couple of Stored procedures and UDFs and Tables which I generated an
>script from our development server and our database admin has run that
>script on the production server.Unfortunately because of some changes I had
>to generate the script two more times and now I'd like to make sure that
>the objects in the producation are same as development.Is there a way to
>compaur tham and to see what the potential differences are?
>
> Thanks
>|||The red-gate is awesome.
Thanks a lot
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:u6Z%23yP8jFHA.3144@.TK2MSFTNGP12.phx.gbl...
> Hi
> Using a version control system should stop this occuring. You can use dmo
> to compare objects or possibly a third party application such as dbghost
> www.dbghost.com or red gate compare www.red-gate.com .
> John
> "J-T" <J-T@.nospam.com> wrote in message
> news:%23Jr%238I8jFHA.3048@.TK2MSFTNGP10.phx.gbl...
>
How to comair Database objects
I have couple of Stored procedures and UDFs and Tables which I generated an
script from our development server and our database admin has run that
script on the production server.Unfortunately because of some changes I had
to generate the script two more times and now I'd like to make sure that the
objects in the producation are same as development.Is there a way to compaur
tham and to see what the potential differences are?
ThanksHi
Using a version control system should stop this occuring. You can use dmo to
compare objects or possibly a third party application such as dbghost
www.dbghost.com or red gate compare www.red-gate.com .
John
"J-T" <J-T@.nospam.com> wrote in message
news:%23Jr%238I8jFHA.3048@.TK2MSFTNGP10.phx.gbl...
>I have couple of Stored procedures and UDFs and Tables which I generated an
>script from our development server and our database admin has run that
>script on the production server.Unfortunately because of some changes I had
>to generate the script two more times and now I'd like to make sure that
>the objects in the producation are same as development.Is there a way to
>compaur tham and to see what the potential differences are?
>
> Thanks
>|||The red-gate is awesome.
Thanks a lot
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:u6Z%23yP8jFHA.3144@.TK2MSFTNGP12.phx.gbl...
> Hi
> Using a version control system should stop this occuring. You can use dmo
> to compare objects or possibly a third party application such as dbghost
> www.dbghost.com or red gate compare www.red-gate.com .
> John
> "J-T" <J-T@.nospam.com> wrote in message
> news:%23Jr%238I8jFHA.3048@.TK2MSFTNGP10.phx.gbl...
>>I have couple of Stored procedures and UDFs and Tables which I generated
>>an script from our development server and our database admin has run that
>>script on the production server.Unfortunately because of some changes I
>>had to generate the script two more times and now I'd like to make sure
>>that the objects in the producation are same as development.Is there a way
>>to compaur tham and to see what the potential differences are?
>>
>> Thanks
>
script from our development server and our database admin has run that
script on the production server.Unfortunately because of some changes I had
to generate the script two more times and now I'd like to make sure that the
objects in the producation are same as development.Is there a way to compaur
tham and to see what the potential differences are?
ThanksHi
Using a version control system should stop this occuring. You can use dmo to
compare objects or possibly a third party application such as dbghost
www.dbghost.com or red gate compare www.red-gate.com .
John
"J-T" <J-T@.nospam.com> wrote in message
news:%23Jr%238I8jFHA.3048@.TK2MSFTNGP10.phx.gbl...
>I have couple of Stored procedures and UDFs and Tables which I generated an
>script from our development server and our database admin has run that
>script on the production server.Unfortunately because of some changes I had
>to generate the script two more times and now I'd like to make sure that
>the objects in the producation are same as development.Is there a way to
>compaur tham and to see what the potential differences are?
>
> Thanks
>|||The red-gate is awesome.
Thanks a lot
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:u6Z%23yP8jFHA.3144@.TK2MSFTNGP12.phx.gbl...
> Hi
> Using a version control system should stop this occuring. You can use dmo
> to compare objects or possibly a third party application such as dbghost
> www.dbghost.com or red gate compare www.red-gate.com .
> John
> "J-T" <J-T@.nospam.com> wrote in message
> news:%23Jr%238I8jFHA.3048@.TK2MSFTNGP10.phx.gbl...
>>I have couple of Stored procedures and UDFs and Tables which I generated
>>an script from our development server and our database admin has run that
>>script on the production server.Unfortunately because of some changes I
>>had to generate the script two more times and now I'd like to make sure
>>that the objects in the producation are same as development.Is there a way
>>to compaur tham and to see what the potential differences are?
>>
>> Thanks
>
Subscribe to:
Posts (Atom)