buffer.SetString not writing values!

Hello all,

I'm writing a custom component similar to the "Derived Column" transformation, in which a value of some input column is "decoded" into an output column.

Here's the code:

Code Snippet

public override void ProcessInput(int inputID, PipelineBuffer buffer){

//TODO

setupTraceListener();

IDTSOutput90 outp = ComponentMetaData.OutputCollection["DecodeOutput"];

int outId = outp.ID;

int errorOut = -1;

IDTSInput90 inp = ComponentMetaData.InputCollection.GetObjectByID(inputID);

if( ComponentMetaData.OutputCollection.Count > 1){errorOut = ComponentMetaData.OutputCollection["DecodeErrors"].ID;

}

string sourceValue;

if(!buffer.EndOfRowset){

while(buffer.NextRow()){

if(!buffer.IsNull(inputColumnBufferIndex)){

switch(inp.InputColumnCollection[0].DataType){

case DataType.DT_WSTR:

sourceValue = buffer.GetString(this.inputColumnBufferIndex);

break;

case DataType.DT_I4:

sourceValue = buffer.GetInt32(this.inputColumnBufferIndex).ToString();

break;

default: throw new Exception("Invalid Data Type!");

}

sourceValue = sourceValue.Trim();

try{

object decodedValueObj = this.mappings[sourceValue];

if(decodedValueObj == null){

throw new Exception("No mapping!");

}

string decodedValue = decodedValueObj.ToString();

switch(outp.OutputColumnCollection[0].DataType){

case DataType.DT_WSTR:

buffer.SetString(this.outputColumnBufferIndex, decodedValue);

break;

case DataType.DT_I4:

buffer.SetInt32(this.outputColumnBufferIndex, int.Parse(decodedValue));

break;

default:

throw new IOException("Invalid Data Type!");

}

buffer.DirectRow(outId);

}catch(IOException fake){

throw fake;

}catch(Exception e){

redirectOrFail(inp, errorOut, outId, buffer, e);

}

}else{

redirectOrFail(inp,errorOut,outId,buffer,new Exception("Null values cannot be mapped"));

}

}

}

}

mappings is a hash map filled in PreExecute by accessing a database, outputColumnBufferIndex is calculated like this:

Code Snippet

IDTSInput90 input = ComponentMetaData.InputCollection[0];

IDTSOutput90 output = ComponentMetaData.OutputCollection["DecodeOutput"];

IDTSInputColumn90 inputColumn = input.InputColumnCollection[0];

IDTSOutputColumn90 outputColumn = output.OutputColumnCollection[0];

this.inputColumnBufferIndex =

BufferManager.FindColumnByLineageID(input.Buffer, inputColumn.LineageID);

this.outputColumnBufferIndex =

BufferManager.FindColumnByLineageID(input.Buffer, outputColumn.LineageID);

I've highlighted the parts that interest me, I've been testing the component thoroughly and it is getting the input data and mappings right, but it won't write the Decoded value to the given column and leaves it empty, even though the decodedValue variable is not null.

I'm guessing theres a problem when I call buffer.SetString() but I haven't the slightest idea of what that could be.

Any help would be greatly appreciated!

Have you debugged it? Do inputColumnBufferIndex and outputColumnBufferIndex values make sense?

Do you only have one input and one output column in your component?

Are you getting any errors from SetString?

Thanks,

Bob

|||

Thank you for your reply Bob,

Yes, I did debug it and the values made sense. Thats how I know that the decodedValue was being also properly initialized.

It's working now by using:

Code Snippet

buffer[this.outputColumnBufferIndex] = decodedValue;

Don't ask me why.

I'm having all sorts of problems that have very easy but completely undocumented solutions like having to override PerformUpgrade to update the UserComponentTypeName attribute and the Version, even though I'm not changing either one as the component is still in development and not in use in any "real" packages.

Maybe my installation is corrupted somehow?

|||

Hmm, you should not be required to implement PerformUpgrade unless you are really changing your component.

Could you check your ProvideComponentProperties implementation and if there is something that might be messing up the settings?

Thanks,

Bob

|||

I don't know what to look for, this is my 3rd week with C# and SSIS.

Here's the basic idea:

Code Snippet

public override void ProvideComponentProperties(){

//FIXME

base.ProvideComponentProperties();

(set the name, dispositions, etc...)

base.RemoveAllInputsOutputsAndCustomProperties();

(add synchronous inputs and outputs)

(add custom properties)

}

I suspect it could be I'm calling RemoveAllInpusOutputsAndCustomProperties(); after base.ProvideComponentProperties(), but I don't want to touch it just yet. What are your thoughts?

Thank you very much again.

|||

Yeah, that is it.

You can safely remove that line as ProvideComponentProperties is called only once in the SSIS designer.

Alternatively, you may move that line before base.ProvideComponentProperties().

Thanks,

Bob

|||Thank you very much for your help|||

Adrian, this is not related to your question, but there is a bug in how buffer.EndOfRowset is used in the ProcessInput method in the first code sample, please see my blog for details

http://blogs.msdn.com/michen/archive/2007/08/31/Buffer.EndOfRowset.aspx

|||

Thank you very much for that link! I'll be changing that right away.

buffer.SetString not writing values!

Hello all,

I'm writing a custom component similar to the "Derived Column" transformation, in which a value of some input column is "decoded" into an output column.

Here's the code:

Code Snippet

public override void ProcessInput(int inputID, PipelineBuffer buffer){

//TODO

setupTraceListener();

IDTSOutput90 outp = ComponentMetaData.OutputCollection["DecodeOutput"];

int outId = outp.ID;

int errorOut = -1;

IDTSInput90 inp = ComponentMetaData.InputCollection.GetObjectByID(inputID);

if( ComponentMetaData.OutputCollection.Count > 1){errorOut = ComponentMetaData.OutputCollection["DecodeErrors"].ID;

}

string sourceValue;

if(!buffer.EndOfRowset){

while(buffer.NextRow()){

if(!buffer.IsNull(inputColumnBufferIndex)){

switch(inp.InputColumnCollection[0].DataType){

case DataType.DT_WSTR:

sourceValue = buffer.GetString(this.inputColumnBufferIndex);

break;

case DataType.DT_I4:

sourceValue = buffer.GetInt32(this.inputColumnBufferIndex).ToString();

break;

default: throw new Exception("Invalid Data Type!");

}

sourceValue = sourceValue.Trim();

try{

object decodedValueObj = this.mappings[sourceValue];

if(decodedValueObj == null){

throw new Exception("No mapping!");

}

string decodedValue = decodedValueObj.ToString();

switch(outp.OutputColumnCollection[0].DataType){

case DataType.DT_WSTR:

buffer.SetString(this.outputColumnBufferIndex, decodedValue);

break;

case DataType.DT_I4:

buffer.SetInt32(this.outputColumnBufferIndex, int.Parse(decodedValue));

break;

default:

throw new IOException("Invalid Data Type!");

}

buffer.DirectRow(outId);

}catch(IOException fake){

throw fake;

}catch(Exception e){

redirectOrFail(inp, errorOut, outId, buffer, e);

}

}else{

redirectOrFail(inp,errorOut,outId,buffer,new Exception("Null values cannot be mapped"));

}

}

}

}

mappings is a hash map filled in PreExecute by accessing a database, outputColumnBufferIndex is calculated like this:

Code Snippet

IDTSInput90 input = ComponentMetaData.InputCollection[0];

IDTSOutput90 output = ComponentMetaData.OutputCollection["DecodeOutput"];

IDTSInputColumn90 inputColumn = input.InputColumnCollection[0];

IDTSOutputColumn90 outputColumn = output.OutputColumnCollection[0];

this.inputColumnBufferIndex =

BufferManager.FindColumnByLineageID(input.Buffer, inputColumn.LineageID);

this.outputColumnBufferIndex =

BufferManager.FindColumnByLineageID(input.Buffer, outputColumn.LineageID);

I've highlighted the parts that interest me, I've been testing the component thoroughly and it is getting the input data and mappings right, but it won't write the Decoded value to the given column and leaves it empty, even though the decodedValue variable is not null.

I'm guessing theres a problem when I call buffer.SetString() but I haven't the slightest idea of what that could be.

Any help would be greatly appreciated!

Have you debugged it? Do inputColumnBufferIndex and outputColumnBufferIndex values make sense?

Do you only have one input and one output column in your component?

Are you getting any errors from SetString?

Thanks,

Bob

|||

Thank you for your reply Bob,

Yes, I did debug it and the values made sense. Thats how I know that the decodedValue was being also properly initialized.

It's working now by using:

Code Snippet

buffer[this.outputColumnBufferIndex] = decodedValue;

Don't ask me why.

I'm having all sorts of problems that have very easy but completely undocumented solutions like having to override PerformUpgrade to update the UserComponentTypeName attribute and the Version, even though I'm not changing either one as the component is still in development and not in use in any "real" packages.

Maybe my installation is corrupted somehow?

|||

Hmm, you should not be required to implement PerformUpgrade unless you are really changing your component.

Could you check your ProvideComponentProperties implementation and if there is something that might be messing up the settings?

Thanks,

Bob

|||

I don't know what to look for, this is my 3rd week with C# and SSIS.

Here's the basic idea:

Code Snippet

public override void ProvideComponentProperties(){

//FIXME

base.ProvideComponentProperties();

(set the name, dispositions, etc...)

base.RemoveAllInputsOutputsAndCustomProperties();

(add synchronous inputs and outputs)

(add custom properties)

}

I suspect it could be I'm calling RemoveAllInpusOutputsAndCustomProperties(); after base.ProvideComponentProperties(), but I don't want to touch it just yet. What are your thoughts?

Thank you very much again.

|||

Yeah, that is it.

You can safely remove that line as ProvideComponentProperties is called only once in the SSIS designer.

Alternatively, you may move that line before base.ProvideComponentProperties().

Thanks,

Bob

|||Thank you very much for your help|||

Adrian, this is not related to your question, but there is a bug in how buffer.EndOfRowset is used in the ProcessInput method in the first code sample, please see my blog for details

http://blogs.msdn.com/michen/archive/2007/08/31/Buffer.EndOfRowset.aspx

|||

Thank you very much for that link! I'll be changing that right away.

buffer.SetString not writing values!

Hello all,

I'm writing a custom component similar to the "Derived Column" transformation, in which a value of some input column is "decoded" into an output column.

Here's the code:

Code Snippet

