Dynamic Min Max memory settings

I have a sql server where applications suddenly and unexpectedly time out.
I noticed that we have the memory configuration set up as "Fixed" with an amount of 2576MB. In the error logs there is an error on start up of "Warning: unable to allocate 'min server memory' of 2576MB."
I would like to set the memory configuration to dynamic, but in my research I am unable to determine what are the appropriate settings for Min and Max. We have two instances of sQL Server running, with no other applications. One instance is heavy on the r
ead/write and the other instance is an archive, that sees very little action throughout the day. There is 3.7GB RAM on the server.
Since I would like to set the memory configuration to dynamic, can you steer me in the right direction?
Message posted via http://www.sqlmonster.com
Hi
Have a look at
http://support.microsoft.com/default...b;en-us;274750
Make sure you are using the correct version of the OS.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:454da8a5f79844d190b94f6cff488a26@.SQLMonster.c om...
> I have a sql server where applications suddenly and unexpectedly time out.
> I noticed that we have the memory configuration set up as "Fixed" with an
amount of 2576MB. In the error logs there is an error on start up of
"Warning: unable to allocate 'min server memory' of 2576MB."
> I would like to set the memory configuration to dynamic, but in my
research I am unable to determine what are the appropriate settings for Min
and Max. We have two instances of sQL Server running, with no other
applications. One instance is heavy on the read/write and the other instance
is an archive, that sees very little action throughout the day. There is
3.7GB RAM on the server.
> Since I would like to set the memory configuration to dynamic, can you
steer me in the right direction?
> --
> Message posted via http://www.sqlmonster.com
|||If the server is a dedicated SQL server (and no Analysis services, etc).
Simply set the max all the way to the max in SQL Enterprise manager...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:454da8a5f79844d190b94f6cff488a26@.SQLMonster.c om...
> I have a sql server where applications suddenly and unexpectedly time out.
> I noticed that we have the memory configuration set up as "Fixed" with an
amount of 2576MB. In the error logs there is an error on start up of
"Warning: unable to allocate 'min server memory' of 2576MB."
> I would like to set the memory configuration to dynamic, but in my
research I am unable to determine what are the appropriate settings for Min
and Max. We have two instances of sQL Server running, with no other
applications. One instance is heavy on the read/write and the other instance
is an archive, that sees very little action throughout the day. There is
3.7GB RAM on the server.
> Since I would like to set the memory configuration to dynamic, can you
steer me in the right direction?
> --
> Message posted via http://www.sqlmonster.com
|||Am I to assume that the min memory setting would be set at zero?
Message posted via http://www.sqlmonster.com
|||Robert - when you right-click the server & go to properties, Memory, then
click the radio button for Dynamic you see the slider for mem - the min will
be 0 meg, fine to leave there, & I would recommend against putting the max
all the way to the right - what you are saying is that SQL server can grab
100% memory on the box. MS Best practices recommend leaving approx 200-500
meg for the OS & other processes/apps you may have going on the box to avoid
an out of mem situation. Esp with SQL 7 I have seen issues with having the
max all the way over to 100% mem amount on the box.
John
"Robert Richards via SQLMonster.com" wrote:

> Am I to assume that the min memory setting would be set at zero?
> --
> Message posted via http://www.sqlmonster.com
>
|||I disagree. Microsoft actually recommends setting MAX SERVER MEMORY to 100%
of physical and 50% of swap sizes when running in Dynamic mode.
The reason is two-fold. First of all, when in Dynamic Management mode, SQL
Server can actually acquire more than the MAX SERVER SETTING value, it will
just try to get underneath that value as soon as it no longer requires.
Second, memory usage is always measured in terms of VIRTUAL MEMORY, how much
is backed by physical memory depends on what SQL Server is doing, at what
processer priority, and what the priority of the other processes are.
If you need to reserve a specific amount of physical memory, you need to
take a look at the -g start up parameter. This controls the size of the MEM
TO LEAVE region, which is 384 MB by default (128 MB for 255 MAX WORKER
THREADS plus a default value of -g at 256 MB).
Sincerely,
Anthony Thomas

"John F." <juantana@.newsgroups.microsoft.com> wrote in message
news:39491B1E-5F1A-40F2-A6FE-C97864A4713C@.microsoft.com...
Robert - when you right-click the server & go to properties, Memory, then
click the radio button for Dynamic you see the slider for mem - the min will
be 0 meg, fine to leave there, & I would recommend against putting the max
all the way to the right - what you are saying is that SQL server can grab
100% memory on the box. MS Best practices recommend leaving approx 200-500
meg for the OS & other processes/apps you may have going on the box to avoid
an out of mem situation. Esp with SQL 7 I have seen issues with having the
max all the way over to 100% mem amount on the box.
John
"Robert Richards via SQLMonster.com" wrote:

> Am I to assume that the min memory setting would be set at zero?
> --
> Message posted via http://www.sqlmonster.com
>

Dynamic Min Max memory settings

I have a sql server where applications suddenly and unexpectedly time out.
I noticed that we have the memory configuration set up as "Fixed" with an amount of 2576MB. In the error logs there is an error on start up of "Warning: unable to allocate 'min server memory' of 2576MB."
I would like to set the memory configuration to dynamic, but in my research I am unable to determine what are the appropriate settings for Min and Max. We have two instances of sQL Server running, with no other applications. One instance is heavy on the read/write and the other instance is an archive, that sees very little action throughout the day. There is 3.7GB RAM on the server.
Since I would like to set the memory configuration to dynamic, can you steer me in the right direction?
--
Message posted via http://www.sqlmonster.comHi
Have a look at
http://support.microsoft.com/default.aspx?scid=kb;en-us;274750
Make sure you are using the correct version of the OS.
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:454da8a5f79844d190b94f6cff488a26@.SQLMonster.com...
> I have a sql server where applications suddenly and unexpectedly time out.
> I noticed that we have the memory configuration set up as "Fixed" with an
amount of 2576MB. In the error logs there is an error on start up of
"Warning: unable to allocate 'min server memory' of 2576MB."
> I would like to set the memory configuration to dynamic, but in my
research I am unable to determine what are the appropriate settings for Min
and Max. We have two instances of sQL Server running, with no other
applications. One instance is heavy on the read/write and the other instance
is an archive, that sees very little action throughout the day. There is
3.7GB RAM on the server.
> Since I would like to set the memory configuration to dynamic, can you
steer me in the right direction?
> --
> Message posted via http://www.sqlmonster.com|||If the server is a dedicated SQL server (and no Analysis services, etc).
Simply set the max all the way to the max in SQL Enterprise manager...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:454da8a5f79844d190b94f6cff488a26@.SQLMonster.com...
> I have a sql server where applications suddenly and unexpectedly time out.
> I noticed that we have the memory configuration set up as "Fixed" with an
amount of 2576MB. In the error logs there is an error on start up of
"Warning: unable to allocate 'min server memory' of 2576MB."
> I would like to set the memory configuration to dynamic, but in my
research I am unable to determine what are the appropriate settings for Min
and Max. We have two instances of sQL Server running, with no other
applications. One instance is heavy on the read/write and the other instance
is an archive, that sees very little action throughout the day. There is
3.7GB RAM on the server.
> Since I would like to set the memory configuration to dynamic, can you
steer me in the right direction?
> --
> Message posted via http://www.sqlmonster.com|||Am I to assume that the min memory setting would be set at zero?
--
Message posted via http://www.sqlmonster.com|||Robert - when you right-click the server & go to properties, Memory, then
click the radio button for Dynamic you see the slider for mem - the min will
be 0 meg, fine to leave there, & I would recommend against putting the max
all the way to the right - what you are saying is that SQL server can grab
100% memory on the box. MS Best practices recommend leaving approx 200-500
meg for the OS & other processes/apps you may have going on the box to avoid
an out of mem situation. Esp with SQL 7 I have seen issues with having the
max all the way over to 100% mem amount on the box.
John
"Robert Richards via SQLMonster.com" wrote:
> Am I to assume that the min memory setting would be set at zero?
> --
> Message posted via http://www.sqlmonster.com
>|||I disagree. Microsoft actually recommends setting MAX SERVER MEMORY to 100%
of physical and 50% of swap sizes when running in Dynamic mode.
The reason is two-fold. First of all, when in Dynamic Management mode, SQL
Server can actually acquire more than the MAX SERVER SETTING value, it will
just try to get underneath that value as soon as it no longer requires.
Second, memory usage is always measured in terms of VIRTUAL MEMORY, how much
is backed by physical memory depends on what SQL Server is doing, at what
processer priority, and what the priority of the other processes are.
If you need to reserve a specific amount of physical memory, you need to
take a look at the -g start up parameter. This controls the size of the MEM
TO LEAVE region, which is 384 MB by default (128 MB for 255 MAX WORKER
THREADS plus a default value of -g at 256 MB).
Sincerely,
Anthony Thomas
"John F." <juantana@.newsgroups.microsoft.com> wrote in message
news:39491B1E-5F1A-40F2-A6FE-C97864A4713C@.microsoft.com...
Robert - when you right-click the server & go to properties, Memory, then
click the radio button for Dynamic you see the slider for mem - the min will
be 0 meg, fine to leave there, & I would recommend against putting the max
all the way to the right - what you are saying is that SQL server can grab
100% memory on the box. MS Best practices recommend leaving approx 200-500
meg for the OS & other processes/apps you may have going on the box to avoid
an out of mem situation. Esp with SQL 7 I have seen issues with having the
max all the way over to 100% mem amount on the box.
John
"Robert Richards via SQLMonster.com" wrote:
> Am I to assume that the min memory setting would be set at zero?
> --
> Message posted via http://www.sqlmonster.com
>

