Help Start with MSDE???SA password??

While installing the MSDE I get the folowing error:
A storng SA password is required forsecurity reasons.
Please use SAPWD switch to supply the same.
Refer to readme for more details.
Setup will now exit.

Thanks in advance

MustaphaThe installation instructions that came with your version of MSDE should include help on how to do this.

From where did you get MSDE?

Terri|||Hi,
If you need to change the sa password after the install, and you can uses the osql command line tool that will be available through your computer's command prompt.

If you don't know what a strong password is - it's one that meets certain criteria that make it harder for a hacker to break. I belive the min length is eight characters, it should contain a mix of at least three types of characters - upper case, lower case, numerals, punctuation marks (spaces are allowed). I'd opt for a 'pass-phrase'. Because you can use spaces, etc... you can use a phrase rather than just a string of alpha numeric charcaters. A medium length phrase, 40 charcaters or so, that mixes case and has some numerics is eaier to remember (without the need to write it down; a security problem in itself), and hard to crack.

Never leave the sa password blank.|||

I hope you get this. I am having the exact same trouble and the instructions don't tell me how to deal with this. I didn't have this problem the first time I loaded the program back in September but since my computer crashed and I had to reinstall everything, I am now having to fix this. I used the disc that came with the book and I also tried the version from this site.

Do you have any ideas?

Carolyn (crowbare@.sbcglobal.net)

|||

The reason you're seeing this message is that the MSDE installation executable expects you to pass the parameter to the installer when you run it. It's not intuitive that you have to do this, so when you double-click the download file you will see an "error" indicating that you must specify a strong password.

To avoid this, open a command prompt and navigate to the directory in which the installation file was saved. Call the help on that file by executing my_msde_install_file.exe /?. It will tell you the appropriate switch to use to set the password at runtime.

Hope this helps.

Help required: Using OLE Automation SPs