public override void ProcessInput(int inputID, PipelineBuffer buffer){

//TODO

setupTraceListener();

IDTSOutput90 outp = ComponentMetaData.OutputCollection["DecodeOutput"];

int outId = outp.ID;

int errorOut = -1;

IDTSInput90 inp = ComponentMetaData.InputCollection.GetObjectByID(inputID);

if( ComponentMetaData.OutputCollection.Count > 1){errorOut = ComponentMetaData.OutputCollection["DecodeErrors"].ID;

}

string sourceValue;

if(!buffer.EndOfRowset){

while(buffer.NextRow()){

if(!buffer.IsNull(inputColumnBufferIndex)){

switch(inp.InputColumnCollection[0].DataType){

case DataType.DT_WSTR:

sourceValue = buffer.GetString(this.inputColumnBufferIndex);

break;

case DataType.DT_I4:

sourceValue = buffer.GetInt32(this.inputColumnBufferIndex).ToString();

break;

default: throw new Exception("Invalid Data Type!");

}

sourceValue = sourceValue.Trim();

try{

object decodedValueObj = this.mappings[sourceValue];

if(decodedValueObj == null){

throw new Exception("No mapping!");

}

string decodedValue = decodedValueObj.ToString();

switch(outp.OutputColumnCollection[0].DataType){

case DataType.DT_WSTR:

buffer.SetString(this.outputColumnBufferIndex, decodedValue);

break;

case DataType.DT_I4:

buffer.SetInt32(this.outputColumnBufferIndex, int.Parse(decodedValue));

break;

default:

throw new IOException("Invalid Data Type!");

}

buffer.DirectRow(outId);

}catch(IOException fake){

throw fake;

}catch(Exception e){

redirectOrFail(inp, errorOut, outId, buffer, e);

}

}else{

redirectOrFail(inp,errorOut,outId,buffer,new Exception("Null values cannot be mapped"));

}

}

}

}

mappings is a hash map filled in PreExecute by accessing a database, outputColumnBufferIndex is calculated like this:

Code Snippet

IDTSInput90 input = ComponentMetaData.InputCollection[0];

IDTSOutput90 output = ComponentMetaData.OutputCollection["DecodeOutput"];

IDTSInputColumn90 inputColumn = input.InputColumnCollection[0];

IDTSOutputColumn90 outputColumn = output.OutputColumnCollection[0];

this.inputColumnBufferIndex =

BufferManager.FindColumnByLineageID(input.Buffer, inputColumn.LineageID);

this.outputColumnBufferIndex =

BufferManager.FindColumnByLineageID(input.Buffer, outputColumn.LineageID);

I've highlighted the parts that interest me, I've been testing the component thoroughly and it is getting the input data and mappings right, but it won't write the Decoded value to the given column and leaves it empty, even though the decodedValue variable is not null.

I'm guessing theres a problem when I call buffer.SetString() but I haven't the slightest idea of what that could be.

Any help would be greatly appreciated!

Have you debugged it? Do inputColumnBufferIndex and outputColumnBufferIndex values make sense?

Do you only have one input and one output column in your component?

Are you getting any errors from SetString?

Thanks,

Bob

|||

Thank you for your reply Bob,

Yes, I did debug it and the values made sense. Thats how I know that the decodedValue was being also properly initialized.

It's working now by using:

Code Snippet

buffer[this.outputColumnBufferIndex] = decodedValue;

Don't ask me why.

I'm having all sorts of problems that have very easy but completely undocumented solutions like having to override PerformUpgrade to update the UserComponentTypeName attribute and the Version, even though I'm not changing either one as the component is still in development and not in use in any "real" packages.

Maybe my installation is corrupted somehow?

|||

Hmm, you should not be required to implement PerformUpgrade unless you are really changing your component.

Could you check your ProvideComponentProperties implementation and if there is something that might be messing up the settings?

Thanks,

Bob

|||

I don't know what to look for, this is my 3rd week with C# and SSIS.

Here's the basic idea:

Code Snippet

public override void ProvideComponentProperties(){

//FIXME

base.ProvideComponentProperties();

(set the name, dispositions, etc...)

base.RemoveAllInputsOutputsAndCustomProperties();

(add synchronous inputs and outputs)

(add custom properties)

}

I suspect it could be I'm calling RemoveAllInpusOutputsAndCustomProperties(); after base.ProvideComponentProperties(), but I don't want to touch it just yet. What are your thoughts?

Thank you very much again.

|||

Yeah, that is it.

You can safely remove that line as ProvideComponentProperties is called only once in the SSIS designer.

Alternatively, you may move that line before base.ProvideComponentProperties().

Thanks,

Bob

|||Thank you very much for your help|||

Adrian, this is not related to your question, but there is a bug in how buffer.EndOfRowset is used in the ProcessInput method in the first code sample, please see my blog for details

http://blogs.msdn.com/michen/archive/2007/08/31/Buffer.EndOfRowset.aspx

|||

Thank you very much for that link! I'll be changing that right away.

Buffer size not specified error

Error: "The specified buffer size is not valid. [buffer size specified = 0]

Hello, im very new to SQL 2005 everywhere but looked like it could do the job for what i needed:

Im working on a c# (.net 2.0) project and loaded data

(one column from one table, 800 rows, text, no greater than 80characters in length)

from an access db into a data set, then lnserted the data in SQLce, great it works fab!

but as soon as I select another field(text, <=10) from the access db, and try to insert it into sql i get the error...

what have i missed?

could you post the code you are using to do the insertion and some info about the schema of the table you are inserting the records into? then we can see what's going on here.

thanks,

Darren

|||The problem was due to a field size not being long enough, i increased the field size and works great.

Buffer Pool vs MemToLeave Area

Whats in the buffer pool and whats in the memtoleave area ? Is this all in
RAM or could it also be virtual memory/pagefile ? Using SQL 2000Whether it is in virtual memory or not depends on how much ram you have and
how you set your system up. As for what is in there I would suggest you
take a look in BooksOnLine under "memory, pool" and it's associated links in
addition to "Inside SQL 2000" which I know you have now<g>.
Andrew J. Kelly
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23rFuqtE9DHA.3012@.TK2MSFTNGP09.phx.gbl...
> Whats in the buffer pool and whats in the memtoleave area ? Is this all in
> RAM or could it also be virtual memory/pagefile ? Using SQL 2000
>|||I didn't really talk about the memtoleave value in the book, but I discussed
it here
http://www.sqlmag.com/Articles/Inde...ArticleID=16522
The above article is several years old now, so it is freely accessible.
I also wrote two articles on memory in SQL Server Magazine last March and
April, but those are only available to subscribers, as they are less than a
year old.
http://www.sqlmag.com/Articles/Inde...ArticleID=37890
http://www.sqlmag.com/Articles/Inde...ArticleID=37908
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:#GqikKJ9DHA.1548@.tk2msftngp13.phx.gbl...
> Whether it is in virtual memory or not depends on how much ram you have
and
> how you set your system up. As for what is in there I would suggest you
> take a look in BooksOnLine under "memory, pool" and it's associated links
in
> addition to "Inside SQL 2000" which I know you have now<g>.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:%23rFuqtE9DHA.3012@.TK2MSFTNGP09.phx.gbl...
in
>|||Probably not but you do discuss the memory pool<g>.
Andrew J. Kelly
SQL Server MVP
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:uE1kiAL9DHA.2832@.tk2msftngp13.phx.gbl...
> I didn't really talk about the memtoleave value in the book, but I
discussed
> it here
> http://www.sqlmag.com/Articles/Inde...ArticleID=16522
> The above article is several years old now, so it is freely accessible.
> I also wrote two articles on memory in SQL Server Magazine last March and
> April, but those are only available to subscribers, as they are less than
a
> year old.
> http://www.sqlmag.com/Articles/Inde...ArticleID=37890
> http://www.sqlmag.com/Articles/Inde...ArticleID=37908
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:#GqikKJ9DHA.1548@.tk2msftngp13.phx.gbl...
> and
links
> in
all
> in
>|||Have a look at
http://msdn.microsoft.com/data/defa...ev_01262004.asp
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23rFuqtE9DHA.3012@.TK2MSFTNGP09.phx.gbl...
> Whats in the buffer pool and whats in the memtoleave area ? Is this all in
> RAM or could it also be virtual memory/pagefile ? Using SQL 2000
>|||That's a good one Jasper. How did I miss that?
Andrew J. Kelly
SQL Server MVP
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:eanijzL9DHA.2064@.TK2MSFTNGP11.phx.gbl...
> Have a look at
>
http://msdn.microsoft.com/data/defa...004.
asp
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:%23rFuqtE9DHA.3012@.TK2MSFTNGP09.phx.gbl...
in
>

Buffer Pool vs MemToLeave Area

Whats in the buffer pool and whats in the memtoleave area ? Is this all in
RAM or could it also be virtual memory/pagefile ? Using SQL 2000Whether it is in virtual memory or not depends on how much ram you have and
how you set your system up. As for what is in there I would suggest you
take a look in BooksOnLine under "memory, pool" and it's associated links in
addition to "Inside SQL 2000" which I know you have now<g>.
--
Andrew J. Kelly
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23rFuqtE9DHA.3012@.TK2MSFTNGP09.phx.gbl...
> Whats in the buffer pool and whats in the memtoleave area ? Is this all in
> RAM or could it also be virtual memory/pagefile ? Using SQL 2000
>|||I didn't really talk about the memtoleave value in the book, but I discussed
it here
http://www.sqlmag.com/Articles/Index.cfm?ArticleID=16522
The above article is several years old now, so it is freely accessible.
I also wrote two articles on memory in SQL Server Magazine last March and
April, but those are only available to subscribers, as they are less than a
year old.
http://www.sqlmag.com/Articles/Index.cfm?ArticleID=37890
http://www.sqlmag.com/Articles/Index.cfm?ArticleID=37908
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:#GqikKJ9DHA.1548@.tk2msftngp13.phx.gbl...
> Whether it is in virtual memory or not depends on how much ram you have
and
> how you set your system up. As for what is in there I would suggest you
> take a look in BooksOnLine under "memory, pool" and it's associated links
in
> addition to "Inside SQL 2000" which I know you have now<g>.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:%23rFuqtE9DHA.3012@.TK2MSFTNGP09.phx.gbl...
> > Whats in the buffer pool and whats in the memtoleave area ? Is this all
in
> > RAM or could it also be virtual memory/pagefile ? Using SQL 2000
> >
> >
>|||Probably not but you do discuss the memory pool<g>.
--
Andrew J. Kelly
SQL Server MVP
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:uE1kiAL9DHA.2832@.tk2msftngp13.phx.gbl...
> I didn't really talk about the memtoleave value in the book, but I
discussed
> it here
> http://www.sqlmag.com/Articles/Index.cfm?ArticleID=16522
> The above article is several years old now, so it is freely accessible.
> I also wrote two articles on memory in SQL Server Magazine last March and
> April, but those are only available to subscribers, as they are less than
a
> year old.
> http://www.sqlmag.com/Articles/Index.cfm?ArticleID=37890
> http://www.sqlmag.com/Articles/Index.cfm?ArticleID=37908
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:#GqikKJ9DHA.1548@.tk2msftngp13.phx.gbl...
> > Whether it is in virtual memory or not depends on how much ram you have
> and
> > how you set your system up. As for what is in there I would suggest you
> > take a look in BooksOnLine under "memory, pool" and it's associated
links
> in
> > addition to "Inside SQL 2000" which I know you have now<g>.
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > news:%23rFuqtE9DHA.3012@.TK2MSFTNGP09.phx.gbl...
> > > Whats in the buffer pool and whats in the memtoleave area ? Is this
all
> in
> > > RAM or could it also be virtual memory/pagefile ? Using SQL 2000
> > >
> > >
> >
> >
>|||Have a look at
http://msdn.microsoft.com/data/default.aspx?pull=/library/en-us/dnsqldev/html/sqldev_01262004.asp
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23rFuqtE9DHA.3012@.TK2MSFTNGP09.phx.gbl...
> Whats in the buffer pool and whats in the memtoleave area ? Is this all in
> RAM or could it also be virtual memory/pagefile ? Using SQL 2000
>|||That's a good one Jasper. How did I miss that?
--
Andrew J. Kelly
SQL Server MVP
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:eanijzL9DHA.2064@.TK2MSFTNGP11.phx.gbl...
> Have a look at
>
http://msdn.microsoft.com/data/default.aspx?pull=/library/en-us/dnsqldev/html/sqldev_01262004.asp
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:%23rFuqtE9DHA.3012@.TK2MSFTNGP09.phx.gbl...
> > Whats in the buffer pool and whats in the memtoleave area ? Is this all
in
> > RAM or could it also be virtual memory/pagefile ? Using SQL 2000
> >
> >
>