Dynamic Min Max memory settings

I have a sql server where applications suddenly and unexpectedly time out.
I noticed that we have the memory configuration set up as "Fixed" with an am
ount of 2576MB. In the error logs there is an error on start up of "Warning:
unable to allocate 'min server memory' of 2576MB."
I would like to set the memory configuration to dynamic, but in my research
I am unable to determine what are the appropriate settings for Min and Max.
We have two instances of sQL Server running, with no other applications. One
instance is heavy on the r
ead/write and the other instance is an archive, that sees very little action
throughout the day. There is 3.7GB RAM on the server.
Since I would like to set the memory configuration to dynamic, can you steer
me in the right direction?
Message posted via http://www.droptable.comHi
Have a look at
http://support.microsoft.com/defaul...kb;en-us;274750
Make sure you are using the correct version of the OS.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Robert Richards via droptable.com" <forum@.droptable.com> wrote in message
news:454da8a5f79844d190b94f6cff488a26@.SQ
droptable.com...
> I have a sql server where applications suddenly and unexpectedly time out.
> I noticed that we have the memory configuration set up as "Fixed" with an
amount of 2576MB. In the error logs there is an error on start up of
"Warning: unable to allocate 'min server memory' of 2576MB."
> I would like to set the memory configuration to dynamic, but in my
research I am unable to determine what are the appropriate settings for Min
and Max. We have two instances of sQL Server running, with no other
applications. One instance is heavy on the read/write and the other instance
is an archive, that sees very little action throughout the day. There is
3.7GB RAM on the server.
> Since I would like to set the memory configuration to dynamic, can you
steer me in the right direction?
> --
> Message posted via http://www.droptable.com|||If the server is a dedicated SQL server (and no Analysis services, etc).
Simply set the max all the way to the max in SQL Enterprise manager...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Robert Richards via droptable.com" <forum@.droptable.com> wrote in message
news:454da8a5f79844d190b94f6cff488a26@.SQ
droptable.com...
> I have a sql server where applications suddenly and unexpectedly time out.
> I noticed that we have the memory configuration set up as "Fixed" with an
amount of 2576MB. In the error logs there is an error on start up of
"Warning: unable to allocate 'min server memory' of 2576MB."
> I would like to set the memory configuration to dynamic, but in my
research I am unable to determine what are the appropriate settings for Min
and Max. We have two instances of sQL Server running, with no other
applications. One instance is heavy on the read/write and the other instance
is an archive, that sees very little action throughout the day. There is
3.7GB RAM on the server.
> Since I would like to set the memory configuration to dynamic, can you
steer me in the right direction?
> --
> Message posted via http://www.droptable.com|||Am I to assume that the min memory setting would be set at zero?
Message posted via http://www.droptable.com|||Robert - when you right-click the server & go to properties, Memory, then
click the radio button for Dynamic you see the slider for mem - the min will
be 0 meg, fine to leave there, & I would recommend against putting the max
all the way to the right - what you are saying is that SQL server can grab
100% memory on the box. MS Best practices recommend leaving approx 200-500
meg for the OS & other processes/apps you may have going on the box to avoid
an out of mem situation. Esp with SQL 7 I have seen issues with having the
max all the way over to 100% mem amount on the box.
John
"Robert Richards via droptable.com" wrote:

> Am I to assume that the min memory setting would be set at zero?
> --
> Message posted via http://www.droptable.com
>|||I disagree. Microsoft actually recommends setting MAX SERVER MEMORY to 100%
of physical and 50% of swap sizes when running in Dynamic mode.
The reason is two-fold. First of all, when in Dynamic Management mode, SQL
Server can actually acquire more than the MAX SERVER SETTING value, it will
just try to get underneath that value as soon as it no longer requires.
Second, memory usage is always measured in terms of VIRTUAL MEMORY, how much
is backed by physical memory depends on what SQL Server is doing, at what
processer priority, and what the priority of the other processes are.
If you need to reserve a specific amount of physical memory, you need to
take a look at the -g start up parameter. This controls the size of the MEM
TO LEAVE region, which is 384 MB by default (128 MB for 255 MAX WORKER
THREADS plus a default value of -g at 256 MB).
Sincerely,
Anthony Thomas
"John F." <juantana@.newsgroups.microsoft.com> wrote in message
news:39491B1E-5F1A-40F2-A6FE-C97864A4713C@.microsoft.com...
Robert - when you right-click the server & go to properties, Memory, then
click the radio button for Dynamic you see the slider for mem - the min will
be 0 meg, fine to leave there, & I would recommend against putting the max
all the way to the right - what you are saying is that SQL server can grab
100% memory on the box. MS Best practices recommend leaving approx 200-500
meg for the OS & other processes/apps you may have going on the box to avoid
an out of mem situation. Esp with SQL 7 I have seen issues with having the
max all the way over to 100% mem amount on the box.
John
"Robert Richards via droptable.com" wrote:

> Am I to assume that the min memory setting would be set at zero?
> --
> Message posted via http://www.droptable.com
>

dynamic killer

Hi, guys!

I want to build a scheduled job to kill any connections from server
'WS1187' built by some VB applications. The code reads something like
this:

declare @.id int
begin
set @.id = (select spid from sysprocesses where hostname='WS1187' and
program_name='Visual Basic')
kill @.id
end
GO

It doesn't compile. The error message is
"Server: Msg 170, Level 15, State 1, Line 5
Line 4: Incorrect syntax near '@.id'."

Is there any way we can kill a user process with a dynamic "spid"
built based on certain business rules?

Thanks in advance.

Gary"Gary" <rooty_hill2002@.yahoo.com.au> wrote in message
news:171bd226.0410072058.4d1b0ab0@.posting.google.c om...
> Hi, guys!
> I want to build a scheduled job to kill any connections from server
> 'WS1187' built by some VB applications. The code reads something like
> this:
> declare @.id int
> begin
> set @.id = (select spid from sysprocesses where hostname='WS1187' and
> program_name='Visual Basic')
> kill @.id
> end
> GO
> It doesn't compile. The error message is
> "Server: Msg 170, Level 15, State 1, Line 5
> Line 4: Incorrect syntax near '@.id'."
> Is there any way we can kill a user process with a dynamic "spid"
> built based on certain business rules?
> Thanks in advance.
> Gary

Check the syntax for KILL in BOL, and you'll see that it doesn't allow a
variable for the SPID, so you need to use dynamic SQL:

exec('kill ' + convert(varchar, @.id))

Simon

Dynamic Data Source

Hi,
I'm facing a problem where I have to connect to difference dataservers
in the same report. I'm not using any asp applications. I want the
connection string to change dynamically upon the users request.
For example.
Consider a report A where I have to access the same database in two
different servers(production and test).
There exists a report parameter with values prod & test. All I want to
know is that it is possible to connect to difference dataservers(or
datasources) dynamically in the same report based on user parameters.
Please note that I'm viewing the reports directly from the reporting
server. I do not have any ASP.NET application to use the
DataSources.cs. I might have to change the XML code.
Regards,
Thyagu.Yes this is possible in RS 2005. In RS 2000 you can do it but it is more
work. In RS 2000 you have to make your query expression based and refer to
the appropriate database based on the parameter. RS 2005 it is much easier
with the new support for expression based data sources.
For lots of reasons I recommend going to RS 2005.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<tdelli@.gmail.com> wrote in message
news:1136373739.521552.310060@.g49g2000cwa.googlegroups.com...
> Hi,
> I'm facing a problem where I have to connect to difference dataservers
> in the same report. I'm not using any asp applications. I want the
> connection string to change dynamically upon the users request.
> For example.
> Consider a report A where I have to access the same database in two
> different servers(production and test).
> There exists a report parameter with values prod & test. All I want to
> know is that it is possible to connect to difference dataservers(or
> datasources) dynamically in the same report based on user parameters.
> Please note that I'm viewing the reports directly from the reporting
> server. I do not have any ASP.NET application to use the
> DataSources.cs. I might have to change the XML code.
> Regards,
> Thyagu.
>