Hi,
I am using the sp_OACreate, sp_OAMethod, sp_OASetProperty etc... SPs to
execute a piece of dynamic SQL.
My dynamic SQL needs to return a resultset so I basically did the following:
SET @.vSQL = 'SELECT * FROM MyTable'
EXEC sp_OAMethod @.vObject, 'ExecuteWithResults', @.vResult OUTPUT, @.vSQL
I now have the contents of MyTable in @.vResult (which is a handle). I used
the following to return the results as a string into @.pResults (which is a
varchar):
EXEC @.vHR = sp_OAMethod @.vResult, 'GetRangeString', @.pResults OUTPUT
This is all well and good but I don't want the results returned as a string,
I want it returned as a recordset which will ultimately be the output from
the SP that wraps all of this up. Furthermore the max string that I can
return is 8000 chars (i.e. max length of a varchar) so with any decent sized
dataset this doesn't return everything anyway.
So my question is: Once I have the results as handled by @.vResult...how do I
navigate over it and return the results as a normal rowset? The
GetRangeString function is obviously not the answer.
Thanks in advance
Regards
Jamie Thomson
An SSIS blog - http://blogs.conchango.com/jamietho...ategory/71.aspxWhat is @.vObject a handle to? Normally you don't need to use OLE Automation
SPs to execute dynamic SQL. I think you will have to explain a little bit
better why you want to shove a table into an output variable and then
process it back into a resultset again. Why not just EXEC('SELECT * FROM
MyTable') ... (and I assume that dynamic SQL is actually required here for
some reason, and that your query is a little more complicated than this...
otherwise please read http://www.sommarskog.se/dynamic_sql.html)...
http://www.aspfaq.com/
(Reverse address to reply.)
"Jamie Thomson" <jamiekthomson@.removethisbit.blueyonder.co.uk> wrote in
message news:xKNSd.196724$K7.125153@.fe2.news.blueyonder.co.uk...
> Hi,
> I am using the sp_OACreate, sp_OAMethod, sp_OASetProperty etc... SPs to
> execute a piece of dynamic SQL.
> My dynamic SQL needs to return a resultset so I basically did the
following:
> SET @.vSQL = 'SELECT * FROM MyTable'
> EXEC sp_OAMethod @.vObject, 'ExecuteWithResults', @.vResult OUTPUT, @.vSQL
> I now have the contents of MyTable in @.vResult (which is a handle). I used
> the following to return the results as a string into @.pResults (which is a
> varchar):
> EXEC @.vHR = sp_OAMethod @.vResult, 'GetRangeString', @.pResults OUTPUT
> This is all well and good but I don't want the results returned as a
string,
> I want it returned as a recordset which will ultimately be the output from
> the SP that wraps all of this up. Furthermore the max string that I can
> return is 8000 chars (i.e. max length of a varchar) so with any decent
sized
> dataset this doesn't return everything anyway.
>
> So my question is: Once I have the results as handled by @.vResult...how do
I
> navigate over it and return the results as a normal rowset? The
> GetRangeString function is obviously not the answer.
> Thanks in advance
>
> Regards
> Jamie Thomson
> An SSIS blog - http://blogs.conchango.com/jamietho...ategory/71.aspx
>
>|||Hi Aaron,
Yes, you're absolutely right. My real query is alot more complicated than
this. :)
The reason I am doing it is a permissions issue. Under our current
application security model the calling app (an ASP.Net app) doesn't have
access to MyTable therefore EXEC(...) doesn't work. I am not allowed to
change the security model either (its not under my control) :o( but I AM
allowed to access it using this method.
Regards
Jamie Thomson
An SSIS blog - http://blogs.conchango.com/jamietho...ategory/71.aspx
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:udXW3iSGFHA.3472@.TK2MSFTNGP09.phx.gbl...
> What is @.vObject a handle to? Normally you don't need to use OLE
> Automation
> SPs to execute dynamic SQL. I think you will have to explain a little bit
> better why you want to shove a table into an output variable and then
> process it back into a resultset again. Why not just EXEC('SELECT * FROM
> MyTable') ... (and I assume that dynamic SQL is actually required here for
> some reason, and that your query is a little more complicated than this...
> otherwise please read http://www.sommarskog.se/dynamic_sql.html)...
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Jamie Thomson" <jamiekthomson@.removethisbit.blueyonder.co.uk> wrote in
> message news:xKNSd.196724$K7.125153@.fe2.news.blueyonder.co.uk...
> following:
> string,
> sized
> I
>

Help required!

Hi all

I am creating a web services application in Visual Studio Dot net
which links to a MS SQL database.

I need to create a form which lets the user pick his field of
choice(doesnt matter what it is..) using drop down boxes,radio buttons
etc and query the database according to what he has picked and
retrieve only those columns and rows.

The thing is, I do not want to hardcode the queries using
OleDbDataAdapter or SqlDataAdapter, but want the query to be generated
on the fly, once the user picks his fields from the form.

I'd greatly appreciate any help.

Thanks,
mSeems you are looking to provide ad-hoc reporting capabilities. To avoid
hard-coding queries you would have to access the SQL Server meta-data to
retrieve information about tables and to provide options for users to pick
tables and columns. You can start by looking at the information schema views
in the SQL Server documentation. Here is one example of a query that returns
tables and columns:

SELECT c.table_catalog AS 'Database Name',
c.table_schema AS 'Owner',
c.table_name AS 'Table Name',
c.column_name AS 'Column Name',
c.data_type AS 'Column Data Type'
FROM INFORMATION_SCHEMA.COLUMNS c
INNER JOIN INFORMATION_SCHEMA.TABLES t
ON t.table_catalog = c.table_catalog
AND t.table_schema = c.table_schema
AND t.table_name = c.table_name
WHERE t.table_type = 'BASE TABLE'

Since the table and column names may not mean much to users, you may want to
build a mapping table with descriptive names. That involves some hard-coding
and maintenance (best stored in a table that you can join easily) to update
when a new table/column is added or changed, but greatly improves the user
experience.

Next you would allow selection of columns and placing filters (the WHERE
clause of the query). A good idea is to enforce a requirement to have at
least one condition. Users tend to forget about adding filters and could
possibly dump the whole table out. At this point it is very important to
check the column type and perform verification on any parameters entered.

Last is to build a dynamic query and return the results to the user. A good
reading for dynamic queries is Erland Sommarskog's article here:
http://www.sommarskog.se/dynamic_sql.html. Pay attention to the SQL
injection section as this is very common for ad-hoc query systems.

This is a very simplified approach to what you are looking for. It can get a
lot more complex if you need to provide special operations like summary
queries, or if you decide to provide capabilities to join multiple tables.

HTH,

Plamen Ratchev
http://www.SQLStudio.com|||On 27 Feb 2007 14:40:23 -0800, madhumita.iyengar@.gmail.com wrote:

Quote:

Originally Posted by

>Hi all
>
>I am creating a web services application in Visual Studio Dot net
>which links to a MS SQL database.
>
>I need to create a form which lets the user pick his field of
>choice(doesnt matter what it is..) using drop down boxes,radio buttons
>etc and query the database according to what he has picked and
>retrieve only those columns and rows.
>
>The thing is, I do not want to hardcode the queries using
>OleDbDataAdapter or SqlDataAdapter, but want the query to be generated
>on the fly, once the user picks his fields from the form.
>
>I'd greatly appreciate any help.
>
>Thanks,
>m

In addition to Plamen's suggestions, you might look into the report
builder part of Sql Server Reporting Services. The programmer sets up
the appropriate data model, and the user configures the report. There
is something of a learning curve for the user but designing a report
can be fairly trivial.|||On 27 Feb 2007 14:40:23 -0800, madhumita.iyengar@.gmail.com wrote:

Quote:

Originally Posted by

>Hi all
>
>I am creating a web services application in Visual Studio Dot net
>which links to a MS SQL database.
>
>I need to create a form which lets the user pick his field of
>choice(doesnt matter what it is..) using drop down boxes,radio buttons
>etc and query the database according to what he has picked and
>retrieve only those columns and rows.
>
>The thing is, I do not want to hardcode the queries using
>OleDbDataAdapter or SqlDataAdapter, but want the query to be generated
>on the fly, once the user picks his fields from the form.
>
>I'd greatly appreciate any help.
>
>Thanks,
>m

And you'll likely get more replies if you're specific in your post,
rather than just Help Required!.

Help required using Variables and Comparing them in SSIS

Hi i am New to ssis and Scripting in ssis what i was trying to do was to compare few variables which i populate using execute sql tasks and determine which process needs to kick off based on Task success or Failure

are the variables i am populating Mapping them to result set in execute sql task.

This is the criteria for script task to Fail or success

I am not able to populate the varibales with the Values from Execute Sql and also i am encountering errors in the script task.

I am Declaring ex: myvar11 as variables and then using it , please help me as i am new and guide me for the right approach. and can this logic be implemented in ssis for example like && operator used in script.
End IF

I've done something similar to this, albeit much simpler.

I created an Execute SQL task to select a single row from a table.

In the SQL Task Editor, General, I specified ResultSet = Single row, SQLSourceType = Direct Input, and entered the Select SQL in the SQLStatement field.

In the Result Set pane, I mapped the Result Names to the Variable Names using 0 for column1, 1 for columnn 2 etc.

Then in the Precedence Constraint Editor, I selected Evaluation Operation = Expression and Constraint, Value = Success, and entered my expression using the Variable Names.

The result of the expression controls the conditional execution of one of the two following data flows

|||

We have done something similar in a script task, you should be able to call the required variables using something similar to below

If Dts.Variables("gvMedSumFileMode".ToString() = "P" && Dts.Variables("gvMedSumFileType").ToString() = "U" then

if cint(Dts.Variables("gvMedSumFileVolume").ToString()) = cint(dts.Variables("gvMedCtrlFileVolume").ToString()) + 1 then

Dts.Task.Result = success

....

....

....

|||

Dev2624 wrote:

Hi i am New to ssis and Scripting in ssis what i was trying to do was to compare few variables which i populate using execute sql tasks and determine which process needs to kick off based on Task success or Failure

gvMedSumFileMode

gvMedSumFileType

gvMedSumFileVolume

gvMedCtrlFileVolume

gvMedSumSupplementNumber

gvMedCtrlSupplementNumber

are the variables i am populating Mapping them to result set in execute sql task.

This is the criteria for script task to Fail or success

If myvar11("gvMedSumFileMode").ToString = "P" && myVar12("gvMedSumFileType").ToString = "U" Then

If CType(myVar13("gvMedSumFileVolume").Value, Integer) = CType(myVar14("gvMedCtrlFileVolume").Value, Integer) + 1 Then

DTS.Task .Result = success

else

if CType(myVar13("gvMedSumFileVolume").Value, Integer) = CType(myVar14("gvMedCtrlFileVolume").Value, Integer) && CType(myVar15("gvMedSumSupplementNumber").Value, Integer) = CType(myVar16("gvMedCtrlSupplementNumber").Value, Integer) + 1 then

DTS.Task .Result = success

else
DTS.Task .Result = Failure

End if

End if

else

DTS.Task .Result = failure

I am not able to populate the varibales with the Values from Execute Sql and also i am encountering errors in the script task.

I am Declaring ex: myvar11 as variables and then using it , please help me as i am new and guide me for the right approach. and can this logic be implemented in ssis for example like && operator used in script.
End IF

I agree with the first answer; using expressions in the precedence constratint is the best way to 'direct' the traffic in your control flow, as they are easier to maintain and implement. Make sure you research on 'Precedence constraints'.|||

When you are using DTS.Variables are you writing them in the main page of script task under readonly Variables or declaring the variables as MyVariables is the right approach, also the Previous reply to this that i have followed the same method and trying to populate the variables , Can you please guide me as to how can we use these as expressions(in my case)

where conditions are for ex if varx="p" && vary="u"

then if varz= varc+1 success

or other case is if varx="p" && vary="u"

then if varz=varc

if varg=varh+1

success

how do we acheive these conditions in expressions.

|||

Also when i am trying to use && to evaluate both the variables at once it is not allowing me to do it , its throwing an error as expression expected .The questions i might ask might be trivial to you but please help me as i am a newbie and learning the steps ..

also is cint is the right word since the variable gvmedsumfilevolume is int in type. and i am trying to compare two integer values in the expression.

|||

Sorry I was getting confised between c# and vb.net. Below is the correct code, the variables that you are using within the script must also be placed in the "ReadOnlyVariables" property on the "Script Task Editor" > "Script" page. They should be in the format

gvMedSumFileMode,gvMedSumFileType,gvMedSumFileVolume",gvMedCtrlFileVolume

Note that there are no gaps between the commas and the next variable. Not sure if this is a bug but we found that it would ignore the remaining variables if spaces were included.

If Dts.Variables("gvMedSumFileMode").Value.ToString() = "P" And Dts.Variables("gvMedSumFileType").Value.ToString = "U" Then

If CInt(Dts.Variables("gvMedSumFileVolume").Value.ToString()) = CInt(Dts.Variables("gvMedCtrlFileVolume").Value.ToString()) + 1 Then

Dts.TaskResult = Dts.Results.Success

Else

If CInt(Dts.Variables("gvMedSumFileVolume").Value.ToString()) = CInt(Dts.Variables("gvMedCtrlFileVolume").Value.ToString()) And _

CInt(Dts.Variables("gvMedSumSupplementNumber").Value.ToString()) = CInt(Dts.Variables("gvMedCtrlSupplementNumber").Value.ToString()) + 1 Then

Dts.TaskResult = Dts.Results.Success

Else

Dts.TaskResult = Dts.Results.Failure

End If

End If

Else

Dts.TaskResult = Dts.Results.Failure

End If

|||

You should be using the variabledispenser... BEWARE :-)

http://blogs.conchango.com/jamiethomson/archive/2007/08/28/Beware-of-variable-usage-in-script-tasks.aspx

|||

This is the code that i am using and the task is failing is this the right way to do this . I am populating the variables using sql direct input and mapping them into the result set.

Variable names have changed and made these changes in the code. Please tell me how to resolve this

MedSumFileMode String

MedSumFileType String

MedSumFileVolume int32 rest all variables are type int32.

|||Dev, please share the error message. That would help, don't you think?

Also, You don't want LockOneForWrite unless you're only working with one variable. In this case you're not, so you'll want to use LockForWrite instead. If you're not writing to the variables, use LockForRead. Search for examples, but for starters:

Code Snippet

...

Dim vars As Variables
Dts.VariableDispenser.LockForRead("MedSumFileMode",vars)
Dts.VariableDispenser.LockForRead("MedSumFileType",vars)
Dts.VariableDispenser.LockForRead("MedSumFileVolume",vars)
Dts.VariableDispenser.LockForRead("MedCtrlFileVolume",vars)
Dts.VariableDispenser.LockForRead("MedSumSupplementNumber",vars)
Dts.VariableDispenser.LockForRead("MedCtrlSupplementNumber",vars)
Dts.VariableDispenser.GetVariables(vars)

If vars("MedSumFileMode").Value.ToString = "P" And vars("MedSumFileType").Value.ToString = "U" Then.......

|||

Dim vars As Variables
Dts.VariableDispenser.LockForRead("MedSumFileMode",vars)
Dts.VariableDispenser.LockForRead("MedSumFileType",vars)
Dts.VariableDispenser.LockForRead("MedSumFileVolume",vars)
Dts.VariableDispenser.LockForRead("MedCtrlFileVolume",vars)
Dts.VariableDispenser.LockForRead("MedSumSupplementNumber",vars)
Dts.VariableDispenser.LockForRead("MedCtrlSupplementNumber",vars)
Dts.VariableDispenser.GetVariables(vars)

If vars("MedSumFileMode").Value.ToString = "P" And vars("MedSumFileType").Value.ToString = "U" Then.......

when i use this it highlights an error saying too many arguments Public SubLock For Read(variable As String)

|||

If i use Dim vars As Variables
Dts.VariableDispenser.LockOneForRead("MedSumFileMode",vars)
the error goes away. is this the right approach.

ill post back with the error details

|||

Dev2624 wrote:

Dim vars As Variables
Dts.VariableDispenser.LockForRead("MedSumFileMode",vars)
Dts.VariableDispenser.LockForRead("MedSumFileType",vars)
Dts.VariableDispenser.LockForRead("MedSumFileVolume",vars)
Dts.VariableDispenser.LockForRead("MedCtrlFileVolume",vars)
Dts.VariableDispenser.LockForRead("MedSumSupplementNumber",vars)
Dts.VariableDispenser.LockForRead("MedCtrlSupplementNumber",vars)
Dts.VariableDispenser.GetVariables(vars)

If vars("MedSumFileMode").Value.ToString = "P" And vars("MedSumFileType").Value.ToString = "U" Then.......

when i use this it highlights an error saying too many arguments Public SubLock For Read(variable As String)

Yep, I'm not quite awake yet this morning.

Code Snippet

Dim vars As Variables
Dts.VariableDispenser.LockForRead("MedSumFileMode")
Dts.VariableDispenser.LockForRead("MedSumFileType")
Dts.VariableDispenser.LockForRead("MedSumFileVolume")
Dts.VariableDispenser.LockForRead("MedCtrlFileVolume")
Dts.VariableDispenser.LockForRead("MedSumSupplementNumber")
Dts.VariableDispenser.LockForRead("MedCtrlSupplementNumber")
Dts.VariableDispenser.GetVariables(vars)

If vars("MedSumFileMode").Value.ToString = "P" And vars("MedSumFileType").Value.ToString = "U" Then.......

|||

The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there.

at Microsoft.SqlServer.Dts.Runtime.Variables.get_Item(Object index)

at ScriptTask_08359122705d4affbf377ddc743eea59.ScriptMain.Main() in dts://Scripts/ScriptTask_08359122705d4affbf377ddc743eea59/ScriptMain:line 30

this is the error .

|||

Dim vars As Variables
Dts.VariableDispenser.LockForRead("MedSumFileMode")
Dts.VariableDispenser.LockForRead("MedSumFileType")
Dts.VariableDispenser.LockForRead("MedSumFileVolume")
Dts.VariableDispenser.LockForRead("MedCtrlFileVolume")
Dts.VariableDispenser.LockForRead("MedSumSupplementNumber")
Dts.VariableDispenser.LockForRead("MedCtrlSupplementNumber")
Dts.VariableDispenser.GetVariables(vars)

If vars("MedSumFileMode").Value.ToString = "P" And vars("MedSumFileType").Value.ToString = "U" Then.......

The problem with this method is that the vars collection does not take the string name of the variable it will only take the index of the variable, hence the code becomes difficult to read

If vars(0).Value.ToString = "P" And vars(1).Value.ToString = "U" Then.......

Help required Urgently: Intermittent connection failures

I am trying to connect to SQL Server from another box and I keep getting
this error intermittently.
DB-Library: Unable to connect: SQL Server is unavailable or does not exist.
Unable to connect: SQL Server does not exist or network access denied.
Net-Library error 10060: Connection (Connect()).
What could be the issues ?
-Nags
Hi,
This can be because of network issues. Can you execute a PING <Ipaddress>
command and
confirm that network is stable.
Thanks
Hari
MCDBA
"Nags" <nags@.DontSpamMe.com> wrote in message
news:eX2eXF#TEHA.3988@.TK2MSFTNGP10.phx.gbl...
> I am trying to connect to SQL Server from another box and I keep getting
> this error intermittently.
> DB-Library: Unable to connect: SQL Server is unavailable or does not
exist.
> Unable to connect: SQL Server does not exist or network access denied.
> Net-Library error 10060: Connection (Connect()).
> What could be the issues ?
> -Nags
>
|||Yes, we already tested it and the network is stable.
-Nags
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:e2v4U7JUEHA.384@.TK2MSFTNGP10.phx.gbl...
> Hi,
> This can be because of network issues. Can you execute a PING <Ipaddress>
> command and
> confirm that network is stable.
> --
> Thanks
> Hari
> MCDBA
> "Nags" <nags@.DontSpamMe.com> wrote in message
> news:eX2eXF#TEHA.3988@.TK2MSFTNGP10.phx.gbl...
> exist.
>
|||Are these servers in the same subnet? Are they dual
homed? Perhaps the serving box is too busy? Perhaps a
bad patch cable? Are any other applications on this
server having problems connecting out?
Sincerly,
Invotion Engineering Team
Advanced Microsoft Hosting Solutions
http://www.Invotion.com
[vbcol=seagreen]
>--Original Message--
>Yes, we already tested it and the network is stable.
>-Nags
>"Hari" <hari_prasad_k@.hotmail.com> wrote in message
>news:e2v4U7JUEHA.384@.TK2MSFTNGP10.phx.gbl...
a PING <Ipaddress>[vbcol=seagreen]
box and I keep getting[vbcol=seagreen]
unavailable or does not[vbcol=seagreen]
network access denied.
>
>.
>
|||10060 = Connection Timeout.
Verify that the server doesn't have a personal firewall that is blocking
the client.
Make network traces on both the client and server and verify that the tcp 3
way handshake is completing.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Help required Urgently: Intermittent connection failures

I am trying to connect to SQL Server from another box and I keep getting
this error intermittently.
DB-Library: Unable to connect: SQL Server is unavailable or does not exist.
Unable to connect: SQL Server does not exist or network access denied.
Net-Library error 10060: Connection (Connect()).
What could be the issues ?
-NagsHi,
This can be because of network issues. Can you execute a PING <Ipaddress>
command and
confirm that network is stable.
Thanks
Hari
MCDBA
"Nags" <nags@.DontSpamMe.com> wrote in message
news:eX2eXF#TEHA.3988@.TK2MSFTNGP10.phx.gbl...
> I am trying to connect to SQL Server from another box and I keep getting
> this error intermittently.
> DB-Library: Unable to connect: SQL Server is unavailable or does not
exist.
> Unable to connect: SQL Server does not exist or network access denied.
> Net-Library error 10060: Connection (Connect()).
> What could be the issues ?
> -Nags
>|||Yes, we already tested it and the network is stable.
-Nags
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:e2v4U7JUEHA.384@.TK2MSFTNGP10.phx.gbl...
> Hi,
> This can be because of network issues. Can you execute a PING <Ipaddress>
> command and
> confirm that network is stable.
> --
> Thanks
> Hari
> MCDBA
> "Nags" <nags@.DontSpamMe.com> wrote in message
> news:eX2eXF#TEHA.3988@.TK2MSFTNGP10.phx.gbl...
> exist.
>|||Are these servers in the same subnet? Are they dual
homed? Perhaps the serving box is too busy? Perhaps a
bad patch cable? Are any other applications on this
server having problems connecting out?
Sincerly,
Invotion Engineering Team
Advanced Microsoft Hosting Solutions
http://www.Invotion.com

>--Original Message--
>Yes, we already tested it and the network is stable.
>-Nags
>"Hari" <hari_prasad_k@.hotmail.com> wrote in message
>news:e2v4U7JUEHA.384@.TK2MSFTNGP10.phx.gbl...
a PING <Ipaddress>[vbcol=seagreen]
box and I keep getting[vbcol=seagreen]
unavailable or does not[vbcol=seagreen]
network access denied.[vbcol=seagreen]
>
>.
>|||10060 = Connection Timeout.
Verify that the server doesn't have a personal firewall that is blocking
the client.
Make network traces on both the client and server and verify that the tcp 3
way handshake is completing.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Help Required Urgently(SQL Server Agent)

On one of our servers the sqlseragent was running fine. But now it is not running. We se the option to start the server agent each time the OS starts. Even when I tried to start the agent manually from service manager it is not starting. Would any one help on this urgently.

Also how can we identify under what account any job runs. The reason is when we deleted some NT user accounts one of the job failed as this job runs under that userid. But in the properties it shows that sa is the owner of that job. What is best way to create a job so that these jobs run under one userid so that it won't fail when we delete users.

ThanksLook at the properties page for the SQL Server Agent and see what account it is running under. You may have deleted the account it was using. Alternatively, you can open Services under Administrative Tools and view what account it is running under.

As for the owner of jobs, right-click on the job and choose properties. You will see a drop down box called Owner. You can set the desired owner here.

Roby2222|||Thanks It worked. Actually what happend is the SQL server agent is running under sa. Recently we changed the password for sa. When I used this new password then the SQL Agent started.
My question is when ever we change the password for sa, and when the sql server starts with this new login password, doesn't the Agent also start with
this new password. Do we have to manually change the login password for the Agent?

For the second question, this particular job was failed because we deleted the userid and the error says that the job did not find the specified user. But when I checked the properties the owner is showing as sa. I am not sure why it got that error. That is why I wanted to find out from all the jobs which runs under the deleted username.
Please advise.
Thanks|||My preference for SQL Server Agent is to use a Domain account, rather than a SQL Server Login. This allows SQL Server Agent to access network resources under the credentials of a domain account. This might be necessary if you want a job to move a file to a network share, or a similar operation. We use the same domain account that the SQL Server Service uses, though you could use two different ones if you want to.

As far as the credentials for the Job, I would create a SQL Server Login specifically for Jobs. It's a little more secure and also provides you with the added benefit on not having to worry about your jobs every time you change the SA password.

Roby2222|||One more advantage with using a specifik domain account for you SQL Agent is that then you don't accidentally delete any user accounts that have been used for SQL tasks...

Help Required URGENT ! Out of Memory Error

We are getting the following errror on SQL Server : We have about 4GB
memory and has AWE enabled. Why are we getting these errors? We just
increase the memory from 1 Gig to 4 Gig and we are assuming that now we have
sufficient memory.
LazyWriter: warning, no free buffers found.
Buffer Distribution: Stolen=91967 Free=0 Procedures=726
Inram=292678 Dirty=20958 Kept=0
I/O=0, Latched=1724, Other=0
Buffer Counts: Commited=408053 Target=408053 Hashed=315360
InternalReservation=525 ExternalReservation=16375 Min Free=512
Procedure Cache: TotalProcs=186 TotalPages=726 InUsePages=726
Dynamic Memory Manager: Stolen=91784 OS Reserved=2384
OS Committed=2333
OS In Use=2304
Query Plan=77132 Optimizer=295
General=11448
Utilities=32 Connection=4067
Global Memory Objects: Resource=9076 Locks=185
SQLCache=375 Replication=35
LockBytes=2 ServerGlobal=47
Xact=1670
Query Memory Manager: Grants=2 Waiting=0 Maximum=85781 Available=68497
Error: 17803, Severity: 20, State: 14
Insufficient memory available..
LazyWriter: warning, no free buffers found.
Buffer Distribution: Stolen=80335 Free=0 Procedures=63
Inram=307141 Dirty=19250 Kept=0
I/O=0, Latched=1264, Other=0
Buffer Counts: Commited=408053 Target=408053 Hashed=327655
InternalReservation=1774 ExternalReservation=0 Min Free=512
Procedure Cache: TotalProcs=22 TotalPages=63 InUsePages=63
Dynamic Memory Manager: Stolen=80398 OS Reserved=2392
OS Committed=2341
OS In Use=2274
Query Plan=67512 Optimizer=17
General=11800
Utilities=29 Connection=2764
Global Memory Objects: Resource=8601 Locks=104
SQLCache=236 Replication=76
LockBytes=2 ServerGlobal=47
Xact=2809
Query Memory Manager: Grants=0 Waiting=0 Maximum=93356 Available=93356
How about turning off AWE and make sure the /3GB switch is set in the
Boot.ini file.
Andrew J. Kelly SQL MVP
"Nags" <nags@.DontSpamMe.com> wrote in message
news:%23Zw7f2JeEHA.1036@.TK2MSFTNGP10.phx.gbl...
> We are getting the following errror on SQL Server : We have about 4GB
> memory and has AWE enabled. Why are we getting these errors? We just
> increase the memory from 1 Gig to 4 Gig and we are assuming that now we
have
> sufficient memory.
> LazyWriter: warning, no free buffers found.
> Buffer Distribution: Stolen=91967 Free=0 Procedures=726
> Inram=292678 Dirty=20958 Kept=0
> I/O=0, Latched=1724, Other=0
> Buffer Counts: Commited=408053 Target=408053 Hashed=315360
> InternalReservation=525 ExternalReservation=16375 Min Free=512
> Procedure Cache: TotalProcs=186 TotalPages=726 InUsePages=726
> Dynamic Memory Manager: Stolen=91784 OS Reserved=2384
> OS Committed=2333
> OS In Use=2304
> Query Plan=77132 Optimizer=295
> General=11448
> Utilities=32 Connection=4067
> Global Memory Objects: Resource=9076 Locks=185
> SQLCache=375 Replication=35
> LockBytes=2 ServerGlobal=47
> Xact=1670
> Query Memory Manager: Grants=2 Waiting=0 Maximum=85781 Available=68497
> Error: 17803, Severity: 20, State: 14
> Insufficient memory available..
> LazyWriter: warning, no free buffers found.
> Buffer Distribution: Stolen=80335 Free=0 Procedures=63
> Inram=307141 Dirty=19250 Kept=0
> I/O=0, Latched=1264, Other=0
> Buffer Counts: Commited=408053 Target=408053 Hashed=327655
> InternalReservation=1774 ExternalReservation=0 Min Free=512
> Procedure Cache: TotalProcs=22 TotalPages=63 InUsePages=63
> Dynamic Memory Manager: Stolen=80398 OS Reserved=2392
> OS Committed=2341
> OS In Use=2274
> Query Plan=67512 Optimizer=17
> General=11800
> Utilities=29 Connection=2764
> Global Memory Objects: Resource=8601 Locks=104
> SQLCache=236 Replication=76
> LockBytes=2 ServerGlobal=47
> Xact=2809
> Query Memory Manager: Grants=0 Waiting=0 Maximum=93356 Available=93356
>
|||The /3GB was not set in the Boot.ini file. My fault, I assumed that our
hardware guys had already done it as it was a prerequisite, before they
handed over the server to the DBA's. Its all set now and will be monitoring
it for couple of days to make sure this error does not occur again.
We put /3GB /PAE in boot.ini as we have 9Gig of RAM.
Thank you,
-Nags
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eLZmSvKeEHA.3512@.TK2MSFTNGP12.phx.gbl...
> How about turning off AWE and make sure the /3GB switch is set in the
> Boot.ini file.
> --
> Andrew J. Kelly SQL MVP
>
> "Nags" <nags@.DontSpamMe.com> wrote in message
> news:%23Zw7f2JeEHA.1036@.TK2MSFTNGP10.phx.gbl...
> have
>
|||I hope you are using Enterprise Edition of SQL Server otherwise you won't be
able to utilize more than 2GB of Ram anyway. If you have 9GB (before you
said 4GB) and want to set AWE make sure you set the max memory in SQL Server
to less than 9GB. I would advise maybe starting at 7GB and see how that
works out. You were runing with 1GB before. 9GB is a big jump, are you
sure you need that much. If you can't utilize all the memory you actually
make SQL Server do extra work.
Andrew J. Kelly SQL MVP
"Nags" <nags@.DontSpamMe.com> wrote in message
news:OWdxf9KeEHA.2764@.TK2MSFTNGP11.phx.gbl...
> The /3GB was not set in the Boot.ini file. My fault, I assumed that our
> hardware guys had already done it as it was a prerequisite, before they
> handed over the server to the DBA's. Its all set now and will be
monitoring[vbcol=seagreen]
> it for couple of days to make sure this error does not occur again.
> We put /3GB /PAE in boot.ini as we have 9Gig of RAM.
> Thank you,
> -Nags
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eLZmSvKeEHA.3512@.TK2MSFTNGP12.phx.gbl...
4GB[vbcol=seagreen]
we[vbcol=seagreen]
Available=68497[vbcol=seagreen]
Available=93356
>
|||Yes we have Enterprise Edition of SQL Server and Enterprise Edition of
Windows 2003.
Microsoft SQL Server 2000 - 8.00.818 (Intel X86) May 31 2003 16:08:15
Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows
NT 5.2 (Build 3790: )
We had enabled AWE and were restricting SQL Server at 4 GB to see how it
would perform. We were actually running out of memory before when we had
only 1 Gig. When we went for the new server we went to higher memory, as we
never know when we will need it :o)
On one of our production server we have 32 GB to pin objects into memory for
better performance.
Why do you say "If you can't utilize all the memory".. SQL Server will keep
using all the memory available and will cache all the objects and pages that
it will access. The more memory we have the more it will cache into memory,
probably with everything in memory rather than going to disk .. right ?
Over a period the data most frequently accessed will be in memory with
almost no access to disks .. right ?
-Nags
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OfA5RqLeEHA.3756@.TK2MSFTNGP12.phx.gbl...
> I hope you are using Enterprise Edition of SQL Server otherwise you won't
be
> able to utilize more than 2GB of Ram anyway. If you have 9GB (before you
> said 4GB) and want to set AWE make sure you set the max memory in SQL
Server[vbcol=seagreen]
> to less than 9GB. I would advise maybe starting at 7GB and see how that
> works out. You were runing with 1GB before. 9GB is a big jump, are you
> sure you need that much. If you can't utilize all the memory you actually
> make SQL Server do extra work.
> --
> Andrew J. Kelly SQL MVP
>
> "Nags" <nags@.DontSpamMe.com> wrote in message
> news:OWdxf9KeEHA.2764@.TK2MSFTNGP11.phx.gbl...
> monitoring
> 4GB
just
> we
> Available=68497
> Available=93356
>
|||Two things here. First off pinning tables in memory may hurt performance
more than help. If the tables are accessed frequently then SQL Server has
an internal process to keep those pages in memory over less frequently
accessed data. By you pinning a table you run the risk of using memory for
data that although you accessed it once it may never be accessed again (or
infrequently) and that memory may have been better utilized by some other
tables data. In almost all instances you can not determine better than SQL
Server what should stay in cache and what should not given all the
circumstances the engine has to deal with. By the other statement of can't
utilize all the memory I meant this. Just because you have a 10 GB database
does not mean you will access all 10GB all the time. As a matter of fact in
a typical OLTP app only a fraction of the data is accessed each day. Maybe
less than 5 or 10% on average. But if you have 10GB of cache available SQL
Server will load what ever data you access into cache and it will stay there
forever until one of several things happen.
You restart SQL Server
You run DBCC DROPCLEANBUFFERS
The memroy is needed for something else.
So lets say over time you loaded all 10GB into memory but you only use say
1GB on any given day. SQL Server has to manage all 10GB of memory
regardless of how you use it. There are several processes that deal with it
but lets just take the lazy writer process. On a regular basis it scans all
10GB's of data in cache and does several things. One is it writes dirty
buffers to disk. Another is that it adjusts a counter on each page to help
it determine which page it can free up if needed. So it is doing theses
checks for 9GB of data that really will hardly if ever be touched but the
overhead is still there. If you only had say 1.5GB of memory you can keep
in all the most used data and leave a little for the infrequently used
stuff. If every so often you have to access the hard drive for this
infrequently used data it will overwrite the other infrequently used pages
and not the most accessed ones. This minimal drive access is usually not
that big a deal and you save a lot of overhead in hte engine for little
gain.
Now I am not saying you should only have a few extra bytes over and above
what you regulary use but you can get too excessive with extra memroy. Too
much is better than not enough in most cases but way too much is not good
either.
Andrew J. Kelly SQL MVP
"Nags" <nags@.DontSpamMe.com> wrote in message
news:eEs1R1LeEHA.3132@.TK2MSFTNGP11.phx.gbl...
> Yes we have Enterprise Edition of SQL Server and Enterprise Edition of
> Windows 2003.
> Microsoft SQL Server 2000 - 8.00.818 (Intel X86) May 31 2003 16:08:15
> Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on
Windows
> NT 5.2 (Build 3790: )
> We had enabled AWE and were restricting SQL Server at 4 GB to see how it
> would perform. We were actually running out of memory before when we had
> only 1 Gig. When we went for the new server we went to higher memory, as
we
> never know when we will need it :o)
> On one of our production server we have 32 GB to pin objects into memory
for
> better performance.
> Why do you say "If you can't utilize all the memory".. SQL Server will
keep
> using all the memory available and will cache all the objects and pages
that
> it will access. The more memory we have the more it will cache into
memory,[vbcol=seagreen]
> probably with everything in memory rather than going to disk .. right ?
> Over a period the data most frequently accessed will be in memory with
> almost no access to disks .. right ?
> -Nags
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OfA5RqLeEHA.3756@.TK2MSFTNGP12.phx.gbl...
won't[vbcol=seagreen]
> be
you[vbcol=seagreen]
> Server
you[vbcol=seagreen]
actually[vbcol=seagreen]
our[vbcol=seagreen]
they[vbcol=seagreen]
the[vbcol=seagreen]
about[vbcol=seagreen]
> just
now
>
|||I totally agree, but with AWE enabled, SQL Server preallocates memory
instead of allocating as required.
And secondly, if we restrict SQL Server to start with low memory and we want
to increase it later, we have to restart the server. Sometimes, we cannot
restart for months.
We do have huge processing power, so the slight overhead for scanning the
memory should be ok.
-Nags
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:echyFUMeEHA.3476@.tk2msftngp13.phx.gbl...
> Two things here. First off pinning tables in memory may hurt performance
> more than help. If the tables are accessed frequently then SQL Server has
> an internal process to keep those pages in memory over less frequently
> accessed data. By you pinning a table you run the risk of using memory
for
> data that although you accessed it once it may never be accessed again (or
> infrequently) and that memory may have been better utilized by some other
> tables data. In almost all instances you can not determine better than
SQL
> Server what should stay in cache and what should not given all the
> circumstances the engine has to deal with. By the other statement of
can't
> utilize all the memory I meant this. Just because you have a 10 GB
database
> does not mean you will access all 10GB all the time. As a matter of fact
in
> a typical OLTP app only a fraction of the data is accessed each day. Maybe
> less than 5 or 10% on average. But if you have 10GB of cache available
SQL
> Server will load what ever data you access into cache and it will stay
there
> forever until one of several things happen.
> You restart SQL Server
> You run DBCC DROPCLEANBUFFERS
> The memroy is needed for something else.
> So lets say over time you loaded all 10GB into memory but you only use say
> 1GB on any given day. SQL Server has to manage all 10GB of memory
> regardless of how you use it. There are several processes that deal with
it
> but lets just take the lazy writer process. On a regular basis it scans
all
> 10GB's of data in cache and does several things. One is it writes dirty
> buffers to disk. Another is that it adjusts a counter on each page to
help
> it determine which page it can free up if needed. So it is doing theses
> checks for 9GB of data that really will hardly if ever be touched but the
> overhead is still there. If you only had say 1.5GB of memory you can
keep
> in all the most used data and leave a little for the infrequently used
> stuff. If every so often you have to access the hard drive for this
> infrequently used data it will overwrite the other infrequently used pages
> and not the most accessed ones. This minimal drive access is usually not
> that big a deal and you save a lot of overhead in hte engine for little
> gain.
> Now I am not saying you should only have a few extra bytes over and above
> what you regulary use but you can get too excessive with extra memroy.
Too[vbcol=seagreen]
> much is better than not enough in most cases but way too much is not good
> either.
> --
> Andrew J. Kelly SQL MVP
>
> "Nags" <nags@.DontSpamMe.com> wrote in message
> news:eEs1R1LeEHA.3132@.TK2MSFTNGP11.phx.gbl...
> Windows
had[vbcol=seagreen]
as[vbcol=seagreen]
> we
> for
> keep
> that
> memory,
> won't
> you
that[vbcol=seagreen]
> you
> actually
> our
> they
> the
> about
We
> now
>