buffer pool only has data pages ?

Or does the buffer pool include procedure cache as well ?
I know in sql 2000, they have this mem to leave concept that I have heard is
no longer referred to in 2005. So whats it called in 2005?
ThanksCome on now Hassan this is getting beyond petty. Do you really expect
everyone to spoon feed you everything about SQL Server just because you
don't want to read a book or do a simple Google search. The answer to this
is certainly something you should already know by now but can be found in at
least 20 places with little trouble. Heck even BooksOnLine can answer that
question easily. 95% of the questions you ask here can be found in "Inside
SQL Server 2005" alone. Do everyone including yourself a favor and buy the
book and take a little time to read it. Or at least the sections that are
relevant to what you need to know at the time. You won't regret it.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Hassan" <hassan@.test.com> wrote in message
news:Om2ZjtcZIHA.4696@.TK2MSFTNGP05.phx.gbl...
> Or does the buffer pool include procedure cache as well ?
> I know in sql 2000, they have this mem to leave concept that I have heard
> is no longer referred to in 2005. So whats it called in 2005?
> Thanks|||Andrew J. Kelly wrote:
> Come on now Hassan this is getting beyond petty. Do you really expect
> everyone to spoon feed you everything about SQL Server just because
> you don't want to read a book or do a simple Google search. The answer
> to this is certainly something you should already know by now but can
> be found in at least 20 places with little trouble. Heck even
> BooksOnLine can answer that question easily. 95% of the questions you
> ask here can be found in "Inside SQL Server 2005" alone. Do everyone
> including yourself a favor and buy the book and take a little time to
> read it. Or at least the sections that are relevant to what you need
> to know at the time. You won't regret it.
>
And neither will we :)

Buffer Overrun?

Over the past couple of days we have seen the following message in the SQL
Server Logs:
"SqlSecurityHandler is invoked. Potential buffer overrun detected - server
is terminating."
At this point, the SQL Server and SQL Server Agent will terminate and have
to be restarted. We are running SQL2000(SP4) on Windows Server '03 (no SP).
Has anyone else seen this?We've just seen the error. Our production environment went down with that
message in the log. It took about 8 hours before we were up and running
again. (The Sql Server automatically started a restore operation)
We are also running SQL2000(SP4) on Windows Server 2003.
Veritas NetBackup had just finished a backup when the error occurred.
I really hope that Microsoft can help out with this, cause this is a
_serious_ problem.
SQL server just terminates!
Do you have any new information?
"JKKUS" wrote:

> Over the past couple of days we have seen the following message in the SQL
> Server Logs:
> "SqlSecurityHandler is invoked. Potential buffer overrun detected - server
> is terminating."
> At this point, the SQL Server and SQL Server Agent will terminate and have
> to be restarted. We are running SQL2000(SP4) on Windows Server '03 (no SP
).
> Has anyone else seen this?
>|||Nothing yet. If you find anything out, please post. This problem is annoyi
ng.
"Bjorn L" wrote:
[vbcol=seagreen]
> We've just seen the error. Our production environment went down with that
> message in the log. It took about 8 hours before we were up and running
> again. (The Sql Server automatically started a restore operation)
> We are also running SQL2000(SP4) on Windows Server 2003.
> Veritas NetBackup had just finished a backup when the error occurred.
> I really hope that Microsoft can help out with this, cause this is a
> _serious_ problem.
> SQL server just terminates!
> Do you have any new information?
> "JKKUS" wrote:
>|||Its more than annoying to us, since our production database shuts down.
Anyway, from what we've seen, it definitely seems to be related to backups.
We disabled all backup activities this weekend, and from that on we haven't
seen the problem. (fingers crossed though)
We'll keep investigating.
Would be interesting to hear if its backups thats causing your problems as
well?
"JKKUS" wrote:
[vbcol=seagreen]
> Nothing yet. If you find anything out, please post. This problem is anno
ying.
> "Bjorn L" wrote:
>

Buffer Overrun?

Over the past couple of days we have seen the following message in the SQL
Server Logs:
"SqlSecurityHandler is invoked. Potential buffer overrun detected - server
is terminating."
At this point, the SQL Server and SQL Server Agent will terminate and have
to be restarted. We are running SQL2000(SP4) on Windows Server '03 (no SP).
Has anyone else seen this?
We've just seen the error. Our production environment went down with that
message in the log. It took about 8 hours before we were up and running
again. (The Sql Server automatically started a restore operation)
We are also running SQL2000(SP4) on Windows Server 2003.
Veritas NetBackup had just finished a backup when the error occurred.
I really hope that Microsoft can help out with this, cause this is a
_serious_ problem.
SQL server just terminates!
Do you have any new information?
"JKKUS" wrote:

> Over the past couple of days we have seen the following message in the SQL
> Server Logs:
> "SqlSecurityHandler is invoked. Potential buffer overrun detected - server
> is terminating."
> At this point, the SQL Server and SQL Server Agent will terminate and have
> to be restarted. We are running SQL2000(SP4) on Windows Server '03 (no SP).
> Has anyone else seen this?
>
|||Nothing yet. If you find anything out, please post. This problem is annoying.
"Bjorn L" wrote:
[vbcol=seagreen]
> We've just seen the error. Our production environment went down with that
> message in the log. It took about 8 hours before we were up and running
> again. (The Sql Server automatically started a restore operation)
> We are also running SQL2000(SP4) on Windows Server 2003.
> Veritas NetBackup had just finished a backup when the error occurred.
> I really hope that Microsoft can help out with this, cause this is a
> _serious_ problem.
> SQL server just terminates!
> Do you have any new information?
> "JKKUS" wrote:
|||Its more than annoying to us, since our production database shuts down.
Anyway, from what we've seen, it definitely seems to be related to backups.
We disabled all backup activities this weekend, and from that on we haven't
seen the problem. (fingers crossed though)
We'll keep investigating.
Would be interesting to hear if its backups thats causing your problems as
well?
"JKKUS" wrote:
[vbcol=seagreen]
> Nothing yet. If you find anything out, please post. This problem is annoying.
> "Bjorn L" wrote:

Buffer Overrun?

Over the past couple of days we have seen the following message in the SQL
Server Logs:
"SqlSecurityHandler is invoked. Potential buffer overrun detected - server
is terminating."
At this point, the SQL Server and SQL Server Agent will terminate and have
to be restarted. We are running SQL2000(SP4) on Windows Server '03 (no SP).
Has anyone else seen this?We've just seen the error. Our production environment went down with that
message in the log. It took about 8 hours before we were up and running
again. (The Sql Server automatically started a restore operation)
We are also running SQL2000(SP4) on Windows Server 2003.
Veritas NetBackup had just finished a backup when the error occurred.
I really hope that Microsoft can help out with this, cause this is a
_serious_ problem.
SQL server just terminates!
Do you have any new information?
"JKKUS" wrote:
> Over the past couple of days we have seen the following message in the SQL
> Server Logs:
> "SqlSecurityHandler is invoked. Potential buffer overrun detected - server
> is terminating."
> At this point, the SQL Server and SQL Server Agent will terminate and have
> to be restarted. We are running SQL2000(SP4) on Windows Server '03 (no SP).
> Has anyone else seen this?
>|||Nothing yet. If you find anything out, please post. This problem is annoying.
"Bjorn L" wrote:
> We've just seen the error. Our production environment went down with that
> message in the log. It took about 8 hours before we were up and running
> again. (The Sql Server automatically started a restore operation)
> We are also running SQL2000(SP4) on Windows Server 2003.
> Veritas NetBackup had just finished a backup when the error occurred.
> I really hope that Microsoft can help out with this, cause this is a
> _serious_ problem.
> SQL server just terminates!
> Do you have any new information?
> "JKKUS" wrote:
> > Over the past couple of days we have seen the following message in the SQL
> > Server Logs:
> >
> > "SqlSecurityHandler is invoked. Potential buffer overrun detected - server
> > is terminating."
> >
> > At this point, the SQL Server and SQL Server Agent will terminate and have
> > to be restarted. We are running SQL2000(SP4) on Windows Server '03 (no SP).
> > Has anyone else seen this?
> >
> >|||Its more than annoying to us, since our production database shuts down.
Anyway, from what we've seen, it definitely seems to be related to backups.
We disabled all backup activities this weekend, and from that on we haven't
seen the problem. (fingers crossed though)
We'll keep investigating.
Would be interesting to hear if its backups thats causing your problems as
well?
"JKKUS" wrote:
> Nothing yet. If you find anything out, please post. This problem is annoying.
> "Bjorn L" wrote:
> > We've just seen the error. Our production environment went down with that
> > message in the log. It took about 8 hours before we were up and running
> > again. (The Sql Server automatically started a restore operation)
> >
> > We are also running SQL2000(SP4) on Windows Server 2003.
> > Veritas NetBackup had just finished a backup when the error occurred.
> >
> > I really hope that Microsoft can help out with this, cause this is a
> > _serious_ problem.
> > SQL server just terminates!
> >
> > Do you have any new information?
> >
> > "JKKUS" wrote:
> >
> > > Over the past couple of days we have seen the following message in the SQL
> > > Server Logs:
> > >
> > > "SqlSecurityHandler is invoked. Potential buffer overrun detected - server
> > > is terminating."
> > >
> > > At this point, the SQL Server and SQL Server Agent will terminate and have
> > > to be restarted. We are running SQL2000(SP4) on Windows Server '03 (no SP).
> > > Has anyone else seen this?
> > >
> > >

Buffer Overrun reported in SQLDiag