Help Required URGENT ! Out of Memory Error

We are getting the following errror on SQL Server : We have about 4GB
memory and has AWE enabled. Why are we getting these errors? We just
increase the memory from 1 Gig to 4 Gig and we are assuming that now we have
sufficient memory.
LazyWriter: warning, no free buffers found.
Buffer Distribution: Stolen=91967 Free=0 Procedures=726
Inram=292678 Dirty=20958 Kept=0
I/O=0, Latched=1724, Other=0
Buffer Counts: Commited=408053 Target=408053 Hashed=315360
InternalReservation=525 ExternalReservation=16375 Min Free=512
Procedure Cache: TotalProcs=186 TotalPages=726 InUsePages=726
Dynamic Memory Manager: Stolen=91784 OS Reserved=2384
OS Committed=2333
OS In Use=2304
Query Plan=77132 Optimizer=295
General=11448
Utilities=32 Connection=4067
Global Memory Objects: Resource=9076 Locks=185
SQLCache=375 Replication=35
LockBytes=2 ServerGlobal=47
Xact=1670
Query Memory Manager: Grants=2 Waiting=0 Maximum=85781 Available=68497
Error: 17803, Severity: 20, State: 14
Insufficient memory available..
LazyWriter: warning, no free buffers found.
Buffer Distribution: Stolen=80335 Free=0 Procedures=63
Inram=307141 Dirty=19250 Kept=0
I/O=0, Latched=1264, Other=0
Buffer Counts: Commited=408053 Target=408053 Hashed=327655
InternalReservation=1774 ExternalReservation=0 Min Free=512
Procedure Cache: TotalProcs=22 TotalPages=63 InUsePages=63
Dynamic Memory Manager: Stolen=80398 OS Reserved=2392
OS Committed=2341
OS In Use=2274
Query Plan=67512 Optimizer=17
General=11800
Utilities=29 Connection=2764
Global Memory Objects: Resource=8601 Locks=104
SQLCache=236 Replication=76
LockBytes=2 ServerGlobal=47
Xact=2809
Query Memory Manager: Grants=0 Waiting=0 Maximum=93356 Available=93356How about turning off AWE and make sure the /3GB switch is set in the
Boot.ini file.
Andrew J. Kelly SQL MVP
"Nags" <nags@.DontSpamMe.com> wrote in message
news:%23Zw7f2JeEHA.1036@.TK2MSFTNGP10.phx.gbl...
> We are getting the following errror on SQL Server : We have about 4GB
> memory and has AWE enabled. Why are we getting these errors? We just
> increase the memory from 1 Gig to 4 Gig and we are assuming that now we
have
> sufficient memory.
> LazyWriter: warning, no free buffers found.
> Buffer Distribution: Stolen=91967 Free=0 Procedures=726
> Inram=292678 Dirty=20958 Kept=0
> I/O=0, Latched=1724, Other=0
> Buffer Counts: Commited=408053 Target=408053 Hashed=315360
> InternalReservation=525 ExternalReservation=16375 Min Free=512
> Procedure Cache: TotalProcs=186 TotalPages=726 InUsePages=726
> Dynamic Memory Manager: Stolen=91784 OS Reserved=2384
> OS Committed=2333
> OS In Use=2304
> Query Plan=77132 Optimizer=295
> General=11448
> Utilities=32 Connection=4067
> Global Memory Objects: Resource=9076 Locks=185
> SQLCache=375 Replication=35
> LockBytes=2 ServerGlobal=47
> Xact=1670
> Query Memory Manager: Grants=2 Waiting=0 Maximum=85781 Available=68497
> Error: 17803, Severity: 20, State: 14
> Insufficient memory available..
> LazyWriter: warning, no free buffers found.
> Buffer Distribution: Stolen=80335 Free=0 Procedures=63
> Inram=307141 Dirty=19250 Kept=0
> I/O=0, Latched=1264, Other=0
> Buffer Counts: Commited=408053 Target=408053 Hashed=327655
> InternalReservation=1774 ExternalReservation=0 Min Free=512
> Procedure Cache: TotalProcs=22 TotalPages=63 InUsePages=63
> Dynamic Memory Manager: Stolen=80398 OS Reserved=2392
> OS Committed=2341
> OS In Use=2274
> Query Plan=67512 Optimizer=17
> General=11800
> Utilities=29 Connection=2764
> Global Memory Objects: Resource=8601 Locks=104
> SQLCache=236 Replication=76
> LockBytes=2 ServerGlobal=47
> Xact=2809
> Query Memory Manager: Grants=0 Waiting=0 Maximum=93356 Available=93356
>|||The /3GB was not set in the Boot.ini file. My fault, I assumed that our
hardware guys had already done it as it was a prerequisite, before they
handed over the server to the DBA's. Its all set now and will be monitoring
it for couple of days to make sure this error does not occur again.
We put /3GB /PAE in boot.ini as we have 9Gig of RAM.
Thank you,
-Nags
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eLZmSvKeEHA.3512@.TK2MSFTNGP12.phx.gbl...
> How about turning off AWE and make sure the /3GB switch is set in the
> Boot.ini file.
> --
> Andrew J. Kelly SQL MVP
>
> "Nags" <nags@.DontSpamMe.com> wrote in message
> news:%23Zw7f2JeEHA.1036@.TK2MSFTNGP10.phx.gbl...
> have
>|||I hope you are using Enterprise Edition of SQL Server otherwise you won't be
able to utilize more than 2GB of Ram anyway. If you have 9GB (before you
said 4GB) and want to set AWE make sure you set the max memory in SQL Server
to less than 9GB. I would advise maybe starting at 7GB and see how that
works out. You were runing with 1GB before. 9GB is a big jump, are you
sure you need that much. If you can't utilize all the memory you actually
make SQL Server do extra work.
Andrew J. Kelly SQL MVP
"Nags" <nags@.DontSpamMe.com> wrote in message
news:OWdxf9KeEHA.2764@.TK2MSFTNGP11.phx.gbl...
> The /3GB was not set in the Boot.ini file. My fault, I assumed that our
> hardware guys had already done it as it was a prerequisite, before they
> handed over the server to the DBA's. Its all set now and will be
monitoring
> it for couple of days to make sure this error does not occur again.
> We put /3GB /PAE in boot.ini as we have 9Gig of RAM.
> Thank you,
> -Nags
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eLZmSvKeEHA.3512@.TK2MSFTNGP12.phx.gbl...
4GB[vbcol=seagreen]
we[vbcol=seagreen]
Available=68497[vbcol=seagreen]
Available=93356[vbcol=seagreen]
>|||Yes we have Enterprise Edition of SQL Server and Enterprise Edition of
Windows 2003.
Microsoft SQL Server 2000 - 8.00.818 (Intel X86) May 31 2003 16:08:15
Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows
NT 5.2 (Build 3790: )
We had enabled AWE and were restricting SQL Server at 4 GB to see how it
would perform. We were actually running out of memory before when we had
only 1 Gig. When we went for the new server we went to higher memory, as we
never know when we will need it :o)
On one of our production server we have 32 GB to pin objects into memory for
better performance.
Why do you say "If you can't utilize all the memory".. SQL Server will keep
using all the memory available and will cache all the objects and pages that
it will access. The more memory we have the more it will cache into memory,
probably with everything in memory rather than going to disk .. right ?
Over a period the data most frequently accessed will be in memory with
almost no access to disks .. right ?
-Nags
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OfA5RqLeEHA.3756@.TK2MSFTNGP12.phx.gbl...
> I hope you are using Enterprise Edition of SQL Server otherwise you won't
be
> able to utilize more than 2GB of Ram anyway. If you have 9GB (before you
> said 4GB) and want to set AWE make sure you set the max memory in SQL
Server
> to less than 9GB. I would advise maybe starting at 7GB and see how that
> works out. You were runing with 1GB before. 9GB is a big jump, are you
> sure you need that much. If you can't utilize all the memory you actually
> make SQL Server do extra work.
> --
> Andrew J. Kelly SQL MVP
>
> "Nags" <nags@.DontSpamMe.com> wrote in message
> news:OWdxf9KeEHA.2764@.TK2MSFTNGP11.phx.gbl...
> monitoring
> 4GB
just[vbcol=seagreen]
> we
> Available=68497
> Available=93356
>|||Two things here. First off pinning tables in memory may hurt performance
more than help. If the tables are accessed frequently then SQL Server has
an internal process to keep those pages in memory over less frequently
accessed data. By you pinning a table you run the risk of using memory for
data that although you accessed it once it may never be accessed again (or
infrequently) and that memory may have been better utilized by some other
tables data. In almost all instances you can not determine better than SQL
Server what should stay in cache and what should not given all the
circumstances the engine has to deal with. By the other statement of can't
utilize all the memory I meant this. Just because you have a 10 GB database
does not mean you will access all 10GB all the time. As a matter of fact in
a typical OLTP app only a fraction of the data is accessed each day. Maybe
less than 5 or 10% on average. But if you have 10GB of cache available SQL
Server will load what ever data you access into cache and it will stay there
forever until one of several things happen.
You restart SQL Server
You run DBCC DROPCLEANBUFFERS
The memroy is needed for something else.
So lets say over time you loaded all 10GB into memory but you only use say
1GB on any given day. SQL Server has to manage all 10GB of memory
regardless of how you use it. There are several processes that deal with it
but lets just take the lazy writer process. On a regular basis it scans all
10GB's of data in cache and does several things. One is it writes dirty
buffers to disk. Another is that it adjusts a counter on each page to help
it determine which page it can free up if needed. So it is doing theses
checks for 9GB of data that really will hardly if ever be touched but the
overhead is still there. If you only had say 1.5GB of memory you can keep
in all the most used data and leave a little for the infrequently used
stuff. If every so often you have to access the hard drive for this
infrequently used data it will overwrite the other infrequently used pages
and not the most accessed ones. This minimal drive access is usually not
that big a deal and you save a lot of overhead in hte engine for little
gain.
Now I am not saying you should only have a few extra bytes over and above
what you regulary use but you can get too excessive with extra memroy. Too
much is better than not enough in most cases but way too much is not good
either.
Andrew J. Kelly SQL MVP
"Nags" <nags@.DontSpamMe.com> wrote in message
news:eEs1R1LeEHA.3132@.TK2MSFTNGP11.phx.gbl...
> Yes we have Enterprise Edition of SQL Server and Enterprise Edition of
> Windows 2003.
> Microsoft SQL Server 2000 - 8.00.818 (Intel X86) May 31 2003 16:08:15
> Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on
Windows
> NT 5.2 (Build 3790: )
> We had enabled AWE and were restricting SQL Server at 4 GB to see how it
> would perform. We were actually running out of memory before when we had
> only 1 Gig. When we went for the new server we went to higher memory, as
we
> never know when we will need it :o)
> On one of our production server we have 32 GB to pin objects into memory
for
> better performance.
> Why do you say "If you can't utilize all the memory".. SQL Server will
keep
> using all the memory available and will cache all the objects and pages
that
> it will access. The more memory we have the more it will cache into
memory,
> probably with everything in memory rather than going to disk .. right ?
> Over a period the data most frequently accessed will be in memory with
> almost no access to disks .. right ?
> -Nags
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OfA5RqLeEHA.3756@.TK2MSFTNGP12.phx.gbl...
won't[vbcol=seagreen]
> be
you[vbcol=seagreen]
> Server
you[vbcol=seagreen]
actually[vbcol=seagreen]
our[vbcol=seagreen]
they[vbcol=seagreen]
the[vbcol=seagreen]
about[vbcol=seagreen]
> just
now[vbcol=seagreen]
>|||I totally agree, but with AWE enabled, SQL Server preallocates memory
instead of allocating as required.
And secondly, if we restrict SQL Server to start with low memory and we want
to increase it later, we have to restart the server. Sometimes, we cannot
restart for months.
We do have huge processing power, so the slight overhead for scanning the
memory should be ok.
-Nags
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:echyFUMeEHA.3476@.tk2msftngp13.phx.gbl...
> Two things here. First off pinning tables in memory may hurt performance
> more than help. If the tables are accessed frequently then SQL Server has
> an internal process to keep those pages in memory over less frequently
> accessed data. By you pinning a table you run the risk of using memory
for
> data that although you accessed it once it may never be accessed again (or
> infrequently) and that memory may have been better utilized by some other
> tables data. In almost all instances you can not determine better than
SQL
> Server what should stay in cache and what should not given all the
> circumstances the engine has to deal with. By the other statement of
can't
> utilize all the memory I meant this. Just because you have a 10 GB
database
> does not mean you will access all 10GB all the time. As a matter of fact
in
> a typical OLTP app only a fraction of the data is accessed each day. Maybe
> less than 5 or 10% on average. But if you have 10GB of cache available
SQL
> Server will load what ever data you access into cache and it will stay
there
> forever until one of several things happen.
> You restart SQL Server
> You run DBCC DROPCLEANBUFFERS
> The memroy is needed for something else.
> So lets say over time you loaded all 10GB into memory but you only use say
> 1GB on any given day. SQL Server has to manage all 10GB of memory
> regardless of how you use it. There are several processes that deal with
it
> but lets just take the lazy writer process. On a regular basis it scans
all
> 10GB's of data in cache and does several things. One is it writes dirty
> buffers to disk. Another is that it adjusts a counter on each page to
help
> it determine which page it can free up if needed. So it is doing theses
> checks for 9GB of data that really will hardly if ever be touched but the
> overhead is still there. If you only had say 1.5GB of memory you can
keep
> in all the most used data and leave a little for the infrequently used
> stuff. If every so often you have to access the hard drive for this
> infrequently used data it will overwrite the other infrequently used pages
> and not the most accessed ones. This minimal drive access is usually not
> that big a deal and you save a lot of overhead in hte engine for little
> gain.
> Now I am not saying you should only have a few extra bytes over and above
> what you regulary use but you can get too excessive with extra memroy.
Too
> much is better than not enough in most cases but way too much is not good
> either.
> --
> Andrew J. Kelly SQL MVP
>
> "Nags" <nags@.DontSpamMe.com> wrote in message
> news:eEs1R1LeEHA.3132@.TK2MSFTNGP11.phx.gbl...
> Windows
had[vbcol=seagreen]
as[vbcol=seagreen]
> we
> for
> keep
> that
> memory,
> won't
> you
that[vbcol=seagreen]
> you
> actually
> our
> they
> the
> about
We[vbcol=seagreen]
> now
>