I have applied SQL SP4 to a SQL Server running on a Windows 2000 Cluster
(Active/ Passive), and have started to get Buffer Overrun errors on running
SQLDiag - as follows :
/********************************
Event ID:26
User:N/A
Description:
Application popup: Microsoft Visual C++ Runtime Library : Buffer overrun
detected!
Program: C:\PROGRA~1\MICROS~3\MSSQL\binn\SQLDiag.exe
A buffer overrun has been detected which has corrupted the program's
internal state. The program cannot safely continue execution and must
now be terminated.
/********************************
SQLDiag is being run by an SQL job running an OS command as follows :
SQLDiag -O Z:\Outputsqldiag.txt -E -C
This was always successful prior to the installation of SP4. There seems to
be nothing about this on the web...
Any thoughts or comments gratefully received.
This is a known bug... I don't think there is anything you can do about it
until it is fixed, but it has been reported...
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
"BarryC" <BarryC@.discussions.microsoft.com> wrote in message
news:45D5EBF1-0642-46D9-A3A0-696FBC1C2650@.microsoft.com...
>I have applied SQL SP4 to a SQL Server running on a Windows 2000 Cluster
> (Active/ Passive), and have started to get Buffer Overrun errors on
> running
> SQLDiag - as follows :
> /********************************
> Event ID: 26
> User: N/A
> Description:
> Application popup: Microsoft Visual C++ Runtime Library : Buffer overrun
> detected!
> Program: C:\PROGRA~1\MICROS~3\MSSQL\binn\SQLDiag.exe
> A buffer overrun has been detected which has corrupted the program's
> internal state. The program cannot safely continue execution and must
> now be terminated.
> /********************************
> SQLDiag is being run by an SQL job running an OS command as follows :
> SQLDiag -O Z:\Outputsqldiag.txt -E -C
> This was always successful prior to the installation of SP4. There seems
> to
> be nothing about this on the web...
> Any thoughts or comments gratefully received.
|||SQLDiag.exe upgraded by SP4 has this Buffer overrun issue. Follow this work
around for an immediate solution
Rename the existing SQLDiag.exe (SP4)
Copy the SQLDiag.exe from the SQL Server Setup CD (Version 2000.80.194.0) to
the Binn folder
This old version SQLDiag works fine with the SP4. I tested this solution
and it works fine.
"BarryC" wrote:

> I have applied SQL SP4 to a SQL Server running on a Windows 2000 Cluster
> (Active/ Passive), and have started to get Buffer Overrun errors on running
> SQLDiag - as follows :
> /********************************
> Event ID:26
> User:N/A
> Description:
> Application popup: Microsoft Visual C++ Runtime Library : Buffer overrun
> detected!
> Program: C:\PROGRA~1\MICROS~3\MSSQL\binn\SQLDiag.exe
> A buffer overrun has been detected which has corrupted the program's
> internal state. The program cannot safely continue execution and must
> now be terminated.
> /********************************
> SQLDiag is being run by an SQL job running an OS command as follows :
> SQLDiag -O Z:\Outputsqldiag.txt -E -C
> This was always successful prior to the installation of SP4. There seems to
> be nothing about this on the web...
> Any thoughts or comments gratefully received.
|||Thanks for that - in testing at the moment, but its looking good. Have MS
got an official line on this one yet?
"M. S. Reddy" wrote:
[vbcol=seagreen]
> SQLDiag.exe upgraded by SP4 has this Buffer overrun issue. Follow this work
> around for an immediate solution
> Rename the existing SQLDiag.exe (SP4)
> Copy the SQLDiag.exe from the SQL Server Setup CD (Version 2000.80.194.0) to
> the Binn folder
> This old version SQLDiag works fine with the SP4. I tested this solution
> and it works fine.
>
>
>
> "BarryC" wrote:

Buffer Overrun reported in SQLDiag

I have applied SQL SP4 to a SQL Server running on a Windows 2000 Cluster
(Active/ Passive), and have started to get Buffer Overrun errors on running
SQLDiag - as follows :
/********************************
Event ID: 26
User: N/A
Description:
Application popup: Microsoft Visual C++ Runtime Library : Buffer overrun
detected!
Program: C:\PROGRA~1\MICROS~3\MSSQL\binn\SQLDiag.exe
A buffer overrun has been detected which has corrupted the program's
internal state. The program cannot safely continue execution and must
now be terminated.
/********************************
SQLDiag is being run by an SQL job running an OS command as follows :
SQLDiag -O Z:\Outputsqldiag.txt -E -C
This was always successful prior to the installation of SP4. There seems to
be nothing about this on the web...
Any thoughts or comments gratefully received.This is a known bug... I don't think there is anything you can do about it
until it is fixed, but it has been reported...
--
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
"BarryC" <BarryC@.discussions.microsoft.com> wrote in message
news:45D5EBF1-0642-46D9-A3A0-696FBC1C2650@.microsoft.com...
>I have applied SQL SP4 to a SQL Server running on a Windows 2000 Cluster
> (Active/ Passive), and have started to get Buffer Overrun errors on
> running
> SQLDiag - as follows :
> /********************************
> Event ID: 26
> User: N/A
> Description:
> Application popup: Microsoft Visual C++ Runtime Library : Buffer overrun
> detected!
> Program: C:\PROGRA~1\MICROS~3\MSSQL\binn\SQLDiag.exe
> A buffer overrun has been detected which has corrupted the program's
> internal state. The program cannot safely continue execution and must
> now be terminated.
> /********************************
> SQLDiag is being run by an SQL job running an OS command as follows :
> SQLDiag -O Z:\Outputsqldiag.txt -E -C
> This was always successful prior to the installation of SP4. There seems
> to
> be nothing about this on the web...
> Any thoughts or comments gratefully received.|||SQLDiag.exe upgraded by SP4 has this Buffer overrun issue. Follow this work
around for an immediate solution
Rename the existing SQLDiag.exe (SP4)
Copy the SQLDiag.exe from the SQL Server Setup CD (Version 2000.80.194.0) to
the Binn folder
This old version SQLDiag works fine with the SP4. I tested this solution
and it works fine.
"BarryC" wrote:
> I have applied SQL SP4 to a SQL Server running on a Windows 2000 Cluster
> (Active/ Passive), and have started to get Buffer Overrun errors on running
> SQLDiag - as follows :
> /********************************
> Event ID: 26
> User: N/A
> Description:
> Application popup: Microsoft Visual C++ Runtime Library : Buffer overrun
> detected!
> Program: C:\PROGRA~1\MICROS~3\MSSQL\binn\SQLDiag.exe
> A buffer overrun has been detected which has corrupted the program's
> internal state. The program cannot safely continue execution and must
> now be terminated.
> /********************************
> SQLDiag is being run by an SQL job running an OS command as follows :
> SQLDiag -O Z:\Outputsqldiag.txt -E -C
> This was always successful prior to the installation of SP4. There seems to
> be nothing about this on the web...
> Any thoughts or comments gratefully received.|||Thanks for that - in testing at the moment, but its looking good. Have MS
got an official line on this one yet?
"M. S. Reddy" wrote:
> SQLDiag.exe upgraded by SP4 has this Buffer overrun issue. Follow this work
> around for an immediate solution
> Rename the existing SQLDiag.exe (SP4)
> Copy the SQLDiag.exe from the SQL Server Setup CD (Version 2000.80.194.0) to
> the Binn folder
> This old version SQLDiag works fine with the SP4. I tested this solution
> and it works fine.
>
>
>
> "BarryC" wrote:
> > I have applied SQL SP4 to a SQL Server running on a Windows 2000 Cluster
> > (Active/ Passive), and have started to get Buffer Overrun errors on running
> > SQLDiag - as follows :
> >
> > /********************************
> > Event ID: 26
> > User: N/A
> > Description:
> > Application popup: Microsoft Visual C++ Runtime Library : Buffer overrun
> > detected!
> >
> > Program: C:\PROGRA~1\MICROS~3\MSSQL\binn\SQLDiag.exe
> >
> > A buffer overrun has been detected which has corrupted the program's
> > internal state. The program cannot safely continue execution and must
> > now be terminated.
> > /********************************
> >
> > SQLDiag is being run by an SQL job running an OS command as follows :
> >
> > SQLDiag -O Z:\Outputsqldiag.txt -E -C
> >
> > This was always successful prior to the installation of SP4. There seems to
> > be nothing about this on the web...
> >
> > Any thoughts or comments gratefully received.

Buffer Overrun reported in SQLDiag

I have applied SQL SP4 to a SQL Server running on a Windows 2000 Cluster
(Active/ Passive), and have started to get Buffer Overrun errors on running
SQLDiag - as follows :
/********************************
Event ID: 26
User: N/A
Description:
Application popup: Microsoft Visual C++ Runtime Library : Buffer overrun
detected!
Program: C:\PROGRA~1\MICROS~3\MSSQL\binn\SQLDiag.exe
A buffer overrun has been detected which has corrupted the program's
internal state. The program cannot safely continue execution and must
now be terminated.
/********************************
SQLDiag is being run by an SQL job running an OS command as follows :
SQLDiag -O Z:\Outputsqldiag.txt -E -C
This was always successful prior to the installation of SP4. There seems to
be nothing about this on the web...
Any thoughts or comments gratefully received.This is a known bug... I don't think there is anything you can do about it
until it is fixed, but it has been reported...
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
"BarryC" <BarryC@.discussions.microsoft.com> wrote in message
news:45D5EBF1-0642-46D9-A3A0-696FBC1C2650@.microsoft.com...
>I have applied SQL SP4 to a SQL Server running on a Windows 2000 Cluster
> (Active/ Passive), and have started to get Buffer Overrun errors on
> running
> SQLDiag - as follows :
> /********************************
> Event ID: 26
> User: N/A
> Description:
> Application popup: Microsoft Visual C++ Runtime Library : Buffer overrun
> detected!
> Program: C:\PROGRA~1\MICROS~3\MSSQL\binn\SQLDiag.exe
> A buffer overrun has been detected which has corrupted the program's
> internal state. The program cannot safely continue execution and must
> now be terminated.
> /********************************
> SQLDiag is being run by an SQL job running an OS command as follows :
> SQLDiag -O Z:\Outputsqldiag.txt -E -C
> This was always successful prior to the installation of SP4. There seems
> to
> be nothing about this on the web...
> Any thoughts or comments gratefully received.|||SQLDiag.exe upgraded by SP4 has this Buffer overrun issue. Follow this work
around for an immediate solution
Rename the existing SQLDiag.exe (SP4)
Copy the SQLDiag.exe from the SQL Server Setup CD (Version 2000.80.194.0) to
the Binn folder
This old version SQLDiag works fine with the SP4. I tested this solution
and it works fine.
"BarryC" wrote:

> I have applied SQL SP4 to a SQL Server running on a Windows 2000 Cluster
> (Active/ Passive), and have started to get Buffer Overrun errors on runnin
g
> SQLDiag - as follows :
> /********************************
> Event ID: 26
> User: N/A
> Description:
> Application popup: Microsoft Visual C++ Runtime Library : Buffer overrun
> detected!
> Program: C:\PROGRA~1\MICROS~3\MSSQL\binn\SQLDiag.exe
> A buffer overrun has been detected which has corrupted the program's
> internal state. The program cannot safely continue execution and must
> now be terminated.
> /********************************
> SQLDiag is being run by an SQL job running an OS command as follows :
> SQLDiag -O Z:\Outputsqldiag.txt -E -C
> This was always successful prior to the installation of SP4. There seems
to
> be nothing about this on the web...
> Any thoughts or comments gratefully received.|||Thanks for that - in testing at the moment, but its looking good. Have MS
got an official line on this one yet?
"M. S. Reddy" wrote:
[vbcol=seagreen]
> SQLDiag.exe upgraded by SP4 has this Buffer overrun issue. Follow this wo
rk
> around for an immediate solution
> Rename the existing SQLDiag.exe (SP4)
> Copy the SQLDiag.exe from the SQL Server Setup CD (Version 2000.80.194.0)
to
> the Binn folder
> This old version SQLDiag works fine with the SP4. I tested this solution
> and it works fine.
>
>
>
> "BarryC" wrote:
>

Buffer overrun detected

When I run SQL SP4 in a SQL Server (SQL SP3) running on a Windows 2000
Cluster (Active/Active), I receive the next message:
Buffer Overrun detected!
A buffer overrun has been detected which has corrupted the programs internal
state. The program cannot safely continue execution and must now be
terminated.
In a cluster I have two instances. The first instance installed perfect but
in second instance have a problem describe above.
Thank youHi, we've just come across this too. Has anyone got a solution?
BruceB
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message1757341.html|||Hi
You may want to call PSS on this one.
John
"Mario Felix Filho" <Mario Felix Filho@.discussions.microsoft.com> wrote in
message news:DA97BB4C-309E-4CDF-A1F2-A2B4851D264F@.microsoft.com...
> When I run SQL SP4 in a SQL Server (SQL SP3) running on a Windows 2000
> Cluster (Active/Active), I receive the next message:
> Buffer Overrun detected!
> A buffer overrun has been detected which has corrupted the programs
> internal
> state. The program cannot safely continue execution and must now be
> terminated.
> In a cluster I have two instances. The first instance installed perfect
> but
> in second instance have a problem describe above.
> Thank you
>

Buffer overrun detected

When I run SQL SP4 in a SQL Server (SQL SP3) running on a Windows 2000
Cluster (Active/Active), I receive the next message:
Buffer Overrun detected!
A buffer overrun has been detected which has corrupted the programs internal
state. The program cannot safely continue execution and must now be
terminated.
In a cluster I have two instances. The first instance installed perfect but
in second instance have a problem describe above.
Thank you
Hi, we've just come across this too. Has anyone got a solution?|||Hi
You may want to call PSS on this one.
John
"Mario Felix Filho" <Mario Felix Filho@.discussions.microsoft.com> wrote in
message news:DA97BB4C-309E-4CDF-A1F2-A2B4851D264F@.microsoft.com...
> When I run SQL SP4 in a SQL Server (SQL SP3) running on a Windows 2000
> Cluster (Active/Active), I receive the next message:
> Buffer Overrun detected!
> A buffer overrun has been detected which has corrupted the programs
> internal
> state. The program cannot safely continue execution and must now be
> terminated.
> In a cluster I have two instances. The first instance installed perfect
> but
> in second instance have a problem describe above.
> Thank you
>

Buffer overrun detected

When I run SQL SP4 in a SQL Server (SQL SP3) running on a Windows 2000
Cluster (Active/Active), I receive the next message:
Buffer Overrun detected!
A buffer overrun has been detected which has corrupted the programs internal
state. The program cannot safely continue execution and must now be
terminated.
In a cluster I have two instances. The first instance installed perfect but
in second instance have a problem describe above.
Thank youHi
You may want to call PSS on this one.
John
"Mario Felix Filho" <Mario Felix Filho@.discussions.microsoft.com> wrote in
message news:DA97BB4C-309E-4CDF-A1F2-A2B4851D264F@.microsoft.com...
> When I run SQL SP4 in a SQL Server (SQL SP3) running on a Windows 2000
> Cluster (Active/Active), I receive the next message:
> Buffer Overrun detected!
> A buffer overrun has been detected which has corrupted the programs
> internal
> state. The program cannot safely continue execution and must now be
> terminated.
> In a cluster I have two instances. The first instance installed perfect
> but
> in second instance have a problem describe above.
> Thank you
>

Buffer overflow exception in SSIS

I am running a SSIS package which inserts records in 8 tables. After inserting about 280 records I get an error "Buffer overflow". Any help is greatly appreciated.

We'll need more info. What are the sources and destinations? What providers are being used? What transformation objects does the package use? What OS platform are you running on? etc.

Thanks

Donald Farmer

Buffer Manager on SQl server

I am monitoring my SQL Server. If the server has 2gig of
physical ram and SQL server memory is 1.6G and the SQL
Servver Buffer Manager cache hit ratio is 99, is that
good? If not, what configuration can be done? The server
is very slow at themoment. Thank you.
Jasmine>--Original Message--
>I am monitoring my SQL Server. If the server has 2gig of
>physical ram and SQL server memory is 1.6G and the SQL
>Servver Buffer Manager cache hit ratio is 99, is that
>good? If not, what configuration can be done? The
server
>is very slow at the moment. Thank you.
>Jasmine
Friends,
I just thought I should post this from sp_configure so you
can see how the memory is configured for sql server. It
is set to dynamically configure sql server memory - but
when I run performance monitoring it shows that sql server
consumes too much memory. Please take a look and let me
know if I need to reduce the amount of sql memory or some
key buffer and sort buffer configurations? Thank you very
much.
minimum maximum configure_value run_value
max degree of parallelism 0 32 0 0
max server memory (MB) 4 2147483647 2147483647
2147483647
max text repl size (B) 0 2147483647 65536
65536
max worker threads 32 32767 255 255
media retention 0 365 0 0
min memory per query (KB) 512 2147483647
1024 1024
min server memory (MB) 0 2147483647 0 0
>|||Those numbers are pretty good. SQL is using as much memory as is available
(2GB minus some for OS, overhead, etc. 1.6 is about right). 99% Buffer
cache hit ratio is also good. Until that gets under 95% or so, adding
memory likely won't give a noticable performance boost.
It looks like memory isn't your performance problem. Here is a good
starting point for finding out why your server isn't responding as well as
you need it to.
HOW TO: Troubleshoot Application Performance Issues
http://support.microsoft.com/default.aspx?scid=kb;en-us;298475&Product=sql2k
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jasmine Quinlan" <anonymous@.discussions.microsoft.com> wrote in message
news:f64701c43dd2$ff0f0f20$a001280a@.phx.gbl...
> I am monitoring my SQL Server. If the server has 2gig of
> physical ram and SQL server memory is 1.6G and the SQL
> Servver Buffer Manager cache hit ratio is 99, is that
> good? If not, what configuration can be done? The server
> is very slow at themoment. Thank you.
> Jasmine
>|||Geoff,
Thank you so much for your help. I appreciate that.
Jasmine
>--Original Message--
>Those numbers are pretty good. SQL is using as much
memory as is available
>(2GB minus some for OS, overhead, etc. 1.6 is about
right). 99% Buffer
>cache hit ratio is also good. Until that gets under 95%
or so, adding
>memory likely won't give a noticable performance boost.
>It looks like memory isn't your performance problem.
Here is a good
>starting point for finding out why your server isn't
responding as well as
>you need it to.
>HOW TO: Troubleshoot Application Performance Issues
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;298475&Product=sql2k
>
>--
>Geoff N. Hiten
>Microsoft SQL Server MVP
>Senior Database Administrator
>Careerbuilder.com
>I support the Professional Association for SQL Server
>www.sqlpass.org
>"Jasmine Quinlan" <anonymous@.discussions.microsoft.com>
wrote in message
>news:f64701c43dd2$ff0f0f20$a001280a@.phx.gbl...
>> I am monitoring my SQL Server. If the server has 2gig
of
>> physical ram and SQL server memory is 1.6G and the SQL
>> Servver Buffer Manager cache hit ratio is 99, is that
>> good? If not, what configuration can be done? The
server
>> is very slow at themoment. Thank you.
>> Jasmine
>
>.
>

Buffer Manager on SQl server

I am monitoring my SQL Server. If the server has 2gig of
physical ram and SQL server memory is 1.6G and the SQL
Servver Buffer Manager cache hit ratio is 99, is that
good? If not, what configuration can be done? The server
is very slow at themoment. Thank you.
Jasmine
Those numbers are pretty good. SQL is using as much memory as is available
(2GB minus some for OS, overhead, etc. 1.6 is about right). 99% Buffer
cache hit ratio is also good. Until that gets under 95% or so, adding
memory likely won't give a noticable performance boost.
It looks like memory isn't your performance problem. Here is a good
starting point for finding out why your server isn't responding as well as
you need it to.
HOW TO: Troubleshoot Application Performance Issues
http://support.microsoft.com/default...&Product=sql2k
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jasmine Quinlan" <anonymous@.discussions.microsoft.com> wrote in message
news:f64701c43dd2$ff0f0f20$a001280a@.phx.gbl...
> I am monitoring my SQL Server. If the server has 2gig of
> physical ram and SQL server memory is 1.6G and the SQL
> Servver Buffer Manager cache hit ratio is 99, is that
> good? If not, what configuration can be done? The server
> is very slow at themoment. Thank you.
> Jasmine
>
|||Geoff,
Thank you so much for your help. I appreciate that.
Jasmine

>--Original Message--
>Those numbers are pretty good. SQL is using as much
memory as is available
>(2GB minus some for OS, overhead, etc. 1.6 is about
right). 99% Buffer
>cache hit ratio is also good. Until that gets under 95%
or so, adding
>memory likely won't give a noticable performance boost.
>It looks like memory isn't your performance problem.
Here is a good
>starting point for finding out why your server isn't
responding as well as
>you need it to.
>HOW TO: Troubleshoot Application Performance Issues
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;298475&Product=sql2k
>
>--
>Geoff N. Hiten
>Microsoft SQL Server MVP
>Senior Database Administrator
>Careerbuilder.com
>I support the Professional Association for SQL Server
>www.sqlpass.org
>"Jasmine Quinlan" <anonymous@.discussions.microsoft.com>
wrote in message[vbcol=seagreen]
>news:f64701c43dd2$ff0f0f20$a001280a@.phx.gbl...
of[vbcol=seagreen]
server
>
>.
>

Buffer Manager on SQl server

I am monitoring my SQL Server. If the server has 2gig of
physical ram and SQL server memory is 1.6G and the SQL
Servver Buffer Manager cache hit ratio is 99, is that
good? If not, what configuration can be done? The server
is very slow at themoment. Thank you.
JasmineThose numbers are pretty good. SQL is using as much memory as is available
(2GB minus some for OS, overhead, etc. 1.6 is about right). 99% Buffer
cache hit ratio is also good. Until that gets under 95% or so, adding
memory likely won't give a noticable performance boost.
It looks like memory isn't your performance problem. Here is a good
starting point for finding out why your server isn't responding as well as
you need it to.
HOW TO: Troubleshoot Application Performance Issues
http://support.microsoft.com/defaul...5&Product=sql2k
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jasmine Quinlan" <anonymous@.discussions.microsoft.com> wrote in message
news:f64701c43dd2$ff0f0f20$a001280a@.phx.gbl...
> I am monitoring my SQL Server. If the server has 2gig of
> physical ram and SQL server memory is 1.6G and the SQL
> Servver Buffer Manager cache hit ratio is 99, is that
> good? If not, what configuration can be done? The server
> is very slow at themoment. Thank you.
> Jasmine
>|||Geoff,
Thank you so much for your help. I appreciate that.
Jasmine

>--Original Message--
>Those numbers are pretty good. SQL is using as much
memory as is available
>(2GB minus some for OS, overhead, etc. 1.6 is about
right). 99% Buffer
>cache hit ratio is also good. Until that gets under 95%
or so, adding
>memory likely won't give a noticable performance boost.
>It looks like memory isn't your performance problem.
Here is a good
>starting point for finding out why your server isn't
responding as well as
>you need it to.
>HOW TO: Troubleshoot Application Performance Issues
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;298475&Product=sql2k
>
>--
>Geoff N. Hiten
>Microsoft SQL Server MVP
>Senior Database Administrator
>Careerbuilder.com
>I support the Professional Association for SQL Server
>www.sqlpass.org
>"Jasmine Quinlan" <anonymous@.discussions.microsoft.com>
wrote in message
>news:f64701c43dd2$ff0f0f20$a001280a@.phx.gbl...
of[vbcol=seagreen]
server[vbcol=seagreen]
>
>.
>

Buffer manager

Hi,
Could there be anything wrong with Buffer Manager\Page
reads/writes/sec spikes?
Many thanks,
Oskar
Oscar
Not enough memory for SQL Server
"Oskar" <anonymous@.discussions.microsoft.com> wrote in message
news:314c01c4b05c$99325930$a301280a@.phx.gbl...
> Hi,
> Could there be anything wrong with Buffer Manager\Page
> reads/writes/sec spikes?
> --
> Many thanks,
> Oskar
>

Buffer manager

Hi,
Could there be anything wrong with Buffer Manager\Page
reads/writes/sec spikes?
Many thanks,
OskarOscar
Not enough memory for SQL Server
"Oskar" <anonymous@.discussions.microsoft.com> wrote in message
news:314c01c4b05c$99325930$a301280a@.phx.gbl...
> Hi,
> Could there be anything wrong with Buffer Manager\Page
> reads/writes/sec spikes?
> --
> Many thanks,
> Oskar
>

Buffer manager

Hi,
Could there be anything wrong with Buffer Manager\Page
reads/writes/sec spikes?
--
Many thanks,
OskarOscar
Not enough memory for SQL Server
"Oskar" <anonymous@.discussions.microsoft.com> wrote in message
news:314c01c4b05c$99325930$a301280a@.phx.gbl...
> Hi,
> Could there be anything wrong with Buffer Manager\Page
> reads/writes/sec spikes?
> --
> Many thanks,
> Oskar
>

Buffer Leaks

I've had this a few times.

Can someone explain what a buffer leak is, and how are they avoided.

I downloaded the ExtraSort demo and in non-debug mode I get loads of errors saying the buffer leaked?

How do I plug the whole? Do I need a big cork?

Simon

Well there are 2 leak messages the buffer manager gives and since you didn't specify the exact message I don't know which one you are getting and they happen in very different scenarios. I will explain them both but they are both a component coding error.

1. This buffer has been orphaned...
This is when the engine is shutting down but a component still has a reference to a buffer. This usually mean the component has actually leaked a buffer since when the engine is shutting down everything else in the dataflow has already finished.

2. A call to ProcessInput unexpectedly kept a reference...
This is when a component calls AddRef on a buffer that it was passed in a process input call. This is not allowed. A component that needs to keep a buffer around that it was passed on process input must call clone on the buffer not addref.

You can't do anything about this problem. The component author must fix their component to resolve these problems.

HTH,
Matt

buffer latch?

The following error got posted in the error log:
Could not open FCB for invalid file ID 21808 in
database 'USAAREP'. Table or database may be corrupted..
It was followed by series of messages:
Time out occurred while waiting for buffer latch type 3,
bp 0x147cc500, page (21808:808465440), stat 0x40d, object
ID 8:-271269168:0, waittime 500. Continuing to wait.
At this point we cannot do any backups using SQLMAINT or
BACKUP neither through EM nor QA.
Anybody heard of how to fix this? The server is 7.0 SP4.
TIA,
RobertRobert
I had a problem like this, it was fun to fix as I could
find very little info anywhere. I was using SQL 7 and it
hangs the whole database and you need to stop and start
SQL Server to fix it.
You need to identify the process that is causing the
problem, the buffer latch error message should point you
at the object. Try to work out what processes where
running against that object shortly before the problem
occured.
What was happening in my case was that we had a stored
procedure that updated a table, but due to a design fault
in the database, a small amount of duplicate records were
getting inserted to the table. At the end of the stored
procedure it called another stored procedure to delete the
duplicate records. At some time during this process an
automatic checkpoint kicked in. A combination of updated
records not yet physically written to the database, a
checkpoint trying to write them and another process trying
to delete records that had not yet been written, seemed to
cause the problem.
In the short term I changed the job to have three steps,
do the updates, perform a chackpoint and then the deletes.
For the longterm fix, I rewrote the update process to not
insert duplicates.
You possibilly have something similar going on. Out of
interest do you have a checkpoint process trying to run,
but unable to finish?
Hope this helps
Regards
John

Buffer Latch error

I have been having the following time out error message on
my production server for a while now.
Waiting for type 0x4, current count 0xa, current owning EC
0x5E0B63C8.
Time out occurred while waiting for buffer latch type 4,bp
0x1473080, page 1:23), stat 0xb, object ID 7:3:0, EC
0x6ACBB9E0 : 0, waittime 600. Continuing to wait.
The is an sms server that runs SQL2000 sp3 on Windows 2000
sp4. The microsoft suggestion is to apply sp3 - which I
already did when the server was built. Has anyone come
accross this problem and if so, how did you fix? Is
reapplying service pack a good thing to do? Thanks:I've seen this.
MS will probably disagree. But personally I feel that this is a horribly
handled error and perhaps a bug. You don't provide the specific error number
but assuming it's the same thing I've seen on numerous occaisions...
this error often points to a) a server with inadequte IO capacity and/or b)
queries that are inneffecient for one reason or another that are
exacerrbating the IO issue.
Now... I'll accept that a hardware platform and/or query might be slow...
but I do NOT like the fact that the query simply times out. I'd rather let
it run and have warning messages written to the log that indicate a problem
is happening on this spid. Just my two cents...
but anyway... you should probably be looking at IO issues at the server and
query level.
Of course it could be something completely different. There's not enough
info in your mail to know for sure...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"June Spearman" <anonymous@.discussions.microsoft.com> wrote in message
news:0b2a01c3db7c$f0f4d270$a501280a@.phx.gbl...

quote:

---

> I have been having the following time out error message on
> my production server for a while now.
> Waiting for type 0x4, current count 0xa, current owning EC
> 0x5E0B63C8.
> Time out occurred while waiting for buffer latch type 4,bp
> 0x1473080, page 1:23), stat 0xb, object ID 7:3:0, EC
> 0x6ACBB9E0 : 0, waittime 600. Continuing to wait.
>
> The is an sms server that runs SQL2000 sp3 on Windows 2000
> sp4. The microsoft suggestion is to apply sp3 - which I
> already did when the server was built. Has anyone come
> accross this problem and if so, how did you fix? Is
> reapplying service pack a good thing to do? Thanks:
>
>

---

|||The server runs SMS and every so often during the day it
would run querries to find out what new computers are out
there. The application and server don't seem to have any
problem except for the fact that it generates this error
message. The timeout occurs sometimes during a backup and
that causes the job to fail. What more information can I
give you? How can an IO problem be resolved or how can we
determine if it is a query, memory or hardware?
June

quote:

---

>--Original Message--
>I've seen this.
>MS will probably disagree. But personally I feel that

---

this is a horribly

quote:

---

>handled error and perhaps a bug. You don't provide the

---

specific error number

quote:

---

>but assuming it's the same thing I've seen on numerous

---

occaisions...

quote:

---

>this error often points to a) a server with inadequte IO

---

capacity and/or b)

quote:

---

>queries that are inneffecient for one reason or another

---

that are

quote:

---

>exacerrbating the IO issue.
>Now... I'll accept that a hardware platform and/or query

---

might be slow...

quote:

---

>but I do NOT like the fact that the query simply times

---

out. I'd rather let

quote:

---

>it run and have warning messages written to the log that

---

indicate a problem

quote:

---

>is happening on this spid. Just my two cents...
>but anyway... you should probably be looking at IO issues

---

at the server and

quote:

---

>query level.
>Of course it could be something completely different.

---

There's not enough

quote:

---

>info in your mail to know for sure...
>--
>Brian Moran
>Principal Mentor
>Solid Quality Learning
>SQL Server MVP
>http://www.solidqualitylearning.com
>
>"June Spearman" <anonymous@.discussions.microsoft.com>

---

wrote in message

quote:

---

>news:0b2a01c3db7c$f0f4d270$a501280a@.phx.gbl...
on[QUOTE]
EC[QUOTE]
4,bp[QUOTE]
2000[QUOTE]
>
>.
>

---

Buffer Latch error

I have a backup database job that failed with the following error on sql server 2005

Executed as user: TestUSer. Time-out occurred while waiting for buffer latch type 3 for page (1:10541989), database ID 8. [SQLSTATE 42000] (Error 845) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.

Does anyone know why this happened? Is it the server or the array?

Any help is appreciated.

Thanks

Check your database storage for hardware errors (disk failures, fibre connection, I/O errors, etc) This was a bug on SQL 2000 and 7.0, solved with a service pack. But i guess this is not the answer here. So, please check your equipment 1st.

Buffer Latch error

I have been having the following time out error message on
my production server for a while now.
Waiting for type 0x4, current count 0xa, current owning EC
0x5E0B63C8.
Time out occurred while waiting for buffer latch type 4,bp
0x1473080, page 1:23), stat 0xb, object ID 7:3:0, EC
0x6ACBB9E0 : 0, waittime 600. Continuing to wait.
The is an sms server that runs SQL2000 sp3 on Windows 2000
sp4. The microsoft suggestion is to apply sp3 - which I
already did when the server was built. Has anyone come
accross this problem and if so, how did you fix? Is
reapplying service pack a good thing to do? Thanks:I've seen this.
MS will probably disagree. But personally I feel that this is a horribly
handled error and perhaps a bug. You don't provide the specific error number
but assuming it's the same thing I've seen on numerous occaisions...
this error often points to a) a server with inadequte IO capacity and/or b)
queries that are inneffecient for one reason or another that are
exacerrbating the IO issue.
Now... I'll accept that a hardware platform and/or query might be slow...
but I do NOT like the fact that the query simply times out. I'd rather let
it run and have warning messages written to the log that indicate a problem
is happening on this spid. Just my two cents...
but anyway... you should probably be looking at IO issues at the server and
query level.
Of course it could be something completely different. There's not enough
info in your mail to know for sure...
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"June Spearman" <anonymous@.discussions.microsoft.com> wrote in message
news:0b2a01c3db7c$f0f4d270$a501280a@.phx.gbl...
> I have been having the following time out error message on
> my production server for a while now.
> Waiting for type 0x4, current count 0xa, current owning EC
> 0x5E0B63C8.
> Time out occurred while waiting for buffer latch type 4,bp
> 0x1473080, page 1:23), stat 0xb, object ID 7:3:0, EC
> 0x6ACBB9E0 : 0, waittime 600. Continuing to wait.
>
> The is an sms server that runs SQL2000 sp3 on Windows 2000
> sp4. The microsoft suggestion is to apply sp3 - which I
> already did when the server was built. Has anyone come
> accross this problem and if so, how did you fix? Is
> reapplying service pack a good thing to do? Thanks:
>
>|||The server runs SMS and every so often during the day it
would run querries to find out what new computers are out
there. The application and server don't seem to have any
problem except for the fact that it generates this error
message. The timeout occurs sometimes during a backup and
that causes the job to fail. What more information can I
give you? How can an IO problem be resolved or how can we
determine if it is a query, memory or hardware?
June
>--Original Message--
>I've seen this.
>MS will probably disagree. But personally I feel that
this is a horribly
>handled error and perhaps a bug. You don't provide the
specific error number
>but assuming it's the same thing I've seen on numerous
occaisions...
>this error often points to a) a server with inadequte IO
capacity and/or b)
>queries that are inneffecient for one reason or another
that are
>exacerrbating the IO issue.
>Now... I'll accept that a hardware platform and/or query
might be slow...
>but I do NOT like the fact that the query simply times
out. I'd rather let
>it run and have warning messages written to the log that
indicate a problem
>is happening on this spid. Just my two cents...
>but anyway... you should probably be looking at IO issues
at the server and
>query level.
>Of course it could be something completely different.
There's not enough
>info in your mail to know for sure...
>--
>Brian Moran
>Principal Mentor
>Solid Quality Learning
>SQL Server MVP
>http://www.solidqualitylearning.com
>
>"June Spearman" <anonymous@.discussions.microsoft.com>
wrote in message
>news:0b2a01c3db7c$f0f4d270$a501280a@.phx.gbl...
>> I have been having the following time out error message
on
>> my production server for a while now.
>> Waiting for type 0x4, current count 0xa, current owning
EC
>> 0x5E0B63C8.
>> Time out occurred while waiting for buffer latch type
4,bp
>> 0x1473080, page 1:23), stat 0xb, object ID 7:3:0, EC
>> 0x6ACBB9E0 : 0, waittime 600. Continuing to wait.
>>
>> The is an sms server that runs SQL2000 sp3 on Windows
2000
>> sp4. The microsoft suggestion is to apply sp3 - which I
>> already did when the server was built. Has anyone come
>> accross this problem and if so, how did you fix? Is
>> reapplying service pack a good thing to do? Thanks:
>>
>
>.
>

Buffer Exception

When running a package created on my local machine i get no errors at all but when i try to run the same package on the server i get an error specifying Microsoft.SqlServer.Dts.Pipeline.DoesNotFitBufferException: The value is too large to fit in the column data area of the buffer.

I have tried changing the defaultbuffersize of the data flow task but this makes no difference. I think that a buffer size for a particular column is being exceed but i cannot find anywhere to set this property.

Has anyone else struck this error?

What key things are different between your local development box and your server? Different locale settings, 32 to 64 bit perhaps?

Buffer Cache Hit Ration on a Reporting DB

Hi all.
First off, I'm a sys admin, not a DBA.
I'm trying to improve buffer cache hit ratio on a reporting database.
Database is approximately 130GB. Current server is a Proliant ML530 G2,
2-way 3.6Ghz, 8GB RAM. Multiple datafiles spread out across 4 separate
raid 5 arrays. Disk queuing is pretty good. Sometimes we hit a rough
day.
I've read over and over how BCHR needs to be above 90%, optimally
around 98-99%. On the live OLTP database, BCHR is fine with 8GB in the
system. On the reporting DB, it often drops to 80% or below.
My question is, should a reporting database have this large of a
difference in the hit ratio?
I'm working with our developer & DBA to improve the reports, but I also
need to make sure that the hardware is performing as good as it can.
I've got a DL585, 4-core Opteron in the wings with 16GB running Win2K3
x64. We're waiting to make sure SP4 is completely kosher with our
application before upgrading.
Thoughts, ideas? Much appreciated!
-AJ<ajohnson@.echecktrac.com> wrote in message
news:1144442893.581038.299300@.e56g2000cwe.googlegroups.com...
> Hi all.
> First off, I'm a sys admin, not a DBA.
> I'm trying to improve buffer cache hit ratio on a reporting database.
> Database is approximately 130GB. Current server is a Proliant ML530 G2,
> 2-way 3.6Ghz, 8GB RAM. Multiple datafiles spread out across 4 separate
> raid 5 arrays. Disk queuing is pretty good. Sometimes we hit a rough
> day.
> I've read over and over how BCHR needs to be above 90%, optimally
> around 98-99%. On the live OLTP database, BCHR is fine with 8GB in the
> system.
Unfortunatly BCHR on OLTP databases is an irrelevant metric. A high BCHR
often results from inefficient queries reading lots and lots of pages in the
cache. And the memory size/database size ratio of an OLTP system often
doesn't even permit a low cache hit ratio.

>On the reporting DB, it often drops to 80% or below.
> My question is, should a reporting database have this large of a
> difference in the hit ratio?
Yes, it will quite likely be lower. It's a matter of the ratio between the
cache (<8GB) and the data (130GB), and the predictablility of the queries
and acess paths. Better indexing can help improve performance, although
since indexing reduces total IO, not just physical IO, you might not see a
better BCHR.

> I'm working with our developer & DBA to improve the reports, but I also
> need to make sure that the hardware is performing as good as it can.
> I've got a DL585, 4-core Opteron in the wings with 16GB running Win2K3
> x64. We're waiting to make sure SP4 is completely kosher with our
> application before upgrading.
>
Going all the way to SQL 2005 64-bit will help alot more. You can use all
that memory much more efficiently, plus it's easier to monitor the
performance and find the expensive queries.
David

Buffer Cache Hit Ration on a Reporting DB

Hi all.
First off, I'm a sys admin, not a DBA.
I'm trying to improve buffer cache hit ratio on a reporting database.
Database is approximately 130GB. Current server is a Proliant ML530 G2,
2-way 3.6Ghz, 8GB RAM. Multiple datafiles spread out across 4 separate
raid 5 arrays. Disk queuing is pretty good. Sometimes we hit a rough
day.
I've read over and over how BCHR needs to be above 90%, optimally
around 98-99%. On the live OLTP database, BCHR is fine with 8GB in the
system. On the reporting DB, it often drops to 80% or below.
My question is, should a reporting database have this large of a
difference in the hit ratio?
I'm working with our developer & DBA to improve the reports, but I also
need to make sure that the hardware is performing as good as it can.
I've got a DL585, 4-core Opteron in the wings with 16GB running Win2K3
x64. We're waiting to make sure SP4 is completely kosher with our
application before upgrading.
Thoughts, ideas? Much appreciated!
-AJ<ajohnson@.echecktrac.com> wrote in message
news:1144442893.581038.299300@.e56g2000cwe.googlegroups.com...
> Hi all.
> First off, I'm a sys admin, not a DBA.
> I'm trying to improve buffer cache hit ratio on a reporting database.
> Database is approximately 130GB. Current server is a Proliant ML530 G2,
> 2-way 3.6Ghz, 8GB RAM. Multiple datafiles spread out across 4 separate
> raid 5 arrays. Disk queuing is pretty good. Sometimes we hit a rough
> day.
> I've read over and over how BCHR needs to be above 90%, optimally
> around 98-99%. On the live OLTP database, BCHR is fine with 8GB in the
> system.
Unfortunatly BCHR on OLTP databases is an irrelevant metric. A high BCHR
often results from inefficient queries reading lots and lots of pages in the
cache. And the memory size/database size ratio of an OLTP system often
doesn't even permit a low cache hit ratio.
>On the reporting DB, it often drops to 80% or below.
> My question is, should a reporting database have this large of a
> difference in the hit ratio?
Yes, it will quite likely be lower. It's a matter of the ratio between the
cache (<8GB) and the data (130GB), and the predictablility of the queries
and acess paths. Better indexing can help improve performance, although
since indexing reduces total IO, not just physical IO, you might not see a
better BCHR.
> I'm working with our developer & DBA to improve the reports, but I also
> need to make sure that the hardware is performing as good as it can.
> I've got a DL585, 4-core Opteron in the wings with 16GB running Win2K3
> x64. We're waiting to make sure SP4 is completely kosher with our
> application before upgrading.
>
Going all the way to SQL 2005 64-bit will help alot more. You can use all
that memory much more efficiently, plus it's easier to monitor the
performance and find the expensive queries.
David

buffer cache hit ratio...how to measure?

Does anyone know how to measure the buffer cache hit ratio? I have been reading a lot about it but can't find this measurement in Performance Monitor.
ThanksNevermind, I found the counter. For anyone else who may be interested, it is in the performance monitor under SQL Server: Buffer Manager.

Thanks

Buffer Cache Hit Ratio vs Memory Manager Cache Hit ratio

We have a box that has 8GB of RAM but the OS only lets us see 3GB. We are
trying ascertain whether or not upgrading to a OS that will see the full
monty will help or redploying the assets would be a better option
The Buffer Cache Hit Ratio - 99%
The Memory Manager Cache Hit Ratio = 77%
The Target and actual values for Total Memory and Buffer Pages are the same
The memory pages/sec swings wildly, generally below 1 it goes up into the
thousands.
What is the difference between the two Cache Hit Ratio objects
Note: Vendor supplied DB is horrendous in design.Hi
You would need Enterprise Edition of Windows and SQL Server if you want to
use all 8GB.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jeffrey K. Ericson" wrote:
> We have a box that has 8GB of RAM but the OS only lets us see 3GB. We are
> trying ascertain whether or not upgrading to a OS that will see the full
> monty will help or redploying the assets would be a better option
> The Buffer Cache Hit Ratio - 99%
> The Memory Manager Cache Hit Ratio = 77%
> The Target and actual values for Total Memory and Buffer Pages are the same
> The memory pages/sec swings wildly, generally below 1 it goes up into the
> thousands.
> What is the difference between the two Cache Hit Ratio objects
> Note: Vendor supplied DB is horrendous in design.|||Yes, we have EE of SQL running on 2k standard. We are contemplaing going to
a 2003 version that can support the 8GB.
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> You would need Enterprise Edition of Windows and SQL Server if you want to
> use all 8GB.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Jeffrey K. Ericson" wrote:
> > We have a box that has 8GB of RAM but the OS only lets us see 3GB. We are
> > trying ascertain whether or not upgrading to a OS that will see the full
> > monty will help or redploying the assets would be a better option
> >
> > The Buffer Cache Hit Ratio - 99%
> > The Memory Manager Cache Hit Ratio = 77%
> > The Target and actual values for Total Memory and Buffer Pages are the same
> > The memory pages/sec swings wildly, generally below 1 it goes up into the
> > thousands.
> >
> > What is the difference between the two Cache Hit Ratio objects
> > Note: Vendor supplied DB is horrendous in design.|||Those are good questions; however, you will find that using Win2K AS or
Win2K3 EE not much different, but although AWE will momentarily ease memory
pressures, the added headaches it introduces will make the upgrades
short-lived. By going that route, you could extend the life of your system
for a year or two at best, but then you will be quickly running into the
limitations of the 32-bit architecture.
Since the OS and SQL Server licenses can be redeployed, and upgraded to
their 64-bit counterparts without charge, you could go ahead and upgrade
immediately but consider 64-bit solutions long term, IA64, not that silly
wanna-be, x64, EMT64 garbage.
It kind of depends on the usage. I would look at the SQL Server:Databases,
Transactions per second for each database, or the _Total instance, and the
SQL Server:General Statistics, Batch Requests per second to get an idea of
how busy your box is.
As for the two Cach hit Ratios: the Buffer Manager actually has five (5)
seperate memory managers associated with it...that is, the Buffer Pool. The
Data Buffer, which is just one of the memory managers, stores and manages
the data page cache and is what this counter is referring to. Whenever data
is requested to satisfy a query, if the data pages supporting that request
is currently resident in memory, you have a Data Buffer Cache Hit, otherwise
it is a miss and Disk I/O must be performed to load the requested pages.
You want a high cache hit, anything below 90% and you would be pretty
sluggish. Even at 98%, you are 10 times as inefficient than at 99%.
Now, the other cached items are the procedure plans in the PROCCACHE. The
Cache Manager, Cache Hit Ratio measures this and, actually, has multiple
instances, the _Total and one for each type of execution object: Procedures,
Ad-Hoc, Execution Contexts, Triggers, Cursors, Misc. Normalized Trees, etc.,
etc., etc. You should look at all of the instances as well as the _Total,
which you are quoting a figure of 77%. The instances can clue you in on
whether you have a memory limitation issue or just bad coding.
Now, let this be known, if you go the AWE route, it will be mostly for
additional Data Page buffering, not the Proc Cache. Welcome to the
limitations of the 32-bit arcitecture. No matter what you do, you are
limited to the 2 GB/3 GB lower memory region for Proc Cache storage. Now,
it is possible to offload some of the Data Buffer to the AWE space thereby
leaving more lower memory region for a larger than currently allocated Proc
Cache, but once you go AWE, SQL Server looses some of its flexibility and
dynamicity. Chances are your Proc Cache will not be reallocated much. In
fact, whenever there are memory pressures, the proc cache is usually the
first thing that is flushed or paged out to the swap file.
So, long term, you have two choices: go multi-instanced, either on the same
server, or by installing multiple servers, or took a real good look at IA64,
64-bit platforms. Dell has two low-entry versions currently, their 3250 and
7250 series.
Spend some time with the DBCC MEMORYSTATUS and DBCC PROCCACHE. These will
give you a lot of insight into how your installations is partitioning it
various memory allocations.
INF: Using DBCC MEMORYSTATUS to Monitor SQL Server Memory Usage
http://support.microsoft.com/default.aspx?scid=kb;en-us;271624
Hope this helps.
Sincerely,
Anthony Thomas
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:CAB56205-326F-41A1-8E42-20985CBE4CFF@.microsoft.com...
We have a box that has 8GB of RAM but the OS only lets us see 3GB. We are
trying ascertain whether or not upgrading to a OS that will see the full
monty will help or redploying the assets would be a better option
The Buffer Cache Hit Ratio - 99%
The Memory Manager Cache Hit Ratio = 77%
The Target and actual values for Total Memory and Buffer Pages are the same
The memory pages/sec swings wildly, generally below 1 it goes up into the
thousands.
What is the difference between the two Cache Hit Ratio objects
Note: Vendor supplied DB is horrendous in design.|||Do u have the PAE switch and AWE switch enabled ?
If you have done the required configuration, I dont see how the OS is not
able to see
beyond 3GB.
Thanks,
Gopi
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:8FCCB4AB-E082-40AE-B508-EECCB6603033@.microsoft.com...
> Yes, we have EE of SQL running on 2k standard. We are contemplaing going
> to
> a 2003 version that can support the 8GB.
> "Mike Epprecht (SQL MVP)" wrote:
>> Hi
>> You would need Enterprise Edition of Windows and SQL Server if you want
>> to
>> use all 8GB.
>> Regards
>> --
>> Mike Epprecht, Microsoft SQL Server MVP
>> Zurich, Switzerland
>> MVP Program: http://www.microsoft.com/mvp
>> Blog: http://www.msmvps.com/epprecht/
>>
>> "Jeffrey K. Ericson" wrote:
>> > We have a box that has 8GB of RAM but the OS only lets us see 3GB. We
>> > are
>> > trying ascertain whether or not upgrading to a OS that will see the
>> > full
>> > monty will help or redploying the assets would be a better option
>> >
>> > The Buffer Cache Hit Ratio - 99%
>> > The Memory Manager Cache Hit Ratio = 77%
>> > The Target and actual values for Total Memory and Buffer Pages are the
>> > same
>> > The memory pages/sec swings wildly, generally below 1 it goes up into
>> > the
>> > thousands.
>> >
>> > What is the difference between the two Cache Hit Ratio objects
>> > Note: Vendor supplied DB is horrendous in design.|||Found this in another Post. Have you applied SP4 ?
See the link below for the KB article on this issue. Microsoft are still
working on the problem.
--
Nick Colebourn (MCDBA)
DBA
United Coop Ltd
"Dimitar Dimitrov" wrote:
> The configuration is:
> OS: Windows 2003 Enterprise Edition
> RAM: 6GB
> boot.ini switches : /fastdetect /PAE /3GB /USERVA=3030
> SQL 2000 Enterprise Edition SP3a (awe enabled; min server memory (MB) => max server memory (MB) = 5120)
> Before installing SP4 SQL server uses configured amount of memory
> (5120MB).
> After installing SP4 it doesn't utilize more than 3GB of memory the SQL
> Server Logs reports "Warning: unable to allocate 'min server memory' of
> 5120MB."
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:8FCCB4AB-E082-40AE-B508-EECCB6603033@.microsoft.com...
> Yes, we have EE of SQL running on 2k standard. We are contemplaing going
> to
> a 2003 version that can support the 8GB.
> "Mike Epprecht (SQL MVP)" wrote:
>> Hi
>> You would need Enterprise Edition of Windows and SQL Server if you want
>> to
>> use all 8GB.
>> Regards
>> --
>> Mike Epprecht, Microsoft SQL Server MVP
>> Zurich, Switzerland
>> MVP Program: http://www.microsoft.com/mvp
>> Blog: http://www.msmvps.com/epprecht/
>>
>> "Jeffrey K. Ericson" wrote:
>> > We have a box that has 8GB of RAM but the OS only lets us see 3GB. We
>> > are
>> > trying ascertain whether or not upgrading to a OS that will see the
>> > full
>> > monty will help or redploying the assets would be a better option
>> >
>> > The Buffer Cache Hit Ratio - 99%
>> > The Memory Manager Cache Hit Ratio = 77%
>> > The Target and actual values for Total Memory and Buffer Pages are the
>> > same
>> > The memory pages/sec swings wildly, generally below 1 it goes up into
>> > the
>> > thousands.
>> >
>> > What is the difference between the two Cache Hit Ratio objects
>> > Note: Vendor supplied DB is horrendous in design.|||"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:OzSLCxEaFHA.1152@.tk2msftngp13.phx.gbl...
> Those are good questions; however, you will find that using Win2K AS or
> Win2K3 EE not much different, but although AWE will momentarily ease
> memory
> pressures, the added headaches it introduces will make the upgrades
> short-lived.
What headaches do you mean? We're using AWE for a few years, and I have not
had any headaches because of that :) The only headache could be hardware
and/or drivers not compatible with AWE.
Best regards
Wojtek|||How many transactions per second and/or batch requests per second does your
installation produce?
Ours pushes nearly 1500 hundred of both/either quite often. This coupled
with extended proc, FOR XML, and LOB usage, constrains the 32-bit memory
space quite heavily.
We've been piloting a 64-bit system for a while now and are getting ready to
make the conversion. Dropping AWE altogether and going back to Dynamic
Memory management for all of the memory managers including the PROC CACHE
was probably the most compelling reason for the push to convert.
Glad to hear that you haven't begun to hit those limits yet and that it is
still working out for your installation(s).
Sincerely,
Anthony Thomas
"Wojtek Garwol" <garwol@.usunto.poczta.fm> wrote in message
news:d7qh4d$7v9$1@.nemesis.news.tpi.pl...
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:OzSLCxEaFHA.1152@.tk2msftngp13.phx.gbl...
> Those are good questions; however, you will find that using Win2K AS or
> Win2K3 EE not much different, but although AWE will momentarily ease
> memory
> pressures, the added headaches it introduces will make the upgrades
> short-lived.
What headaches do you mean? We're using AWE for a few years, and I have not
had any headaches because of that :) The only headache could be hardware
and/or drivers not compatible with AWE.
Best regards
Wojtek|||The profile of our system is very different indeed. We have quite a lot of
data, 200+GB, biggest table has about 80mln records, and it is mostly
static. In this scenario, AWE, /pae and /3gb seem to work just fine. But now
I do see what you mean. AFAIK, AWE memory can be used only by data cache, so
it's pretty much limited indeed. Thanks for the info!
Regards
Wojtek
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:%23Wl4GcIaFHA.2076@.TK2MSFTNGP15.phx.gbl...
> How many transactions per second and/or batch requests per second does
> your
> installation produce?
> Ours pushes nearly 1500 hundred of both/either quite often. This coupled
> with extended proc, FOR XML, and LOB usage, constrains the 32-bit memory
> space quite heavily.
> We've been piloting a 64-bit system for a while now and are getting ready
> to
> make the conversion. Dropping AWE altogether and going back to Dynamic
> Memory management for all of the memory managers including the PROC CACHE
> was probably the most compelling reason for the push to convert.
> Glad to hear that you haven't begun to hit those limits yet and that it is
> still working out for your installation(s).
> Sincerely,
>
> Anthony Thomas
>
> --
> "Wojtek Garwol" <garwol@.usunto.poczta.fm> wrote in message
> news:d7qh4d$7v9$1@.nemesis.news.tpi.pl...
> "Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
> news:OzSLCxEaFHA.1152@.tk2msftngp13.phx.gbl...
>> Those are good questions; however, you will find that using Win2K AS or
>> Win2K3 EE not much different, but although AWE will momentarily ease
>> memory
>> pressures, the added headaches it introduces will make the upgrades
>> short-lived.
> What headaches do you mean? We're using AWE for a few years, and I have
> not
> had any headaches because of that :) The only headache could be hardware
> and/or drivers not compatible with AWE.
> Best regards
> Wojtek
>