Help Required URGENT ! Out of Memory Error

We are getting the following errror on SQL Server : We have about 4GB
memory and has AWE enabled. Why are we getting these errors? We just
increase the memory from 1 Gig to 4 Gig and we are assuming that now we have
sufficient memory.
LazyWriter: warning, no free buffers found.
Buffer Distribution: Stolen=91967 Free=0 Procedures=726
Inram=292678 Dirty=20958 Kept=0
I/O=0, Latched=1724, Other=0
Buffer Counts: Commited=408053 Target=408053 Hashed=315360
InternalReservation=525 ExternalReservation=16375 Min Free=512
Procedure Cache: TotalProcs=186 TotalPages=726 InUsePages=726
Dynamic Memory Manager: Stolen=91784 OS Reserved=2384
OS Committed=2333
OS In Use=2304
Query Plan=77132 Optimizer=295
General=11448
Utilities=32 Connection=4067
Global Memory Objects: Resource=9076 Locks=185
SQLCache=375 Replication=35
LockBytes=2 ServerGlobal=47
Xact=1670
Query Memory Manager: Grants=2 Waiting=0 Maximum=85781 Available=68497
Error: 17803, Severity: 20, State: 14
Insufficient memory available..
LazyWriter: warning, no free buffers found.
Buffer Distribution: Stolen=80335 Free=0 Procedures=63
Inram=307141 Dirty=19250 Kept=0
I/O=0, Latched=1264, Other=0
Buffer Counts: Commited=408053 Target=408053 Hashed=327655
InternalReservation=1774 ExternalReservation=0 Min Free=512
Procedure Cache: TotalProcs=22 TotalPages=63 InUsePages=63
Dynamic Memory Manager: Stolen=80398 OS Reserved=2392
OS Committed=2341
OS In Use=2274
Query Plan=67512 Optimizer=17
General=11800
Utilities=29 Connection=2764
Global Memory Objects: Resource=8601 Locks=104
SQLCache=236 Replication=76
LockBytes=2 ServerGlobal=47
Xact=2809
Query Memory Manager: Grants=0 Waiting=0 Maximum=93356 Available=93356How about turning off AWE and make sure the /3GB switch is set in the
Boot.ini file.
--
Andrew J. Kelly SQL MVP
"Nags" <nags@.DontSpamMe.com> wrote in message
news:%23Zw7f2JeEHA.1036@.TK2MSFTNGP10.phx.gbl...
> We are getting the following errror on SQL Server : We have about 4GB
> memory and has AWE enabled. Why are we getting these errors? We just
> increase the memory from 1 Gig to 4 Gig and we are assuming that now we
have
> sufficient memory.
> LazyWriter: warning, no free buffers found.
> Buffer Distribution: Stolen=91967 Free=0 Procedures=726
> Inram=292678 Dirty=20958 Kept=0
> I/O=0, Latched=1724, Other=0
> Buffer Counts: Commited=408053 Target=408053 Hashed=315360
> InternalReservation=525 ExternalReservation=16375 Min Free=512
> Procedure Cache: TotalProcs=186 TotalPages=726 InUsePages=726
> Dynamic Memory Manager: Stolen=91784 OS Reserved=2384
> OS Committed=2333
> OS In Use=2304
> Query Plan=77132 Optimizer=295
> General=11448
> Utilities=32 Connection=4067
> Global Memory Objects: Resource=9076 Locks=185
> SQLCache=375 Replication=35
> LockBytes=2 ServerGlobal=47
> Xact=1670
> Query Memory Manager: Grants=2 Waiting=0 Maximum=85781 Available=68497
> Error: 17803, Severity: 20, State: 14
> Insufficient memory available..
> LazyWriter: warning, no free buffers found.
> Buffer Distribution: Stolen=80335 Free=0 Procedures=63
> Inram=307141 Dirty=19250 Kept=0
> I/O=0, Latched=1264, Other=0
> Buffer Counts: Commited=408053 Target=408053 Hashed=327655
> InternalReservation=1774 ExternalReservation=0 Min Free=512
> Procedure Cache: TotalProcs=22 TotalPages=63 InUsePages=63
> Dynamic Memory Manager: Stolen=80398 OS Reserved=2392
> OS Committed=2341
> OS In Use=2274
> Query Plan=67512 Optimizer=17
> General=11800
> Utilities=29 Connection=2764
> Global Memory Objects: Resource=8601 Locks=104
> SQLCache=236 Replication=76
> LockBytes=2 ServerGlobal=47
> Xact=2809
> Query Memory Manager: Grants=0 Waiting=0 Maximum=93356 Available=93356
>|||The /3GB was not set in the Boot.ini file. My fault, I assumed that our
hardware guys had already done it as it was a prerequisite, before they
handed over the server to the DBA's. Its all set now and will be monitoring
it for couple of days to make sure this error does not occur again.
We put /3GB /PAE in boot.ini as we have 9Gig of RAM.
Thank you,
-Nags
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eLZmSvKeEHA.3512@.TK2MSFTNGP12.phx.gbl...
> How about turning off AWE and make sure the /3GB switch is set in the
> Boot.ini file.
> --
> Andrew J. Kelly SQL MVP
>
> "Nags" <nags@.DontSpamMe.com> wrote in message
> news:%23Zw7f2JeEHA.1036@.TK2MSFTNGP10.phx.gbl...
> > We are getting the following errror on SQL Server : We have about 4GB
> > memory and has AWE enabled. Why are we getting these errors? We just
> > increase the memory from 1 Gig to 4 Gig and we are assuming that now we
> have
> > sufficient memory.
> >
> > LazyWriter: warning, no free buffers found.
> > Buffer Distribution: Stolen=91967 Free=0 Procedures=726
> > Inram=292678 Dirty=20958 Kept=0
> > I/O=0, Latched=1724, Other=0
> > Buffer Counts: Commited=408053 Target=408053 Hashed=315360
> > InternalReservation=525 ExternalReservation=16375 Min Free=512
> > Procedure Cache: TotalProcs=186 TotalPages=726 InUsePages=726
> > Dynamic Memory Manager: Stolen=91784 OS Reserved=2384
> > OS Committed=2333
> > OS In Use=2304
> > Query Plan=77132 Optimizer=295
> > General=11448
> > Utilities=32 Connection=4067
> > Global Memory Objects: Resource=9076 Locks=185
> > SQLCache=375 Replication=35
> > LockBytes=2 ServerGlobal=47
> > Xact=1670
> > Query Memory Manager: Grants=2 Waiting=0 Maximum=85781 Available=68497
> >
> > Error: 17803, Severity: 20, State: 14
> > Insufficient memory available..
> > LazyWriter: warning, no free buffers found.
> > Buffer Distribution: Stolen=80335 Free=0 Procedures=63
> > Inram=307141 Dirty=19250 Kept=0
> > I/O=0, Latched=1264, Other=0
> > Buffer Counts: Commited=408053 Target=408053 Hashed=327655
> > InternalReservation=1774 ExternalReservation=0 Min Free=512
> > Procedure Cache: TotalProcs=22 TotalPages=63 InUsePages=63
> > Dynamic Memory Manager: Stolen=80398 OS Reserved=2392
> > OS Committed=2341
> > OS In Use=2274
> > Query Plan=67512 Optimizer=17
> > General=11800
> > Utilities=29 Connection=2764
> > Global Memory Objects: Resource=8601 Locks=104
> > SQLCache=236 Replication=76
> > LockBytes=2 ServerGlobal=47
> > Xact=2809
> > Query Memory Manager: Grants=0 Waiting=0 Maximum=93356 Available=93356
> >
> >
>|||I hope you are using Enterprise Edition of SQL Server otherwise you won't be
able to utilize more than 2GB of Ram anyway. If you have 9GB (before you
said 4GB) and want to set AWE make sure you set the max memory in SQL Server
to less than 9GB. I would advise maybe starting at 7GB and see how that
works out. You were runing with 1GB before. 9GB is a big jump, are you
sure you need that much. If you can't utilize all the memory you actually
make SQL Server do extra work.
--
Andrew J. Kelly SQL MVP
"Nags" <nags@.DontSpamMe.com> wrote in message
news:OWdxf9KeEHA.2764@.TK2MSFTNGP11.phx.gbl...
> The /3GB was not set in the Boot.ini file. My fault, I assumed that our
> hardware guys had already done it as it was a prerequisite, before they
> handed over the server to the DBA's. Its all set now and will be
monitoring
> it for couple of days to make sure this error does not occur again.
> We put /3GB /PAE in boot.ini as we have 9Gig of RAM.
> Thank you,
> -Nags
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eLZmSvKeEHA.3512@.TK2MSFTNGP12.phx.gbl...
> > How about turning off AWE and make sure the /3GB switch is set in the
> > Boot.ini file.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Nags" <nags@.DontSpamMe.com> wrote in message
> > news:%23Zw7f2JeEHA.1036@.TK2MSFTNGP10.phx.gbl...
> > > We are getting the following errror on SQL Server : We have about
4GB
> > > memory and has AWE enabled. Why are we getting these errors? We just
> > > increase the memory from 1 Gig to 4 Gig and we are assuming that now
we
> > have
> > > sufficient memory.
> > >
> > > LazyWriter: warning, no free buffers found.
> > > Buffer Distribution: Stolen=91967 Free=0 Procedures=726
> > > Inram=292678 Dirty=20958 Kept=0
> > > I/O=0, Latched=1724, Other=0
> > > Buffer Counts: Commited=408053 Target=408053 Hashed=315360
> > > InternalReservation=525 ExternalReservation=16375 Min Free=512
> > > Procedure Cache: TotalProcs=186 TotalPages=726 InUsePages=726
> > > Dynamic Memory Manager: Stolen=91784 OS Reserved=2384
> > > OS Committed=2333
> > > OS In Use=2304
> > > Query Plan=77132 Optimizer=295
> > > General=11448
> > > Utilities=32 Connection=4067
> > > Global Memory Objects: Resource=9076 Locks=185
> > > SQLCache=375 Replication=35
> > > LockBytes=2 ServerGlobal=47
> > > Xact=1670
> > > Query Memory Manager: Grants=2 Waiting=0 Maximum=85781
Available=68497
> > >
> > > Error: 17803, Severity: 20, State: 14
> > > Insufficient memory available..
> > > LazyWriter: warning, no free buffers found.
> > > Buffer Distribution: Stolen=80335 Free=0 Procedures=63
> > > Inram=307141 Dirty=19250 Kept=0
> > > I/O=0, Latched=1264, Other=0
> > > Buffer Counts: Commited=408053 Target=408053 Hashed=327655
> > > InternalReservation=1774 ExternalReservation=0 Min Free=512
> > > Procedure Cache: TotalProcs=22 TotalPages=63 InUsePages=63
> > > Dynamic Memory Manager: Stolen=80398 OS Reserved=2392
> > > OS Committed=2341
> > > OS In Use=2274
> > > Query Plan=67512 Optimizer=17
> > > General=11800
> > > Utilities=29 Connection=2764
> > > Global Memory Objects: Resource=8601 Locks=104
> > > SQLCache=236 Replication=76
> > > LockBytes=2 ServerGlobal=47
> > > Xact=2809
> > > Query Memory Manager: Grants=0 Waiting=0 Maximum=93356
Available=93356
> > >
> > >
> >
> >
>|||Yes we have Enterprise Edition of SQL Server and Enterprise Edition of
Windows 2003.
Microsoft SQL Server 2000 - 8.00.818 (Intel X86) May 31 2003 16:08:15
Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows
NT 5.2 (Build 3790: )
We had enabled AWE and were restricting SQL Server at 4 GB to see how it
would perform. We were actually running out of memory before when we had
only 1 Gig. When we went for the new server we went to higher memory, as we
never know when we will need it :o)
On one of our production server we have 32 GB to pin objects into memory for
better performance.
Why do you say "If you can't utilize all the memory".. SQL Server will keep
using all the memory available and will cache all the objects and pages that
it will access. The more memory we have the more it will cache into memory,
probably with everything in memory rather than going to disk .. right ?
Over a period the data most frequently accessed will be in memory with
almost no access to disks .. right ?
-Nags
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OfA5RqLeEHA.3756@.TK2MSFTNGP12.phx.gbl...
> I hope you are using Enterprise Edition of SQL Server otherwise you won't
be
> able to utilize more than 2GB of Ram anyway. If you have 9GB (before you
> said 4GB) and want to set AWE make sure you set the max memory in SQL
Server
> to less than 9GB. I would advise maybe starting at 7GB and see how that
> works out. You were runing with 1GB before. 9GB is a big jump, are you
> sure you need that much. If you can't utilize all the memory you actually
> make SQL Server do extra work.
> --
> Andrew J. Kelly SQL MVP
>
> "Nags" <nags@.DontSpamMe.com> wrote in message
> news:OWdxf9KeEHA.2764@.TK2MSFTNGP11.phx.gbl...
> > The /3GB was not set in the Boot.ini file. My fault, I assumed that our
> > hardware guys had already done it as it was a prerequisite, before they
> > handed over the server to the DBA's. Its all set now and will be
> monitoring
> > it for couple of days to make sure this error does not occur again.
> >
> > We put /3GB /PAE in boot.ini as we have 9Gig of RAM.
> >
> > Thank you,
> >
> > -Nags
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:eLZmSvKeEHA.3512@.TK2MSFTNGP12.phx.gbl...
> > > How about turning off AWE and make sure the /3GB switch is set in the
> > > Boot.ini file.
> > >
> > > --
> > > Andrew J. Kelly SQL MVP
> > >
> > >
> > > "Nags" <nags@.DontSpamMe.com> wrote in message
> > > news:%23Zw7f2JeEHA.1036@.TK2MSFTNGP10.phx.gbl...
> > > > We are getting the following errror on SQL Server : We have about
> 4GB
> > > > memory and has AWE enabled. Why are we getting these errors? We
just
> > > > increase the memory from 1 Gig to 4 Gig and we are assuming that now
> we
> > > have
> > > > sufficient memory.
> > > >
> > > > LazyWriter: warning, no free buffers found.
> > > > Buffer Distribution: Stolen=91967 Free=0 Procedures=726
> > > > Inram=292678 Dirty=20958 Kept=0
> > > > I/O=0, Latched=1724, Other=0
> > > > Buffer Counts: Commited=408053 Target=408053 Hashed=315360
> > > > InternalReservation=525 ExternalReservation=16375 Min Free=512
> > > > Procedure Cache: TotalProcs=186 TotalPages=726 InUsePages=726
> > > > Dynamic Memory Manager: Stolen=91784 OS Reserved=2384
> > > > OS Committed=2333
> > > > OS In Use=2304
> > > > Query Plan=77132 Optimizer=295
> > > > General=11448
> > > > Utilities=32 Connection=4067
> > > > Global Memory Objects: Resource=9076 Locks=185
> > > > SQLCache=375 Replication=35
> > > > LockBytes=2 ServerGlobal=47
> > > > Xact=1670
> > > > Query Memory Manager: Grants=2 Waiting=0 Maximum=85781
> Available=68497
> > > >
> > > > Error: 17803, Severity: 20, State: 14
> > > > Insufficient memory available..
> > > > LazyWriter: warning, no free buffers found.
> > > > Buffer Distribution: Stolen=80335 Free=0 Procedures=63
> > > > Inram=307141 Dirty=19250 Kept=0
> > > > I/O=0, Latched=1264, Other=0
> > > > Buffer Counts: Commited=408053 Target=408053 Hashed=327655
> > > > InternalReservation=1774 ExternalReservation=0 Min Free=512
> > > > Procedure Cache: TotalProcs=22 TotalPages=63 InUsePages=63
> > > > Dynamic Memory Manager: Stolen=80398 OS Reserved=2392
> > > > OS Committed=2341
> > > > OS In Use=2274
> > > > Query Plan=67512 Optimizer=17
> > > > General=11800
> > > > Utilities=29 Connection=2764
> > > > Global Memory Objects: Resource=8601 Locks=104
> > > > SQLCache=236 Replication=76
> > > > LockBytes=2 ServerGlobal=47
> > > > Xact=2809
> > > > Query Memory Manager: Grants=0 Waiting=0 Maximum=93356
> Available=93356
> > > >
> > > >
> > >
> > >
> >
> >
>|||Two things here. First off pinning tables in memory may hurt performance
more than help. If the tables are accessed frequently then SQL Server has
an internal process to keep those pages in memory over less frequently
accessed data. By you pinning a table you run the risk of using memory for
data that although you accessed it once it may never be accessed again (or
infrequently) and that memory may have been better utilized by some other
tables data. In almost all instances you can not determine better than SQL
Server what should stay in cache and what should not given all the
circumstances the engine has to deal with. By the other statement of can't
utilize all the memory I meant this. Just because you have a 10 GB database
does not mean you will access all 10GB all the time. As a matter of fact in
a typical OLTP app only a fraction of the data is accessed each day. Maybe
less than 5 or 10% on average. But if you have 10GB of cache available SQL
Server will load what ever data you access into cache and it will stay there
forever until one of several things happen.
You restart SQL Server
You run DBCC DROPCLEANBUFFERS
The memroy is needed for something else.
So lets say over time you loaded all 10GB into memory but you only use say
1GB on any given day. SQL Server has to manage all 10GB of memory
regardless of how you use it. There are several processes that deal with it
but lets just take the lazy writer process. On a regular basis it scans all
10GB's of data in cache and does several things. One is it writes dirty
buffers to disk. Another is that it adjusts a counter on each page to help
it determine which page it can free up if needed. So it is doing theses
checks for 9GB of data that really will hardly if ever be touched but the
overhead is still there. If you only had say 1.5GB of memory you can keep
in all the most used data and leave a little for the infrequently used
stuff. If every so often you have to access the hard drive for this
infrequently used data it will overwrite the other infrequently used pages
and not the most accessed ones. This minimal drive access is usually not
that big a deal and you save a lot of overhead in hte engine for little
gain.
Now I am not saying you should only have a few extra bytes over and above
what you regulary use but you can get too excessive with extra memroy. Too
much is better than not enough in most cases but way too much is not good
either.
--
Andrew J. Kelly SQL MVP
"Nags" <nags@.DontSpamMe.com> wrote in message
news:eEs1R1LeEHA.3132@.TK2MSFTNGP11.phx.gbl...
> Yes we have Enterprise Edition of SQL Server and Enterprise Edition of
> Windows 2003.
> Microsoft SQL Server 2000 - 8.00.818 (Intel X86) May 31 2003 16:08:15
> Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on
Windows
> NT 5.2 (Build 3790: )
> We had enabled AWE and were restricting SQL Server at 4 GB to see how it
> would perform. We were actually running out of memory before when we had
> only 1 Gig. When we went for the new server we went to higher memory, as
we
> never know when we will need it :o)
> On one of our production server we have 32 GB to pin objects into memory
for
> better performance.
> Why do you say "If you can't utilize all the memory".. SQL Server will
keep
> using all the memory available and will cache all the objects and pages
that
> it will access. The more memory we have the more it will cache into
memory,
> probably with everything in memory rather than going to disk .. right ?
> Over a period the data most frequently accessed will be in memory with
> almost no access to disks .. right ?
> -Nags
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OfA5RqLeEHA.3756@.TK2MSFTNGP12.phx.gbl...
> > I hope you are using Enterprise Edition of SQL Server otherwise you
won't
> be
> > able to utilize more than 2GB of Ram anyway. If you have 9GB (before
you
> > said 4GB) and want to set AWE make sure you set the max memory in SQL
> Server
> > to less than 9GB. I would advise maybe starting at 7GB and see how that
> > works out. You were runing with 1GB before. 9GB is a big jump, are
you
> > sure you need that much. If you can't utilize all the memory you
actually
> > make SQL Server do extra work.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Nags" <nags@.DontSpamMe.com> wrote in message
> > news:OWdxf9KeEHA.2764@.TK2MSFTNGP11.phx.gbl...
> > > The /3GB was not set in the Boot.ini file. My fault, I assumed that
our
> > > hardware guys had already done it as it was a prerequisite, before
they
> > > handed over the server to the DBA's. Its all set now and will be
> > monitoring
> > > it for couple of days to make sure this error does not occur again.
> > >
> > > We put /3GB /PAE in boot.ini as we have 9Gig of RAM.
> > >
> > > Thank you,
> > >
> > > -Nags
> > > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > > news:eLZmSvKeEHA.3512@.TK2MSFTNGP12.phx.gbl...
> > > > How about turning off AWE and make sure the /3GB switch is set in
the
> > > > Boot.ini file.
> > > >
> > > > --
> > > > Andrew J. Kelly SQL MVP
> > > >
> > > >
> > > > "Nags" <nags@.DontSpamMe.com> wrote in message
> > > > news:%23Zw7f2JeEHA.1036@.TK2MSFTNGP10.phx.gbl...
> > > > > We are getting the following errror on SQL Server : We have
about
> > 4GB
> > > > > memory and has AWE enabled. Why are we getting these errors? We
> just
> > > > > increase the memory from 1 Gig to 4 Gig and we are assuming that
now
> > we
> > > > have
> > > > > sufficient memory.
> > > > >
> > > > > LazyWriter: warning, no free buffers found.
> > > > > Buffer Distribution: Stolen=91967 Free=0 Procedures=726
> > > > > Inram=292678 Dirty=20958 Kept=0
> > > > > I/O=0, Latched=1724, Other=0
> > > > > Buffer Counts: Commited=408053 Target=408053 Hashed=315360
> > > > > InternalReservation=525 ExternalReservation=16375 Min Free=512
> > > > > Procedure Cache: TotalProcs=186 TotalPages=726 InUsePages=726
> > > > > Dynamic Memory Manager: Stolen=91784 OS Reserved=2384
> > > > > OS Committed=2333
> > > > > OS In Use=2304
> > > > > Query Plan=77132 Optimizer=295
> > > > > General=11448
> > > > > Utilities=32 Connection=4067
> > > > > Global Memory Objects: Resource=9076 Locks=185
> > > > > SQLCache=375 Replication=35
> > > > > LockBytes=2 ServerGlobal=47
> > > > > Xact=1670
> > > > > Query Memory Manager: Grants=2 Waiting=0 Maximum=85781
> > Available=68497
> > > > >
> > > > > Error: 17803, Severity: 20, State: 14
> > > > > Insufficient memory available..
> > > > > LazyWriter: warning, no free buffers found.
> > > > > Buffer Distribution: Stolen=80335 Free=0 Procedures=63
> > > > > Inram=307141 Dirty=19250 Kept=0
> > > > > I/O=0, Latched=1264, Other=0
> > > > > Buffer Counts: Commited=408053 Target=408053 Hashed=327655
> > > > > InternalReservation=1774 ExternalReservation=0 Min Free=512
> > > > > Procedure Cache: TotalProcs=22 TotalPages=63 InUsePages=63
> > > > > Dynamic Memory Manager: Stolen=80398 OS Reserved=2392
> > > > > OS Committed=2341
> > > > > OS In Use=2274
> > > > > Query Plan=67512 Optimizer=17
> > > > > General=11800
> > > > > Utilities=29 Connection=2764
> > > > > Global Memory Objects: Resource=8601 Locks=104
> > > > > SQLCache=236 Replication=76
> > > > > LockBytes=2 ServerGlobal=47
> > > > > Xact=2809
> > > > > Query Memory Manager: Grants=0 Waiting=0 Maximum=93356
> > Available=93356
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||I totally agree, but with AWE enabled, SQL Server preallocates memory
instead of allocating as required.
And secondly, if we restrict SQL Server to start with low memory and we want
to increase it later, we have to restart the server. Sometimes, we cannot
restart for months.
We do have huge processing power, so the slight overhead for scanning the
memory should be ok.
-Nags
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:echyFUMeEHA.3476@.tk2msftngp13.phx.gbl...
> Two things here. First off pinning tables in memory may hurt performance
> more than help. If the tables are accessed frequently then SQL Server has
> an internal process to keep those pages in memory over less frequently
> accessed data. By you pinning a table you run the risk of using memory
for
> data that although you accessed it once it may never be accessed again (or
> infrequently) and that memory may have been better utilized by some other
> tables data. In almost all instances you can not determine better than
SQL
> Server what should stay in cache and what should not given all the
> circumstances the engine has to deal with. By the other statement of
can't
> utilize all the memory I meant this. Just because you have a 10 GB
database
> does not mean you will access all 10GB all the time. As a matter of fact
in
> a typical OLTP app only a fraction of the data is accessed each day. Maybe
> less than 5 or 10% on average. But if you have 10GB of cache available
SQL
> Server will load what ever data you access into cache and it will stay
there
> forever until one of several things happen.
> You restart SQL Server
> You run DBCC DROPCLEANBUFFERS
> The memroy is needed for something else.
> So lets say over time you loaded all 10GB into memory but you only use say
> 1GB on any given day. SQL Server has to manage all 10GB of memory
> regardless of how you use it. There are several processes that deal with
it
> but lets just take the lazy writer process. On a regular basis it scans
all
> 10GB's of data in cache and does several things. One is it writes dirty
> buffers to disk. Another is that it adjusts a counter on each page to
help
> it determine which page it can free up if needed. So it is doing theses
> checks for 9GB of data that really will hardly if ever be touched but the
> overhead is still there. If you only had say 1.5GB of memory you can
keep
> in all the most used data and leave a little for the infrequently used
> stuff. If every so often you have to access the hard drive for this
> infrequently used data it will overwrite the other infrequently used pages
> and not the most accessed ones. This minimal drive access is usually not
> that big a deal and you save a lot of overhead in hte engine for little
> gain.
> Now I am not saying you should only have a few extra bytes over and above
> what you regulary use but you can get too excessive with extra memroy.
Too
> much is better than not enough in most cases but way too much is not good
> either.
> --
> Andrew J. Kelly SQL MVP
>
> "Nags" <nags@.DontSpamMe.com> wrote in message
> news:eEs1R1LeEHA.3132@.TK2MSFTNGP11.phx.gbl...
> > Yes we have Enterprise Edition of SQL Server and Enterprise Edition of
> > Windows 2003.
> >
> > Microsoft SQL Server 2000 - 8.00.818 (Intel X86) May 31 2003 16:08:15
> > Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on
> Windows
> > NT 5.2 (Build 3790: )
> >
> > We had enabled AWE and were restricting SQL Server at 4 GB to see how it
> > would perform. We were actually running out of memory before when we
had
> > only 1 Gig. When we went for the new server we went to higher memory,
as
> we
> > never know when we will need it :o)
> >
> > On one of our production server we have 32 GB to pin objects into memory
> for
> > better performance.
> >
> > Why do you say "If you can't utilize all the memory".. SQL Server will
> keep
> > using all the memory available and will cache all the objects and pages
> that
> > it will access. The more memory we have the more it will cache into
> memory,
> > probably with everything in memory rather than going to disk .. right ?
> > Over a period the data most frequently accessed will be in memory with
> > almost no access to disks .. right ?
> >
> > -Nags
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:OfA5RqLeEHA.3756@.TK2MSFTNGP12.phx.gbl...
> > > I hope you are using Enterprise Edition of SQL Server otherwise you
> won't
> > be
> > > able to utilize more than 2GB of Ram anyway. If you have 9GB (before
> you
> > > said 4GB) and want to set AWE make sure you set the max memory in SQL
> > Server
> > > to less than 9GB. I would advise maybe starting at 7GB and see how
that
> > > works out. You were runing with 1GB before. 9GB is a big jump, are
> you
> > > sure you need that much. If you can't utilize all the memory you
> actually
> > > make SQL Server do extra work.
> > >
> > > --
> > > Andrew J. Kelly SQL MVP
> > >
> > >
> > > "Nags" <nags@.DontSpamMe.com> wrote in message
> > > news:OWdxf9KeEHA.2764@.TK2MSFTNGP11.phx.gbl...
> > > > The /3GB was not set in the Boot.ini file. My fault, I assumed that
> our
> > > > hardware guys had already done it as it was a prerequisite, before
> they
> > > > handed over the server to the DBA's. Its all set now and will be
> > > monitoring
> > > > it for couple of days to make sure this error does not occur again.
> > > >
> > > > We put /3GB /PAE in boot.ini as we have 9Gig of RAM.
> > > >
> > > > Thank you,
> > > >
> > > > -Nags
> > > > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > > > news:eLZmSvKeEHA.3512@.TK2MSFTNGP12.phx.gbl...
> > > > > How about turning off AWE and make sure the /3GB switch is set in
> the
> > > > > Boot.ini file.
> > > > >
> > > > > --
> > > > > Andrew J. Kelly SQL MVP
> > > > >
> > > > >
> > > > > "Nags" <nags@.DontSpamMe.com> wrote in message
> > > > > news:%23Zw7f2JeEHA.1036@.TK2MSFTNGP10.phx.gbl...
> > > > > > We are getting the following errror on SQL Server : We have
> about
> > > 4GB
> > > > > > memory and has AWE enabled. Why are we getting these errors?
We
> > just
> > > > > > increase the memory from 1 Gig to 4 Gig and we are assuming that
> now
> > > we
> > > > > have
> > > > > > sufficient memory.
> > > > > >
> > > > > > LazyWriter: warning, no free buffers found.
> > > > > > Buffer Distribution: Stolen=91967 Free=0 Procedures=726
> > > > > > Inram=292678 Dirty=20958 Kept=0
> > > > > > I/O=0, Latched=1724, Other=0
> > > > > > Buffer Counts: Commited=408053 Target=408053 Hashed=315360
> > > > > > InternalReservation=525 ExternalReservation=16375 Min Free=512
> > > > > > Procedure Cache: TotalProcs=186 TotalPages=726 InUsePages=726
> > > > > > Dynamic Memory Manager: Stolen=91784 OS Reserved=2384
> > > > > > OS Committed=2333
> > > > > > OS In Use=2304
> > > > > > Query Plan=77132 Optimizer=295
> > > > > > General=11448
> > > > > > Utilities=32 Connection=4067
> > > > > > Global Memory Objects: Resource=9076 Locks=185
> > > > > > SQLCache=375 Replication=35
> > > > > > LockBytes=2 ServerGlobal=47
> > > > > > Xact=1670
> > > > > > Query Memory Manager: Grants=2 Waiting=0 Maximum=85781
> > > Available=68497
> > > > > >
> > > > > > Error: 17803, Severity: 20, State: 14
> > > > > > Insufficient memory available..
> > > > > > LazyWriter: warning, no free buffers found.
> > > > > > Buffer Distribution: Stolen=80335 Free=0 Procedures=63
> > > > > > Inram=307141 Dirty=19250 Kept=0
> > > > > > I/O=0, Latched=1264, Other=0
> > > > > > Buffer Counts: Commited=408053 Target=408053 Hashed=327655
> > > > > > InternalReservation=1774 ExternalReservation=0 Min Free=512
> > > > > > Procedure Cache: TotalProcs=22 TotalPages=63 InUsePages=63
> > > > > > Dynamic Memory Manager: Stolen=80398 OS Reserved=2392
> > > > > > OS Committed=2341
> > > > > > OS In Use=2274
> > > > > > Query Plan=67512 Optimizer=17
> > > > > > General=11800
> > > > > > Utilities=29 Connection=2764
> > > > > > Global Memory Objects: Resource=8601 Locks=104
> > > > > > SQLCache=236 Replication=76
> > > > > > LockBytes=2 ServerGlobal=47
> > > > > > Xact=2809
> > > > > > Query Memory Manager: Grants=0 Waiting=0 Maximum=93356
> > > Available=93356
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>