Extracting Sql: March 2012

Thursday, March 29, 2012

Failed to start jobs in sql agent after restart service

We have clustered sql2005 (sp2) installed in win2003 (sp2). Recently, we
encountered a problem which failed to start jobs in sql agent after restart
mssql server and aql agent services. It failed with exception error in the
job status dialogue.
There is no error message in event viewer and we found an error aqlagent.out
it shows
2007-07-28 02:08:11 - ! [165] ODBC Error: 0, Unable to complete login
process due to delay in opening server connection [SQLSTATE 08001]
Kindly advise and thanks in advanceCheck your service account (SQLAgent) and permissions related to this job.
Plus, check your cluster resources pertaining to SQL Agent service
"stephanie" <stephanie@.discussions.microsoft.com> wrote in message
news:754761F4-D5A9-460F-904A-BA0FC63CFBC6@.microsoft.com...
> We have clustered sql2005 (sp2) installed in win2003 (sp2). Recently, we
> encountered a problem which failed to start jobs in sql agent after
> restart
> mssql server and aql agent services. It failed with exception error in
> the
> job status dialogue.
>
> There is no error message in event viewer and we found an error
> aqlagent.out
> it shows
>
> 2007-07-28 02:08:11 - ! [165] ODBC Error: 0, Unable to complete login
> process due to delay in opening server connection [SQLSTATE 08001]
>
> Kindly advise and thanks in advance
>|||This problem only exist in node A. If failover to Node B , the agent works
normally.
the sql agent service a/c is an administrator. I compared the permission and
sql agent setting in cluster admin. Thery are the same
Kindly advise. Thanks a lot.
"bass_player [SBS-MVP]" wrote:

> Check your service account (SQLAgent) and permissions related to this job.
> Plus, check your cluster resources pertaining to SQL Agent service
>
> "stephanie" <stephanie@.discussions.microsoft.com> wrote in message
> news:754761F4-D5A9-460F-904A-BA0FC63CFBC6@.microsoft.com...
>
>

Failed to start jobs in sql agent after restart service

We have clustered sql2005 (sp2) installed in win2003 (sp2). Recently, we
encountered a problem which failed to start jobs in sql agent after restart
mssql server and aql agent services. It failed with exception error in the
job status dialogue.
There is no error message in event viewer and we found an error aqlagent.out
it shows
2007-07-28 02:08:11 - ! [165] ODBC Error: 0, Unable to complete login
process due to delay in opening server connection [SQLSTATE 08001]
Kindly advise and thanks in advanceCheck your service account (SQLAgent) and permissions related to this job.
Plus, check your cluster resources pertaining to SQL Agent service
"stephanie" <stephanie@.discussions.microsoft.com> wrote in message
news:754761F4-D5A9-460F-904A-BA0FC63CFBC6@.microsoft.com...
> We have clustered sql2005 (sp2) installed in win2003 (sp2). Recently, we
> encountered a problem which failed to start jobs in sql agent after
> restart
> mssql server and aql agent services. It failed with exception error in
> the
> job status dialogue.
>
> There is no error message in event viewer and we found an error
> aqlagent.out
> it shows
>
> 2007-07-28 02:08:11 - ! [165] ODBC Error: 0, Unable to complete login
> process due to delay in opening server connection [SQLSTATE 08001]
>
> Kindly advise and thanks in advance
>|||This problem only exist in node A. If failover to Node B , the agent works
normally.
the sql agent service a/c is an administrator. I compared the permission and
sql agent setting in cluster admin. Thery are the same
Kindly advise. Thanks a lot.
"bass_player [SBS-MVP]" wrote:
> Check your service account (SQLAgent) and permissions related to this job.
> Plus, check your cluster resources pertaining to SQL Agent service
>
> "stephanie" <stephanie@.discussions.microsoft.com> wrote in message
> news:754761F4-D5A9-460F-904A-BA0FC63CFBC6@.microsoft.com...
> > We have clustered sql2005 (sp2) installed in win2003 (sp2). Recently, we
> > encountered a problem which failed to start jobs in sql agent after
> > restart
> > mssql server and aql agent services. It failed with exception error in
> > the
> > job status dialogue.
> >
> >
> >
> > There is no error message in event viewer and we found an error
> > aqlagent.out
> >
> > it shows
> >
> >
> >
> > 2007-07-28 02:08:11 - ! [165] ODBC Error: 0, Unable to complete login
> > process due to delay in opening server connection [SQLSTATE 08001]
> >
> >
> >
> > Kindly advise and thanks in advance
> >
>
>

Failed to start a new trace - SQL PROFILER

SQL Profiler is running under Domain account and has
access to local folders... still it is throwing this error
message...
Another issue is double clicking the SQL Profiler Icon
does not start Profiler... I have to explicitly run it
under current user account...
Version MS SQL 2000/OS Windows XP Professional...
Any idea?Check to see if this is your issue.
295461 FIX: Profiler Fails To Start New Trace When User Environment Variable
http://support.microsoft.com/?id=295461
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||I have already verified it. Its not the issue.
I am not able to start SQL profiler as well as SQL Query
Analyzer by simply double clicking it. I have to
explicitly run it under current user account... Initially
I was not able to access any Profiler template files saved
under Desktop, then I granted rights to the logged in
Domain user... Now it is able to access the files but
could not start trace...
Hope this helps...

quote:

>--Original Message--
>Check to see if this is your issue.
>295461 FIX: Profiler Fails To Start New Trace When User

Environment Variable

quote:

>http://support.microsoft.com/?id=295461
>Thanks,
>Kevin McDonnell
>Microsoft Corporation
>This posting is provided AS IS with no warranties, and

confers no rights.

quote:

>
>.
>

|||previous post:
"I am not able to start SQL profiler as well as SQL Query
Analyzer by simply double clicking it. I have to
explicitly run it under current user account... Initially
I was not able to access any Profiler template files saved
under Desktop, then I granted rights to the logged in
Domain user... Now it is able to access the files but
could not start trace...
"
Please explain what account you're logging into the machine with.
What groups does this user belong to?
Are there any Group Policies loaded ?
Also, did you create new desktop shortcuts to Profiler and Query Analyser?
Do the programs run if you launch them from the menu as opposed to your
shortcuts?
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Please explain what account you're logging into the
machine with.
Active Directory Domain User
What groups does this user belong to?
Local Administrator
Domain Users
Debugger users
Are there any Group Policies loaded ?
Yes. I just refreshed it.
Also, did you create new desktop shortcuts to Profiler and
Query Analyser?
No. I was able to access it using short cut previously.
Do the programs run if you launch them from the menu as
opposed to your shortcuts?
No It doesn't run from anywhere. Not from Shortcut, Menu
and SQL Enterprise Manager/Tools.

quote:

>--Original Message--
>previous post:
>"I am not able to start SQL profiler as well as SQL Query
>Analyzer by simply double clicking it. I have to
>explicitly run it under current user account... Initially
>I was not able to access any Profiler template files

saved

quote:

>under Desktop, then I granted rights to the logged in
>Domain user... Now it is able to access the files but
>could not start trace...
>"
>Please explain what account you're logging into the

machine with.

quote:

>What groups does this user belong to?
>Are there any Group Policies loaded ?
>Also, did you create new desktop shortcuts to Profiler

and Query Analyser?

quote:

>Do the programs run if you launch them from the menu as

opposed to your

quote:

>shortcuts?
>
>Thanks,
>Kevin McDonnell
>Microsoft Corporation
>This posting is provided AS IS with no warranties, and

confers no rights.

quote:

>
>.
>

|||You may want to try running FileMon.exe from www.sysinternals.com to see
where the permission problem is.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Failed to set proper user name ('domain\username') for the connection

Im runnning a Trace from Production against a Development box and getting
this error message:
Failed to set proper user name ('domain\username') for the connection
.. I have removed the db_id from the Trace table. Also, all the Logins/ Users
are set to the proper default DB. So Im really confused as to why I would be
getting this? Any ideas?
TIA, ChrisR
Hi
Have you got the same db_id for the database(s) on the production and
development servers? You may want to restore master! Alternatively try to
save the trace as a SQL File and then run that, you should not get this
problem and it will be easier to debug any problems that you do get.
John
"ChrisR" <ChrisR@.noEmail.com> wrote in message
news:%23qqMGq7CFHA.4052@.TK2MSFTNGP15.phx.gbl...
> Im runnning a Trace from Production against a Development box and getting
> this error message:
> Failed to set proper user name ('domain\username') for the connection
> . I have removed the db_id from the Trace table. Also, all the Logins/
> Users
> are set to the proper default DB. So Im really confused as to why I would
> be
> getting this? Any ideas?
>
> TIA, ChrisR
>
|||John if Im not mistaken, the problem should have been fixed by simply
removing the db_id... therefore not requiring the restore of Master.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:e6jSfhHDFHA.2180@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Hi
> Have you got the same db_id for the database(s) on the production and
> development servers? You may want to restore master! Alternatively try to
> save the trace as a SQL File and then run that, you should not get this
> problem and it will be easier to debug any problems that you do get.
> John
> "ChrisR" <ChrisR@.noEmail.com> wrote in message
> news:%23qqMGq7CFHA.4052@.TK2MSFTNGP15.phx.gbl...
getting[vbcol=seagreen]
would
>
|||Hi
That is not what I understand! Profiling it may help.
John
ChrisR wrote:[vbcol=seagreen]
> John if Im not mistaken, the problem should have been fixed by simply
> removing the db_id... therefore not requiring the restore of Master.
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:e6jSfhHDFHA.2180@.TK2MSFTNGP10.phx.gbl...
and[vbcol=seagreen]
try to[vbcol=seagreen]
this[vbcol=seagreen]
get.[vbcol=seagreen]
> getting
connection[vbcol=seagreen]
Logins/[vbcol=seagreen]
I[vbcol=seagreen]
> would
|||It generally does work if you remove database ids from the
trace. Double check to make sure all the users captured in
the trace have a default database of the target database.
The commands should then be executed in the default database
so the database id isn't an issue.
-Sue
On 8 Feb 2005 00:01:01 -0800, "John Bell"
<jbellnewsposts@.hotmail.com> wrote:
[vbcol=seagreen]
>Hi
>That is not what I understand! Profiling it may help.
>John
>ChrisR wrote:
>and
>try to
>this
>get.
>connection
>Logins/
>I
|||Hi
Another through, although you say there default databases are set for
all users, check that there are no orphaned users.
John
John Bell wrote:[vbcol=seagreen]
> Hi
> That is not what I understand! Profiling it may help.
> John
> ChrisR wrote:
simply[vbcol=seagreen]
Master.[vbcol=seagreen]
> and
Alternatively[vbcol=seagreen]
> try to
get[vbcol=seagreen]
> this
> get.
and[vbcol=seagreen]
> connection
> Logins/
why[vbcol=seagreen]
> I
sql

Failed to set proper user name ('domain\username') for the connection

Im runnning a Trace from Production against a Development box and getting
this error message:
Failed to set proper user name ('domain\username') for the connection
. I have removed the db_id from the Trace table. Also, all the Logins/ Users
are set to the proper default DB. So Im really confused as to why I would be
getting this? Any ideas?
TIA, ChrisRHi
Have you got the same db_id for the database(s) on the production and
development servers? You may want to restore master! Alternatively try to
save the trace as a SQL File and then run that, you should not get this
problem and it will be easier to debug any problems that you do get.
John
"ChrisR" <ChrisR@.noEmail.com> wrote in message
news:%23qqMGq7CFHA.4052@.TK2MSFTNGP15.phx.gbl...
> Im runnning a Trace from Production against a Development box and getting
> this error message:
> Failed to set proper user name ('domain\username') for the connection
> . I have removed the db_id from the Trace table. Also, all the Logins/
> Users
> are set to the proper default DB. So Im really confused as to why I would
> be
> getting this? Any ideas?
>
> TIA, ChrisR
>|||John if Im not mistaken, the problem should have been fixed by simply
removing the db_id... therefore not requiring the restore of Master.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:e6jSfhHDFHA.2180@.TK2MSFTNGP10.phx.gbl...
> Hi
> Have you got the same db_id for the database(s) on the production and
> development servers? You may want to restore master! Alternatively try to
> save the trace as a SQL File and then run that, you should not get this
> problem and it will be easier to debug any problems that you do get.
> John
> "ChrisR" <ChrisR@.noEmail.com> wrote in message
> news:%23qqMGq7CFHA.4052@.TK2MSFTNGP15.phx.gbl...
> > Im runnning a Trace from Production against a Development box and
getting
> > this error message:
> >
> > Failed to set proper user name ('domain\username') for the connection
> >
> > . I have removed the db_id from the Trace table. Also, all the Logins/
> > Users
> > are set to the proper default DB. So Im really confused as to why I
would
> > be
> > getting this? Any ideas?
> >
> >
> >
> > TIA, ChrisR
> >
> >
>|||Hi
That is not what I understand! Profiling it may help.
John
ChrisR wrote:
> John if Im not mistaken, the problem should have been fixed by simply
> removing the db_id... therefore not requiring the restore of Master.
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:e6jSfhHDFHA.2180@.TK2MSFTNGP10.phx.gbl...
> > Hi
> >
> > Have you got the same db_id for the database(s) on the production
and
> > development servers? You may want to restore master! Alternatively
try to
> > save the trace as a SQL File and then run that, you should not get
this
> > problem and it will be easier to debug any problems that you do
get.
> >
> > John
> >
> > "ChrisR" <ChrisR@.noEmail.com> wrote in message
> > news:%23qqMGq7CFHA.4052@.TK2MSFTNGP15.phx.gbl...
> > > Im runnning a Trace from Production against a Development box and
> getting
> > > this error message:
> > >
> > > Failed to set proper user name ('domain\username') for the
connection
> > >
> > > . I have removed the db_id from the Trace table. Also, all the
Logins/
> > > Users
> > > are set to the proper default DB. So Im really confused as to why
I
> would
> > > be
> > > getting this? Any ideas?
> > >
> > >
> > >
> > > TIA, ChrisR
> > >
> > >
> >
> >|||It generally does work if you remove database ids from the
trace. Double check to make sure all the users captured in
the trace have a default database of the target database.
The commands should then be executed in the default database
so the database id isn't an issue.
-Sue
On 8 Feb 2005 00:01:01 -0800, "John Bell"
<jbellnewsposts@.hotmail.com> wrote:
>Hi
>That is not what I understand! Profiling it may help.
>John
>ChrisR wrote:
>> John if Im not mistaken, the problem should have been fixed by simply
>> removing the db_id... therefore not requiring the restore of Master.
>>
>> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
>> news:e6jSfhHDFHA.2180@.TK2MSFTNGP10.phx.gbl...
>> > Hi
>> >
>> > Have you got the same db_id for the database(s) on the production
>and
>> > development servers? You may want to restore master! Alternatively
>try to
>> > save the trace as a SQL File and then run that, you should not get
>this
>> > problem and it will be easier to debug any problems that you do
>get.
>> >
>> > John
>> >
>> > "ChrisR" <ChrisR@.noEmail.com> wrote in message
>> > news:%23qqMGq7CFHA.4052@.TK2MSFTNGP15.phx.gbl...
>> > > Im runnning a Trace from Production against a Development box and
>> getting
>> > > this error message:
>> > >
>> > > Failed to set proper user name ('domain\username') for the
>connection
>> > >
>> > > . I have removed the db_id from the Trace table. Also, all the
>Logins/
>> > > Users
>> > > are set to the proper default DB. So Im really confused as to why
>I
>> would
>> > > be
>> > > getting this? Any ideas?
>> > >
>> > >
>> > >
>> > > TIA, ChrisR
>> > >
>> > >
>> >
>> >|||Hi
Another through, although you say there default databases are set for
all users, check that there are no orphaned users.
John
John Bell wrote:
> Hi
> That is not what I understand! Profiling it may help.
> John
> ChrisR wrote:
> > John if Im not mistaken, the problem should have been fixed by
simply
> > removing the db_id... therefore not requiring the restore of
Master.
> >
> >
> > "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> > news:e6jSfhHDFHA.2180@.TK2MSFTNGP10.phx.gbl...
> > > Hi
> > >
> > > Have you got the same db_id for the database(s) on the production
> and
> > > development servers? You may want to restore master!
Alternatively
> try to
> > > save the trace as a SQL File and then run that, you should not
get
> this
> > > problem and it will be easier to debug any problems that you do
> get.
> > >
> > > John
> > >
> > > "ChrisR" <ChrisR@.noEmail.com> wrote in message
> > > news:%23qqMGq7CFHA.4052@.TK2MSFTNGP15.phx.gbl...
> > > > Im runnning a Trace from Production against a Development box
and
> > getting
> > > > this error message:
> > > >
> > > > Failed to set proper user name ('domain\username') for the
> connection
> > > >
> > > > . I have removed the db_id from the Trace table. Also, all the
> Logins/
> > > > Users
> > > > are set to the proper default DB. So Im really confused as to
why
> I
> > would
> > > > be
> > > > getting this? Any ideas?
> > > >
> > > >
> > > >
> > > > TIA, ChrisR
> > > >
> > > >
> > >
> > >

Failed to set proper user name ('domain\username') for the connection

failed to search using d.DateCreated >='05/07/2007' AND d.DateCreated<= '05/07/2007'

hi all programmers.
i want to find the record that created on only 05/07/2007
the datetime in my database is 5/7/2007 4:09:00 PM
i used this (d.DateCreated >='05/07/2007' AND d.DateCreated<= '05/07/2007' ) inside the where clause.
but it returns no result.
please help me on this issue. thanks for all helps.

Try this

d.DateCreated >='05/07/2007' AND d.DateCreated< '06/07/2007'

|||

Or, to avoid ambiguity caused by your connection's language settings, you might want to use the following date format:

yyyy-mm-dd hh:mi: ss.mmm

<ignore the space after mi: - excluding it from forum posts results in this: mi >

e.g.

d.DateCreated >= '2007-07-05 00:00:00.000' AND d.DateCreated < '2007-07-06 00:00:00.000'

d.DateCreated >= '2007-05-07 00:00:00.000' AND d.DateCreated < '2007-05-08 00:00:00.000'

(depending on the format of the date that you oiginally provided)

Chris

|||try this one

CONVERT(smalldatetime,CONVERT(varchar(10),d.DateCreated,101)) >=CONVERT(smalldatetime,'05/07/2007') AND
CONVERT(smalldatetime,CONVERT(varchar(10), d.DateCreated,101))<= CONVERT(smalldatetime,'05/07/2007')|||The where clause you specified is only looking for EXACTLY midnight, 5/07/2007 12:00am. If you want to search for anything on 5/7 use:

DateCreated >='5/7/2007' AND DateCreated < '5/8/2007'

Failed to script out Dropping Default Constraint script for table columns

I used the following code to script out Creating default constraints. It works fine. But if I change so.ScriptDrops = true to script Dropping default constraints, it always fails with

Exception: {"Script failed for DefaultConstraint 'DF_Employee_VacationHours'. "}

InnerException: {"Object reference not set to an instance of an object."}

Can anyone shred some light on it? Thanks in advance.

using System;

using System.Collections.Generic;

using System.Collections.Specialized;

using System.Text;

using Microsoft.SqlServer.Management.Smo;

using Microsoft.SqlServer.Management.Common;

namespace Test2

{

class Program

{

static void Main(string[] args)

{

Server s = new Server(".");

StringCollection sc;

ScriptingOptions so=new ScriptingOptions();

so.ScriptDrops = false;

s.ConnectionContext.SqlExecutionModes = SqlExecutionModes.CaptureSql;

foreach (Table t in s.Databases["Adventureworks"].Tables)

{

foreach (Column c in t.Columns)

{

if (c.DefaultConstraint!=null)

{

Console.WriteLine(string.Format("{0},{1}", t.Name, c.Name));

sc=c.DefaultConstraint.Script(so);

foreach (string st in sc)

{

Console.WriteLine(string.Format("{0}", st));

}

sc = null;

}

return;

}

Hi,

Thank you for reporting this issue. This is a defect in SMO, and we're filing a bug report. We will try to fix it for the next release of the product.

Failed to schedule DTS in SQL 2005

Hello,

I am trying to to schedule DTS Package but this message appear .

Message
The job failed. The Job was invoked by User sa. The last step to run was step 1 (1). and this

Message
Executed as user: Computer name\SYSTEM. The package execution failed. The step failed

so how I can schedule DTS Package in sql 2005 .

Do you have logging enabled for this package? if enabled check the log file it should have more detailed information on the error.

The error you have posted is a very generic error from the job agent, and it does not give any information.

Thanks

|||

Sounds like you might have some issues with the account settings. See the following thread for tips on setting up proxies / credentials / jobs etc.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1955723&SiteID=1

|||

Thanks, all

But still the same problem even I did all thing to solve but still can't schedule DTS, but when I see th proparites of the

SQL Server Agent-->Connection and found the sql server connection grayed out and can't select or change it . so may be this is the problem but how to run the agent under sa account .

|||

Assuming you are talking about the connection infromation on the left hand side of the job properties screen, that connection information is how you are currently connected to the sql server. You would need to look at the steps tab and click edit on the step associated with the package you are trying to run. At the top of this screen there will be a place for the step name (i.e. run package x), the type (sql server integration services package) and the run as. You would select the correct proxy name from the run as drop down that ties to your sa account...

|||

Thanks very much for you reply but I still can't schedule the DTS even though when I execute the dts it's work fine and I did all thing what said here http://www.codeproject.com/useritems/Schedule__Run__SSIS__DTS.asp

|||

I am trying to schedule working DTS in sql server 2005 sp 2 . but I it failed and got this message . I searched at Internet and found this

http://support.microsoft.com/kb/904796

but I can’t understand how to solve it, any one can help please .

Message

Executed as user: ComputrName\Administrator. ...00.3042.00 for 32-bitCopyright (C) Microsoft Corp 1984-2005. All rights reserved.Started:10:16:11 ?Error: 2007-08-17 22:16:12.95Code: 0x00000000Source: Copy Data from ROOM toDBNamedboER_ROOMTaskDescription: System.Runtime.InteropServices.COMException (0x80040427): Execution was canceled by user.at DTS.PackageClass.Execute()at Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.Exec80PackageTask.ExecuteThread()End ErrorError: 2007-08-17 22:16:14.75Code: 0x00000000Description: System.Runtime.InteropServices.COMException (0x80040427): Execution was canceled by user.at DTS.PackageClass.Execute()at Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.Exec80PackageTask.ExecuteThread()End ErrorError: 2007-08-17 22:16:15.14Code: 0x00000000The package execution fa...The step failed.

|||

how to Reinstall the SQL Server 2000 tools? where to find it .

Still no solution can solve this issue .

sql

Failed To Save Package File

I am just beginning to work on an ETL project at a new client site using their computers. They are an IT department new to SQL Server. We have SQL Server 2005 up and running, no probkem. But I get that error when I try to add a new package to an SSIS project. The error number is 0x8002801D "Library Not Registered". I did see the post about the MSXML DLL files and when I try to load an existing package I get 3 errors regarding XML - stating that the MSXML binaries may not be installed or registered properly. I searched and found the MSXML3 & 6 DLLs but the MSXML4.DLL appears to be missing. If I repair the installation, will this fix it? BTW - the MSXML 6.0 Parser did show as being installed in Add/Remove Programs.

Thanks for you help!

I won't guarantee that it will fix it, but it's not going to hurt Smile

|||

I do not think SSIS uses msxml4.dll. Try registering msxml3.dll and msxml6.dll.

Thanks.

|||Thanks, I will. But I do have a question. My client installed the 64 bit version of SQL Server and the client side tools from the same CD. When I search on the files, I see that both the 32 bit and 64 bit versions of the DLLs are on the system. Should I register the 64 bit DLLs?|||You should register both.

Failed To Save Package File

Thanks for you help!

I won't guarantee that it will fix it, but it's not going to hurt Smile

|||

I do not think SSIS uses msxml4.dll. Try registering msxml3.dll and msxml6.dll.

Thanks.

Failed to retrieve long data for column

Hi,

We have a SSIS project where we load lot of image files using an OLE DB Source component and put it across the database as varbinary into OLEDB Destination component.

Things were fine until now where am getting an error like this. alongside the log also reads like,

There is not enough space on the disk. Long data was retrieved and can't be added to the data flow task buffer.

SSIS Error Code: DTS_E_InducedTransformFailureOnError.

Is this method of loading the files using an OleDb Souce not the efficient one ? If not is there a way I can get this done. Comments are highly appreciated!!

Thanks in Advance

Have you check to see if you are running out of physical disk space?

Unless you have a directory specified in the BLOBTempStoragePath property of the data flow, the default location will be the directory specified by the TEMP environment variable.

Make sure that the directory listed by either of those isn't running out of disk space. If so, then set BLOBTempStoragePath to a directory containing more disk space.|||

Hi Phil,

Thanks for the valuable inputs,Is it ok if I just point a Directory in a Physical drive that has enough disk space is it? What purpose does this BlobStorage Serve ?

|||

MShetty wrote:
Hi Phil,

Thanks for the valuable inputs,Is it ok if I just point a Directory in a Physical drive that has enough disk space is it? What purpose does this BlobStorage Serve ?

The BLOB storage path is used to hold the contents of a varbinary field if it cannot fit into memory.

So point that parameter to a directory that contains enough disk space and try it again.|||Thanks Phil.. It worked Smile |||I got the same message. Failed to retrieve long data for column I was running an ssis job to import tables from an access database to sql2000 so we could change the back end to sql. I had everything working ok. Then I had to change the source connection from the dev access back end to the production access back end so i could get the latest data. One field in one of 83 tables kept blowing up. It was an Ntext field. I tried everything in these posts to no avial. Finally I noticed that the size of the production mdb was 3x the size of my dev mdb. So, I compacted the production mdb and reran the ssis package. It worked like a charm. Hope this may help someone else out there.
tom in stumptown

Failed to retrieve long data for column

Hi,

We have a SSIS project where we load lot of image files using an OLE DB Source component and put it across the database as varbinary into OLEDB Destination component.

Things were fine until now where am getting an error like this. alongside the log also reads like,

There is not enough space on the disk. Long data was retrieved and can't be added to the data flow task buffer.

SSIS Error Code: DTS_E_InducedTransformFailureOnError.

Is this method of loading the files using an OleDb Souce not the efficient one ? If not is there a way I can get this done. Comments are highly appreciated!!

Thanks in Advance

Have you check to see if you are running out of physical disk space?

Unless you have a directory specified in the BLOBTempStoragePath property of the data flow, the default location will be the directory specified by the TEMP environment variable.

Make sure that the directory listed by either of those isn't running out of disk space. If so, then set BLOBTempStoragePath to a directory containing more disk space.|||

Hi Phil,

Thanks for the valuable inputs,Is it ok if I just point a Directory in a Physical drive that has enough disk space is it? What purpose does this BlobStorage Serve ?

|||

MShetty wrote:
Hi Phil,
Thanks for the valuable inputs,Is it ok if I just point a Directory in a Physical drive that has enough disk space is it? What purpose does this BlobStorage Serve ?

The BLOB storage path is used to hold the contents of a varbinary field if it cannot fit into memory.

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

After getting an RPC error when trying to login to my server via either consol or Terminal services, then rebooting, I can no longer connect to SSIS

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

Connect to SSIS Service on machine "BG-22SQL" failed:

The service did not respond to the start or control request in a timely fashion.

Connect to SSIS Service on machine "BG-22SQL" failed:

The service did not respond to the start or control request in a timely fashion.

Can you start the SSIS service on this machine (try starting it via the Service Control Manager)?

If the service fails to start, please see if this KB article applies to your configuration:
http://support.microsoft.com/kb/918644sql

Failed to retrieve data for this request.

I am trying to load data into a SQL Server table through a web page. I am getting error like this:

Error: A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)

I am doing the same for other tables in the same db its working fine. but its working for one table alone. The table name is like this (DTD_PUMA_DeliveryPerformance)

Is it cos of table name?

I tried with import/export wizard also its working fine but still its not working for web page.

I don t think its cos of network or OS!! Its with table or some connection property.

Pls help me in solving this.

There is nothing wrong with the tablename.

Have you checked the connection string and compared it to the one on pages that are successful?

|||

Thanks for the reply.

Identified the problem, its cos of the excel sheet.

There is a row in the excel sheet with the description ("Get Keys"), when i removed tht row its working

So i need to know how to exclude the first row from the excel sheet while selecting (select * from [Sheet1$]).

failed to restore db from SQL 2005 to SQL 2000

Hi, There,
We download SQL 2005 Enterprise edt for 180-day evaluations, before that,
the DB is running on SQL 2000 Std.
When we did the migration, we just detach all DB and uninstall the software
and installed with 2005 SQL, and again attached those DB previous working on
SQL 2000.
Now for some reason, we need the database from SQL 2005 to be restored in
the SQL 2000 STD, I tried either backup from SQL 2005 and restored on 2000,
or detached from 2005 and attached in 2000, both action failed.
Is that a back compatable problem?
Kindly advise some solution how I can achive that if I need the Db from 2005
to be restored in 2000 version.
Thanks for any suggestion.
RgdsS
Shermaine
Hi
> Now for some reason, we need the database from SQL 2005 to be restored in
> the SQL 2000 STD, I tried either backup from SQL 2005 and restored on
> 2000,
> or detached from 2005 and attached in 2000, both action failed.
You cannot restore the db back to SQL Server 2000. One method is to create
an empty database in SQL Server 2000 the by using DTS Package transfer the
data from SS2005 to SS2000
"xiaomei" <xiaomei@.discussions.microsoft.com> wrote in message
news:5F3B4791-C2FF-4A97-9B9B-CB5C525D0D6D@.microsoft.com...
> Hi, There,
> We download SQL 2005 Enterprise edt for 180-day evaluations, before that,
> the DB is running on SQL 2000 Std.
> When we did the migration, we just detach all DB and uninstall the
> software
> and installed with 2005 SQL, and again attached those DB previous working
> on
> SQL 2000.
> Now for some reason, we need the database from SQL 2005 to be restored in
> the SQL 2000 STD, I tried either backup from SQL 2005 and restored on
> 2000,
> or detached from 2005 and attached in 2000, both action failed.
> Is that a back compatable problem?
> Kindly advise some solution how I can achive that if I need the Db from
> 2005
> to be restored in 2000 version.
> Thanks for any suggestion.
> RgdsS
> Shermaine
|||Hi, Uri,
Thanks for your good advice.
Is any limitation for the db transfer between SQL 2005 and 2000, Because I
am unable to register the 2005 in SQL 2000 server group, and vice verse.
I will try, thanks a lot.
Rgds
Shermaine
"Uri Dimant" wrote:

> Hi
> You cannot restore the db back to SQL Server 2000. One method is to create
> an empty database in SQL Server 2000 the by using DTS Package transfer the
> data from SS2005 to SS2000
>
> "xiaomei" <xiaomei@.discussions.microsoft.com> wrote in message
> news:5F3B4791-C2FF-4A97-9B9B-CB5C525D0D6D@.microsoft.com...
>
>
|||Hi
SQL Server 2005 has a new dataype XML , SQL Server 2000 hasn't for example
"xiaomei" <xiaomei@.discussions.microsoft.com> wrote in message
news:D6DF67A7-1248-45E0-98A3-F7AE89E69573@.microsoft.com...[vbcol=seagreen]
> Hi, Uri,
> Thanks for your good advice.
> Is any limitation for the db transfer between SQL 2005 and 2000, Because I
> am unable to register the 2005 in SQL 2000 server group, and vice verse.
> I will try, thanks a lot.
> Rgds
> Shermaine
> "Uri Dimant" wrote:
|||The transaction log format is also different. SQL Server has never been
downlevel compatible with any version - this is not new behavior with SQL
2005 -> 2000.
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e$7bQe4HHHA.3468@.TK2MSFTNGP04.phx.gbl...
> Hi
> SQL Server 2005 has a new dataype XML , SQL Server 2000 hasn't for example
> "xiaomei" <xiaomei@.discussions.microsoft.com> wrote in message
> news:D6DF67A7-1248-45E0-98A3-F7AE89E69573@.microsoft.com...
>
|||Dear Paul, Uri,
Thanks for your help.
Since I am new to SQL, I didn't expect the back-compitable issue, now it is
a big problem for me.
I tried to use DTS to export the database to 2000 server, but it is very
slow, for a 66MB data, it takes more than 40 mins to execute and also cause
one task failed, so in that case I need examine one by one which table was
copied, which not, that is a very big task, especially for my other DBs which
are around 6GB.
Any other suggestion I can do?
Thanks very much.
Rgds
Shermaine
"Paul S Randal [MS]" wrote:

> The transaction log format is also different. SQL Server has never been
> downlevel compatible with any version - this is not new behavior with SQL
> 2005 -> 2000.
> --
> Paul Randal
> Lead Program Manager, Microsoft SQL Server Storage Engine
> http://blogs.msdn.com/sqlserverstorageengine/default.aspx
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:e$7bQe4HHHA.3468@.TK2MSFTNGP04.phx.gbl...
>
>

failed to restore db from SQL 2005 to SQL 2000

Hi, There,
We download SQL 2005 Enterprise edt for 180-day evaluations, before that,
the DB is running on SQL 2000 Std.
When we did the migration, we just detach all DB and uninstall the software
and installed with 2005 SQL, and again attached those DB previous working on
SQL 2000.
Now for some reason, we need the database from SQL 2005 to be restored in
the SQL 2000 STD, I tried either backup from SQL 2005 and restored on 2000,
or detached from 2005 and attached in 2000, both action failed.
Is that a back compatable problem?
Kindly advise some solution how I can achive that if I need the Db from 2005
to be restored in 2000 version.
Thanks for any suggestion.
RgdsS
ShermaineHi
> Now for some reason, we need the database from SQL 2005 to be restored in
> the SQL 2000 STD, I tried either backup from SQL 2005 and restored on
> 2000,
> or detached from 2005 and attached in 2000, both action failed.
You cannot restore the db back to SQL Server 2000. One method is to create
an empty database in SQL Server 2000 the by using DTS Package transfer the
data from SS2005 to SS2000
"xiaomei" <xiaomei@.discussions.microsoft.com> wrote in message
news:5F3B4791-C2FF-4A97-9B9B-CB5C525D0D6D@.microsoft.com...
> Hi, There,
> We download SQL 2005 Enterprise edt for 180-day evaluations, before that,
> the DB is running on SQL 2000 Std.
> When we did the migration, we just detach all DB and uninstall the
> software
> and installed with 2005 SQL, and again attached those DB previous working
> on
> SQL 2000.
> Now for some reason, we need the database from SQL 2005 to be restored in
> the SQL 2000 STD, I tried either backup from SQL 2005 and restored on
> 2000,
> or detached from 2005 and attached in 2000, both action failed.
> Is that a back compatable problem?
> Kindly advise some solution how I can achive that if I need the Db from
> 2005
> to be restored in 2000 version.
> Thanks for any suggestion.
> RgdsS
> Shermaine|||Hi, Uri,
Thanks for your good advice.
Is any limitation for the db transfer between SQL 2005 and 2000, Because I
am unable to register the 2005 in SQL 2000 server group, and vice verse.
I will try, thanks a lot.
Rgds
Shermaine
"Uri Dimant" wrote:

> Hi
> You cannot restore the db back to SQL Server 2000. One method is to create
> an empty database in SQL Server 2000 the by using DTS Package transfer the
> data from SS2005 to SS2000
>
> "xiaomei" <xiaomei@.discussions.microsoft.com> wrote in message
> news:5F3B4791-C2FF-4A97-9B9B-CB5C525D0D6D@.microsoft.com...
>
>|||Hi
SQL Server 2005 has a new dataype XML , SQL Server 2000 hasn't for example
"xiaomei" <xiaomei@.discussions.microsoft.com> wrote in message
news:D6DF67A7-1248-45E0-98A3-F7AE89E69573@.microsoft.com...[vbcol=seagreen]
> Hi, Uri,
> Thanks for your good advice.
> Is any limitation for the db transfer between SQL 2005 and 2000, Because I
> am unable to register the 2005 in SQL 2000 server group, and vice verse.
> I will try, thanks a lot.
> Rgds
> Shermaine
> "Uri Dimant" wrote:
>|||The transaction log format is also different. SQL Server has never been
downlevel compatible with any version - this is not new behavior with SQL
2005 -> 2000.
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstor...ne/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e$7bQe4HHHA.3468@.TK2MSFTNGP04.phx.gbl...
> Hi
> SQL Server 2005 has a new dataype XML , SQL Server 2000 hasn't for example
> "xiaomei" <xiaomei@.discussions.microsoft.com> wrote in message
> news:D6DF67A7-1248-45E0-98A3-F7AE89E69573@.microsoft.com...
>|||Dear Paul, Uri,
Thanks for your help.
Since I am new to SQL, I didn't expect the back-compitable issue, now it is
a big problem for me.
I tried to use DTS to export the database to 2000 server, but it is very
slow, for a 66MB data, it takes more than 40 mins to execute and also cause
one task failed, so in that case I need examine one by one which table was
copied, which not, that is a very big task, especially for my other DBs whic
h
are around 6GB.
Any other suggestion I can do?
Thanks very much.
Rgds
Shermaine
"Paul S Randal [MS]" wrote:

> The transaction log format is also different. SQL Server has never been
> downlevel compatible with any version - this is not new behavior with SQL
> 2005 -> 2000.
> --
> Paul Randal
> Lead Program Manager, Microsoft SQL Server Storage Engine
> http://blogs.msdn.com/sqlserverstor...ne/default.aspx
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:e$7bQe4HHHA.3468@.TK2MSFTNGP04.phx.gbl...
>
>|||Yo, MICROSOFT:
Sit up and take notice:
If you are going to allow things like "Compatibility level 8.0" for
databases stored in SQL 2005, then those database need to be FULLY and
UNCONDITIONALLY compatible with SQL 2000 installations!
We, as DBA's, need the ability to backup a (version 2000) database in SQL
2005 and restore it to a SQL 2000 instance.
If not, then change the nomenclature of "Compatibility 8.0" to something
more descriptive, like "U R screwed"!
I can do it in Word 2003, Excel 2007, Access 2003, and any combination of MS
products and versions, BUT NOT IN SQL.
That sucks!
--
Todd Chittenden
"Paul S Randal [MS]" wrote:

> The transaction log format is also different. SQL Server has never been
> downlevel compatible with any version - this is not new behavior with SQL
> 2005 -> 2000.
> --
> Paul Randal
> Lead Program Manager, Microsoft SQL Server Storage Engine
> http://blogs.msdn.com/sqlserverstor...ne/default.aspx
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:e$7bQe4HHHA.3468@.TK2MSFTNGP04.phx.gbl...
>
>

Failed to reserve contiguous memory

we have a cluster of 2 nodes running Windows 2000 SP4 with SQL Server
Enterprise Edition SP3.
There are 4 instances installed with 2 instances on each node.
We have started to have a problem over the last 2 weeks where jobs are
unable to start and return the error -
"Error 22022: Unable to post notification to SQLServerAgent (reason:
MapViewOfFile() returned error 8, Not enough storage is available to process
this"
looking at the error logs for both instances further errors are returned as
can be seen at the bottom of this post.
The instances that are returning the errors are both on the same node, to
investigate further their preferred nodes are switching over tonight, can yo
u
give me some more insight into what type of errors we are experiencing.
INSTANCE ES
2006-03-08 09:06:45.62 spid68 Buffer Distribution: Stolen=892 Free=453
Procedures=6196
Inram=0 Dirty=2110 Kept=0
I/O=0, Latched=132, Other=159592
2006-03-08 09:06:45.62 spid68 Buffer Counts: Commited=169375
Target=216843 Hashed=161834
InternalReservation=140 ExternalReservation=0 Min Free=128
2006-03-08 09:06:45.62 spid68 Procedure Cache: TotalProcs=2204
TotalPages=6196 InUsePages=3584
2006-03-08 09:06:45.62 spid68 Dynamic Memory Manager: Stolen=7088 OS
Reserved=1728
OS Committed=1708
OS In Use=1694
Query Plan=6095 Optimizer=0
General=2078
Utilities=8 Connection=193
2006-03-08 09:06:45.63 spid68 Global Memory Objects: Resource=1737
Locks=347
SQLCache=140 Replication=2
LockBytes=2 ServerGlobal=44
Xact=18
2006-03-08 09:06:45.63 spid68 Query Memory Manager: Grants=0 Waiting=0
Maximum=159247 Available=159247
2006-03-08 09:06:48.29 spid68 Error: 17803, Severity: 20, State: 12
2006-03-08 09:06:48.29 spid68 Insufficient memory available..
2006-03-08 09:06:56.21 spid68 WARNING: Failed to reserve contiguous
memory of Size= 262144.
2006-03-08 09:06:56.23 spid68 Buffer Distribution: Stolen=924 Free=558
Procedures=6059
Inram=0 Dirty=2110 Kept=0
I/O=0, Latched=132, Other=159592
2006-03-08 09:06:56.23 spid68 Buffer Counts: Commited=169375
Target=216554 Hashed=161834
InternalReservation=272 ExternalReservation=0 Min Free=128
2006-03-08 09:06:56.23 spid68 Procedure Cache: TotalProcs=2199
TotalPages=6059 InUsePages=3528
2006-03-08 09:06:56.23 spid68 Dynamic Memory Manager: Stolen=6983 OS
Reserved=1728
OS Committed=1708
OS In Use=1685
Query Plan=5981 Optimizer=0
General=2078
Utilities=8 Connection=193
2006-03-08 09:06:56.23 spid68 Global Memory Objects: Resource=1737
Locks=347
SQLCache=140 Replication=2
LockBytes=2 ServerGlobal=44
Xact=18
2006-03-08 09:06:56.23 spid68 Query Memory Manager: Grants=0 Waiting=0
Maximum=159247 Available=159247
2006-03-08 09:06:56.23 spid68 Error: 17803, Severity: 20, State: 12
2006-03-08 09:06:56.23 spid68 Insufficient memory available..
INSTANCE FR
2006-03-07 09:34:16.15 spid78 WARNING: Failed to reserve contiguous
memory of Size= 2883584.
2006-03-07 09:34:16.18 spid78 Buffer Distribution: Stolen=1406 Free=5648
Procedures=8709
Inram=0 Dirty=10812 Kept=0
I/O=0, Latched=122, Other=238389
2006-03-07 09:34:16.18 spid78 Buffer Counts: Commited=265086
Target=276059 Hashed=249323
InternalReservation=180 ExternalReservation=0 Min Free=128
2006-03-07 09:34:16.18 spid78 Procedure Cache: TotalProcs=2430
TotalPages=8709 InUsePages=5151
2006-03-07 09:34:16.18 spid78 Dynamic Memory Manager: Stolen=10115 OS
Reserved=1392
OS Committed=1355
OS In Use=1342
Query Plan=8623 Optimizer=0
General=1822
Utilities=9 Connection=862
2006-03-07 09:34:16.20 spid78 Global Memory Objects: Resource=1493
Locks=56
SQLCache=142 Replication=2
LockBytes=2 ServerGlobal=43
Xact=29
2006-03-07 09:34:16.20 spid78 Query Memory Manager: Grants=0 Waiting=0
Maximum=202611 Available=202611
2006-03-07 09:34:16.37 spid78 Error: 17803, Severity: 20, State: 12
2006-03-07 09:34:16.37 spid78 Insufficient memory available..
2006-03-07 09:36:09.73 spid78 WARNING: Failed to reserve contiguous
memory of Size= 2883584.
2006-03-07 09:36:09.74 spid78 Buffer Distribution: Stolen=1714 Free=5825
Procedures=8285
Inram=0 Dirty=10813 Kept=0
I/O=0, Latched=122, Other=238404
2006-03-07 09:36:09.74 spid78 Buffer Counts: Commited=265163
Target=286067 Hashed=249339
InternalReservation=286 ExternalReservation=0 Min Free=128
2006-03-07 09:36:09.74 spid78 Procedure Cache: TotalProcs=2424
TotalPages=8285 InUsePages=4827
2006-03-07 09:36:09.74 spid78 Dynamic Memory Manager: Stolen=9999 OS
Reserved=1376
OS Committed=1339
OS In Use=1322
Query Plan=8496 Optimizer=0
General=1822
Utilities=9 Connection=853
2006-03-07 09:36:09.74 spid78 Global Memory Objects: Resource=1493
Locks=56
SQLCache=142 Replication=2
LockBytes=2 ServerGlobal=43
Xact=29
2006-03-07 09:36:09.74 spid78 Query Memory Manager: Grants=0 Waiting=0
Maximum=209646 Available=209646
2006-03-07 09:36:09.74 spid78 Error: 17803, Severity: 20, State: 12
2006-03-07 09:36:09.74 spid78 Insufficient memory available..
2006-03-07 09:40:44.73 spid90 WARNING: Failed to reserve contiguous
memory of Size= 393216.
2006-03-07 09:40:44.75 spid90 Buffer Distribution: Stolen=1387 Free=6121
Procedures=8391
Inram=0 Dirty=10573 Kept=0
I/O=0, Latched=129, Other=238697
2006-03-07 09:40:44.75 spid90 Buffer Counts: Commited=265298
Target=291738 Hashed=249399
InternalReservation=304 ExternalReservation=112 Min Free=128
2006-03-07 09:40:44.75 spid90 Procedure Cache: TotalProcs=2482
TotalPages=8391 InUsePages=4895
2006-03-07 09:40:44.75 spid90 Dynamic Memory Manager: Stolen=9757 OS
Reserved=1392
OS Committed=1355
OS In Use=1336
Query Plan=8623 Optimizer=35
General=1830
Utilities=9 Connection=307
2006-03-07 09:40:44.75 spid90 Global Memory Objects: Resource=1493
Locks=292
SQLCache=145 Replication=2
LockBytes=2 ServerGlobal=43
Xact=29
2006-03-07 09:40:44.75 spid90 Query Memory Manager: Grants=1 Waiting=0
Maximum=213526 Available=213393
2006-03-07 09:40:44.77 spid90 Error: 17803, Severity: 20, State: 12
2006-03-07 09:40:44.77 spid90 Insufficient memory available..
2006-03-07 09:40:48.69 spid90 WARNING: Failed to reserve contiguous
memory of Size= 393216.
2006-03-07 09:40:48.69 spid90 Buffer Distribution: Stolen=1317 Free=6265
Procedures=8397
Inram=0 Dirty=10489 Kept=0
I/O=0, Latched=129, Other=238701
2006-03-07 09:40:48.69 spid90 Buffer Counts: Commited=265298
Target=290968 Hashed=249319
InternalReservation=311 ExternalReservation=40 Min Free=128
2006-03-07 09:40:48.69 spid90 Procedure Cache: TotalProcs=2483
TotalPages=8397 InUsePages=4882
2006-03-07 09:40:48.69 spid90 Dynamic Memory Manager: Stolen=9696 OS
Reserved=1392
OS Committed=1355
OS In Use=1338
Query Plan=8614 Optimizer=0
General=1828
Utilities=9 Connection=301
2006-03-07 09:40:48.69 spid90 Global Memory Objects: Resource=1493
Locks=249
SQLCache=145 Replication=2
LockBytes=2 ServerGlobal=43
Xact=29
2006-03-07 09:40:48.69 spid90 Query Memory Manager: Grants=1 Waiting=0
Maximum=213975 Available=213917
2006-03-07 09:40:48.70 spid90 Error: 17803, Severity: 20, State: 12
2006-03-07 09:40:48.70 spid90 Insufficient memory available..
2006-03-07 09:41:02.89 spid85 WARNING: Failed to reserve contiguous
memory of Size= 393216.
2006-03-07 09:41:02.91 spid85 Buffer Distribution: Stolen=1289 Free=6292
Procedures=8398
Inram=0 Dirty=10496 Kept=0
I/O=0, Latched=128, Other=238695
2006-03-07 09:41:02.91 spid85 Buffer Counts: Commited=265298
Target=291100 Hashed=249319
InternalReservation=311 ExternalReservation=0 Min Free=128
2006-03-07 09:41:02.91 spid85 Procedure Cache: TotalProcs=2484
TotalPages=8398 InUsePages=4882
2006-03-07 09:41:02.91 spid85 Dynamic Memory Manager: Stolen=9687 OS
Reserved=1392
OS Committed=1355
OS In Use=1334
Query Plan=8612 Optimizer=0
General=1822
Utilities=9 Connection=296
2006-03-07 09:41:02.91 spid85 Global Memory Objects: Resource=1493
Locks=249
SQLCache=145 Replication=2
LockBytes=2 ServerGlobal=43
Xact=29
2006-03-07 09:41:02.91 spid85 Query Memory Manager: Grants=0 Waiting=0
Maximum=213745 Available=213745
2006-03-07 09:41:02.91 spid85 Error: 17803, Severity: 20, State: 12
2006-03-07 09:41:02.91 spid85 Insufficient memory available..
2006-03-07 09:42:12.80 spid83 WARNING: Failed to reserve contiguous
memory of Size= 393216.
2006-03-07 09:42:12.80 spid83 Buffer Distribution: Stolen=1273 Free=6015
Procedures=8407
Inram=0 Dirty=10506 Kept=0
I/O=0, Latched=126, Other=238974
2006-03-07 09:42:12.80 spid83 Buffer Counts: Commited=265301
Target=286566 Hashed=249606
InternalReservation=306 ExternalReservation=0 Min Free=128
2006-03-07 09:42:12.80 spid83 Procedure Cache: TotalProcs=2489
TotalPages=8407 InUsePages=4892
2006-03-07 09:42:12.80 spid83 Dynamic Memory Manager: Stolen=9680 OS
Reserved=1392
OS Committed=1355
OS In Use=1334
Query Plan=8627 Optimizer=0
General=1816
Utilities=9 Connection=280
2006-03-07 09:42:12.80 spid83 Global Memory Objects: Resource=1493
Locks=214
SQLCache=145 Replication=2
LockBytes=2 ServerGlobal=43
Xact=29
2006-03-07 09:42:12.80 spid83 Query Memory Manager: Grants=0 Waiting=0
Maximum=210349 Available=210349
2006-03-07 09:42:12.81 spid83 Error: 17803, Severity: 20, State: 12
2006-03-07 09:42:12.81 spid83 Insufficient memory available..
2006-03-07 09:43:01.86 spid83 WARNING: Failed to reserve contiguous
memory of Size= 393216.
2006-03-07 09:43:01.88 spid83 Buffer Distribution: Stolen=1267 Free=6013
Procedures=8415
Inram=0 Dirty=10511 Kept=0
I/O=0, Latched=126, Other=238969
2006-03-07 09:43:01.88 spid83 Buffer Counts: Commited=265301
Target=283566 Hashed=249606
InternalReservation=304 ExternalReservation=0 Min Free=128
2006-03-07 09:43:01.88 spid83 Procedure Cache: TotalProcs=2490
TotalPages=8415 InUsePages=4895
2006-03-07 09:43:01.88 spid83 Dynamic Memory Manager: Stolen=9682 OS
Reserved=1392
OS Committed=1355
OS In Use=1334
Query Plan=8635 Optimizer=0
General=1816
Utilities=9 Connection=280
2006-03-07 09:43:01.88 spid83 Global Memory Objects: Resource=1493
Locks=186
SQLCache=145 Replication=2
LockBytes=2 ServerGlobal=43
Xact=29
2006-03-07 09:43:01.88 spid83 Query Memory Manager: Grants=0 Waiting=0
Maximum=208224 Available=208224
2006-03-07 09:43:01.88 spid83 Error: 17803, Severity: 20, State: 12
2006-03-07 09:43:01.88 spid83 Insufficient memory available..
2006-03-07 09:44:51.07 spid83 WARNING: Failed to reserve contiguous
memory of Size= 393216.
2006-03-07 09:44:51.07 spid83 Buffer Distribution: Stolen=1246 Free=5938
Procedures=8451
Inram=0 Dirty=10539 Kept=0
I/O=0, Latched=125, Other=239070
2006-03-07 09:44:51.07 spid83 Buffer Counts: Commited=265369
Target=276731 Hashed=249734
InternalReservation=304 ExternalReservation=0 Min Free=128
2006-03-07 09:44:51.07 spid83 Procedure Cache: TotalProcs=2519
TotalPages=8451 InUsePages=4915
2006-03-07 09:44:51.07 spid83 Dynamic Memory Manager: Stolen=9697 OS
Reserved=1392
OS Committed=1355
OS In Use=1334
Query Plan=8669 Optimizer=0
General=1819
Utilities=9 Connection=284
2006-03-07 09:44:51.07 spid83 Global Memory Objects: Resource=1493
Locks=145
SQLCache=146 Replication=2
LockBytes=2 ServerGlobal=43
Xact=29
2006-03-07 09:44:51.07 spid83 Query Memory Manager: Grants=0 Waiting=0
Maximum=203051 Available=203051
2006-03-07 09:44:51.08 spid83 Error: 17803, Severity: 20, State: 12
2006-03-07 09:44:51.08 spid83 Insufficient memory available..
2006-03-07 09:48:35.73 spid53 WARNING: Failed to reserve contiguous
memory of Size= 2883584.
2006-03-07 09:48:35.81 spid53 Buffer Distribution: Stolen=1852 Free=254
Procedures=8279
Inram=0 Dirty=8797 Kept=0
I/O=0, Latched=123, Other=275965
2006-03-07 09:48:35.81 spid53 Buffer Counts: Commited=295270
Target=295270 Hashed=284885
InternalReservation=301 ExternalReservation=60 Min Free=280
2006-03-07 09:48:35.81 spid53 Procedure Cache: TotalProcs=2532
TotalPages=8279 InUsePages=4576
2006-03-07 09:48:35.81 spid53 Dynamic Memory Manager: Stolen=10113 OS
Reserved=1376
OS Committed=1339
OS In Use=1322
Query Plan=8507 Optimizer=0
General=1824
Utilities=9 Connection=873
2006-03-07 09:48:35.82 spid53 Global Memory Objects: Resource=1493
Locks=98
SQLCache=146 Replication=2
LockBytes=2 ServerGlobal=43
Xact=29
2006-03-07 09:48:35.82 spid53 Query Memory Manager: Grants=1 Waiting=0
Maximum=216625 Available=216547
2006-03-07 09:48:36.17 spid53 Error: 17803, Severity: 20, State: 12
2006-03-07 09:48:36.17 spid53 Insufficient memory available..
2006-03-07 09:52:20.51 spid86 WARNING: Failed to reserve contiguous
memory of Size= 2883584.
2006-03-07 09:52:20.53 spid86 Buffer Distribution: Stolen=1834 Free=0
Procedures=8449
Inram=0 Dirty=8873 Kept=0
I/O=0, Latched=126, Other=281582
2006-03-07 09:52:20.53 spid86 Buffer Counts: Commited=300864
Target=303340 Hashed=290581
InternalReservation=299 ExternalReservation=60 Min Free=512
2006-03-07 09:52:20.53 spid86 Procedure Cache: TotalProcs=2565
TotalPages=8449 InUsePages=4679
2006-03-07 09:52:20.53 spid86 Dynamic Memory Manager: Stolen=10265 OS
Reserved=1376
OS Committed=1339
OS In Use=1324
Query Plan=8677 Optimizer=0
General=1825
Utilities=9 Connection=867
2006-03-07 09:52:20.53 spid86 Global Memory Objects: Resource=1493
Locks=79
SQLCache=147 Replication=2
LockBytes=2 ServerGlobal=43
Xact=29
2006-03-07 09:52:20.53 spid86 Query Memory Manager: Grants=1 Waiting=0
Maximum=223068 Available=222990
2006-03-07 09:52:20.53 spid86 Error: 17803, Severity: 20, State: 12
2006-03-07 09:52:20.53 spid86 Insufficient memory available..
2006-03-07 09:52:29.40 spid86 WARNING: Failed to reserve contiguous
memory of Size= 2883584.
2006-03-07 09:52:29.42 spid86 Buffer Distribution: Stolen=1844 Free=230
Procedures=8435
Inram=0 Dirty=8873 Kept=0
I/O=0, Latched=127, Other=281582
2006-03-07 09:52:29.42 spid86 Buffer Counts: Commited=301091
Target=303153 Hashed=290582
InternalReservation=302 ExternalReservation=60 Min Free=496
2006-03-07 09:52:29.42 spid86 Procedure Cache: TotalProcs=2563
TotalPages=8435 InUsePages=4666
2006-03-07 09:52:29.42 spid86 Dynamic Memory Manager: Stolen=10261 OS
Reserved=1376
OS Committed=1339
OS In Use=1322
Query Plan=8666 Optimizer=0
General=1825
Utilities=9 Connection=872
2006-03-07 09:52:29.42 spid86 Global Memory Objects: Resource=1493
Locks=74
SQLCache=147 Replication=2
LockBytes=2 ServerGlobal=43
Xact=29
2006-03-07 09:52:29.42 spid86 Query Memory Manager: Grants=1 Waiting=0
Maximum=223086 Available=223008
2006-03-07 09:52:29.42 spid86 Error: 17803, Severity: 20, State: 12
2006-03-07 09:52:29.42 spid86 Insufficient memory available..
2006-03-07 09:53:43.58 spid76 WARNING: Failed to reserve contiguous
memory of Size= 2883584.
2006-03-07 09:53:43.59 spid76 Buffer Distribution: Stolen=1802
Free=24279 Procedures=8464
Inram=0 Dirty=8962 Kept=0
I/O=0, Latched=126, Other=255435
2006-03-07 09:53:43.59 spid76 Buffer Counts: Commited=299068
Target=299068 Hashed=264523
InternalReservation=303 ExternalReservation=0 Min Free=360
2006-03-07 09:53:43.59 spid76 Procedure Cache: TotalProcs=2580
TotalPages=8464 InUsePages=4689
2006-03-07 09:53:43.59 spid76 Dynamic Memory Manager: Stolen=10266 OS
Reserved=1376
OS Committed=1339
OS In Use=1322
Query Plan=8678 Optimizer=0
General=1824
Utilities=9 Connection=866
2006-03-07 09:53:43.59 spid76 Global Memory Objects: Resource=1493
Locks=71
SQLCache=147 Replication=2
LockBytes=2 ServerGlobal=43
Xact=29
2006-03-07 09:53:43.59 spid76 Query Memory Manager: Grants=0 Waiting=0
Maximum=219938 Available=219938
2006-03-07 09:53:43.59 spid76 Error: 17803, Severity: 20, State: 12
2006-03-07 09:53:43.59 spid76 Insufficient memory available..
2006-03-07 09:54:17.44 spid85 WARNING: Failed to reserve contiguous
memory of Size= 2883584.
2006-03-07 09:54:17.44 spid85 Buffer Distribution: Stolen=1806
Free=23999 Procedures=8470
Inram=0 Dirty=8971 Kept=0
I/O=0, Latched=119, Other=255568
2006-03-07 09:54:17.44 spid85 Buffer Counts: Commited=298933
Target=298933 Hashed=264658
InternalReservation=302 ExternalReservation=0 Min Free=224
2006-03-07 09:54:17.44 spid85 Procedure Cache: TotalProcs=2580
TotalPages=8470 InUsePages=4696
2006-03-07 09:54:17.44 spid85 Dynamic Memory Manager: Stolen=10276 OS
Reserved=1376
OS Committed=1339
OS In Use=1322
Query Plan=8684 Optimizer=0
General=1824
Utilities=9 Connection=870
2006-03-07 09:54:17.44 spid85 Global Memory Objects: Resource=1493
Locks=70
SQLCache=147 Replication=2
LockBytes=2 ServerGlobal=43
Xact=29
2006-03-07 09:54:17.44 spid85 Query Memory Manager: Grants=0 Waiting=0
Maximum=219821 Available=219821
2006-03-07 09:54:17.45 spid85 Error: 17803, Severity: 20, State: 12
2006-03-07 09:54:17.45 spid85 Insufficient memory available..
2006-03-07 09:55:01.91 spid85 WARNING: Failed to reserve contiguous
memory of Size= 2883584.
2006-03-07 09:55:01.92 spid85 Buffer Distribution: Stolen=1814
Free=23146 Procedures=8477
Inram=0 Dirty=8982 Kept=0
I/O=0, Latched=118, Other=256321
2006-03-07 09:55:01.92 spid85 Buffer Counts: Commited=298858
Target=298858 Hashed=265421
InternalReservation=295 ExternalReservation=0 Min Free=128
2006-03-07 09:55:01.92 spid85 Procedure Cache: TotalProcs=2584
TotalPages=8477 InUsePages=4709
2006-03-07 09:55:01.92 spid85 Dynamic Memory Manager: Stolen=10291 OS
Reserved=1376
OS Committed=1339
OS In Use=1322
Query Plan=8699 Optimizer=0
General=1825
Utilities=9 Connection=869
2006-03-07 09:55:01.92 spid85 Global Memory Objects: Resource=1494
Locks=70
SQLCache=147 Replication=2
LockBytes=2 ServerGlobal=43
Xact=29
2006-03-07 09:55:01.92 spid85 Query Memory Manager: Grants=0 Waiting=0
Maximum=219759 Available=219759
2006-03-07 09:55:01.92 spid85 Error: 17803, Severity: 20, State: 12
2006-03-07 09:55:01.92 spid85 Insufficient memory available..
2006-03-07 09:56:15.35 spid87 WARNING: Failed to reserve contiguous
memory of Size= 2883584.
2006-03-07 09:56:15.36 spid87 Buffer Distribution: Stolen=1847
Free=23165 Procedures=8421
Inram=0 Dirty=8991 Kept=0
I/O=0, Latched=116, Other=256318
2006-03-07 09:56:15.36 spid87 Buffer Counts: Commited=298858
Target=314884 Hashed=265425
InternalReservation=294 ExternalReservation=60 Min Free=128
2006-03-07 09:56:15.36 spid87 Procedure Cache: TotalProcs=2588
TotalPages=8421 InUsePages=4621
2006-03-07 09:56:15.36 spid87 Dynamic Memory Manager: Stolen=10250 OS
Reserved=1376
OS Committed=1339
OS In Use=1322
Query Plan=8649 Optimizer=0
General=1825
Utilities=9 Connection=878
2006-03-07 09:56:15.36 spid87 Global Memory Objects: Resource=1493
Locks=70
SQLCache=147 Replication=2
LockBytes=2 ServerGlobal=43
Xact=29
2006-03-07 09:56:15.36 spid87 Query Memory Manager: Grants=1 Waiting=0
Maximum=231843 Available=231765
2006-03-07 09:56:15.36 spid87 Error: 17803, Severity: 20, State: 12
2006-03-07 09:56:15.36 spid87 Insufficient memory available..
2006-03-07 09:56:39.77 spid87 WARNING: Failed to reserve contiguous
memory of Size= 2752512.
2006-03-07 09:56:39.77 spid87 Buffer Distribution: Stolen=1820
Free=22535 Procedures=8420
Inram=0 Dirty=8991 Kept=0
I/O=0, Latched=117, Other=256975
2006-03-07 09:56:39.77 spid87 Buffer Counts: Commited=298858
Target=314520 Hashed=266083
InternalReservation=292 ExternalReservation=60 Min Free=128
2006-03-07 09:56:39.77 spid87 Procedure Cache: TotalProcs=2587
TotalPages=8420 InUsePages=4621
2006-03-07 09:56:39.77 spid87 Dynamic Memory Manager: Stolen=10222 OS
Reserved=1376
OS Committed=1339
OS In Use=1322
Query Plan=8650 Optimizer=0
General=1825
Utilities=9 Connection=849
2006-03-07 09:56:39.77 spid87 Global Memory Objects: Resource=1493
Locks=70
SQLCache=147 Replication=2
LockBytes=2 ServerGlobal=43
Xact=29
2006-03-07 09:56:39.77 spid87 Query Memory Manager: Grants=1 Waiting=0
Maximum=231587 Available=231509
2006-03-07 09:56:39.78 spid87 Error: 17803, Severity: 20, State: 12
2006-03-07 09:56:39.78 spid87 Insufficient memory available..
2006-03-07 10:03:11.82 spid76 WARNING: Failed to reserve contiguous
memory of Size= 2752512.
2006-03-07 10:03:11.84 spid76 Buffer Distribution: Stolen=1791
Free=22216 Procedures=8476
Inram=0 Dirty=8763 Kept=0
I/O=0, Latched=125, Other=257487
2006-03-07 10:03:11.84 spid76 Buffer Counts: Commited=298858
Target=315219 Hashed=266375
InternalReservation=311 ExternalReservation=0 Min Free=128
2006-03-07 10:03:11.84 spid76 Procedure Cache: TotalProcs=2616
TotalPages=8476 InUsePages=4646
2006-03-07 10:03:11.84 spid76 Dynamic Memory Manager: Stolen=10267 OS
Reserved=1376
OS Committed=1339
OS In Use=1322
Query Plan=8690 Optimizer=0
General=1826
Utilities=9 Connection=854
2006-03-07 10:03:11.84 spid76 Global Memory Objects: Resource=1493
Locks=81
SQLCache=147 Replication=2
LockBytes=2 ServerGlobal=43
Xact=31
2006-03-07 10:03:11.84 spid76 Query Memory Manager: Grants=0 Waiting=0
Maximum=232111 Available=232111
2006-03-07 10:03:11.84 spid76 Error: 17803, Severity: 20, State: 12
2006-03-07 10:03:11.84 spid76 Insufficient memory available..
2006-03-07 10:43:21.28 spid78 DBCC CHECKDB (msdb) executed by
SPICERS_WH\CaseltonC found 0 errors and repaired 0 errors.
2006-03-07 10:46:04.68 spid88 WARNING: Failed to reserve contiguous
memory of Size= 2883584.
2006-03-07 10:46:04.69 spid88 Buffer Distribution: Stolen=2640
Free=10042 Procedures=9219
Inram=0 Dirty=9834 Kept=0
I/O=0, Latched=142, Other=291391
2006-03-07 10:46:04.69 spid88 Buffer Counts: Commited=323268
Target=338520 Hashed=301367
InternalReservation=309 ExternalReservation=5060 Min Free=128
2006-03-07 10:46:04.69 spid88 Procedure Cache: TotalProcs=2842
TotalPages=9219 InUsePages=5071
2006-03-07 10:46:04.69 spid88 Dynamic Memory Manager: Stolen=11856 OS
Reserved=1392
OS Committed=1355
OS In Use=1332
Query Plan=9591 Optimizer=0
General=1985
Utilities=519 Connection=903
2006-03-07 10:46:04.69 spid88 Global Memory Objects: Resource=1505
Locks=50
SQLCache=154 Replication=2
LockBytes=2 ServerGlobal=46
Xact=31
2006-03-07 10:46:04.69 spid88 Query Memory Manager: Grants=2 Waiting=0
Maximum=248670 Available=243607
2006-03-07 10:46:04.71 spid88 Error: 17803, Severity: 20, State: 12
2006-03-07 10:46:04.71 spid88 Insufficient memory available..Please could I have a response to this post - it has now been over 3 busines
s
days.
I'm looking for help with where I should start my investigation - I don't
understand the reason for the 'not enough storage space' message, as there i
s
plenty of storage space! The error logs suggest a problem with memory -
'Failed to reserve contiguous memory of Size= 262144'. Presumably this is th
e
underlying problem? If so, it must be due to something that has changed on
all the servers, as we have now seen the problem on 2 of the 4 instances (1
from each cluster). Should I be looking at the jobs that are running with a
view to identifying memory leaks, or is it more likely to be due to somethin
g
that has changed at OS level - perhaps a patch that has been applied?
Finally, does SQL Server SP4 contain anything to specifically fix this
problem?
At the moment, we're getting round the issue by stopping and restarting
instances every night, which is obviously far from satisfactory.
Regards,
David.
"David" wrote:

> we have a cluster of 2 nodes running Windows 2000 SP4 with SQL Server
> Enterprise Edition SP3.
> There are 4 instances installed with 2 instances on each node.
> We have started to have a problem over the last 2 weeks where jobs are
> unable to start and return the error -
> "Error 22022: Unable to post notification to SQLServerAgent (reason:
> MapViewOfFile() returned error 8, Not enough storage is available to proce
ss
> this"
> looking at the error logs for both instances further errors are returned a
s
> can be seen at the bottom of this post.
> The instances that are returning the errors are both on the same node, to
> investigate further their preferred nodes are switching over tonight, can
you
> give me some more insight into what type of errors we are experiencing.
> INSTANCE ES
> 2006-03-08 09:06:45.62 spid68 Buffer Distribution: Stolen=892 Free=453
> Procedures=6196
> Inram=0 Dirty=2110 Kept=0
> I/O=0, Latched=132, Other=159592
> 2006-03-08 09:06:45.62 spid68 Buffer Counts: Commited=169375
> Target=216843 Hashed=161834
> InternalReservation=140 ExternalReservation=0 Min Free=128
> 2006-03-08 09:06:45.62 spid68 Procedure Cache: TotalProcs=2204
> TotalPages=6196 InUsePages=3584
> 2006-03-08 09:06:45.62 spid68 Dynamic Memory Manager: Stolen=7088 OS
> Reserved=1728
> OS Committed=1708
> OS In Use=1694
> Query Plan=6095 Optimizer=0
> General=2078
> Utilities=8 Connection=193
> 2006-03-08 09:06:45.63 spid68 Global Memory Objects: Resource=1737
> Locks=347
> SQLCache=140 Replication=2
> LockBytes=2 ServerGlobal=44
> Xact=18
> 2006-03-08 09:06:45.63 spid68 Query Memory Manager: Grants=0 Waiting=0
> Maximum=159247 Available=159247
> 2006-03-08 09:06:48.29 spid68 Error: 17803, Severity: 20, State: 12
> 2006-03-08 09:06:48.29 spid68 Insufficient memory available..
> 2006-03-08 09:06:56.21 spid68 WARNING: Failed to reserve contiguous
> memory of Size= 262144.
> 2006-03-08 09:06:56.23 spid68 Buffer Distribution: Stolen=924 Free=558
> Procedures=6059
> Inram=0 Dirty=2110 Kept=0
> I/O=0, Latched=132, Other=159592
> 2006-03-08 09:06:56.23 spid68 Buffer Counts: Commited=169375
> Target=216554 Hashed=161834
> InternalReservation=272 ExternalReservation=0 Min Free=128
> 2006-03-08 09:06:56.23 spid68 Procedure Cache: TotalProcs=2199
> TotalPages=6059 InUsePages=3528
> 2006-03-08 09:06:56.23 spid68 Dynamic Memory Manager: Stolen=6983 OS
> Reserved=1728
> OS Committed=1708
> OS In Use=1685
> Query Plan=5981 Optimizer=0
> General=2078
> Utilities=8 Connection=193
> 2006-03-08 09:06:56.23 spid68 Global Memory Objects: Resource=1737
> Locks=347
> SQLCache=140 Replication=2
> LockBytes=2 ServerGlobal=44
> Xact=18
> 2006-03-08 09:06:56.23 spid68 Query Memory Manager: Grants=0 Waiting=0
> Maximum=159247 Available=159247
> 2006-03-08 09:06:56.23 spid68 Error: 17803, Severity: 20, State: 12
> 2006-03-08 09:06:56.23 spid68 Insufficient memory available..
> INSTANCE FR
> 2006-03-07 09:34:16.15 spid78 WARNING: Failed to reserve contiguous
> memory of Size= 2883584.
> 2006-03-07 09:34:16.18 spid78 Buffer Distribution: Stolen=1406 Free=56
48
> Procedures=8709
> Inram=0 Dirty=10812 Kept=0
> I/O=0, Latched=122, Other=238389
> 2006-03-07 09:34:16.18 spid78 Buffer Counts: Commited=265086
> Target=276059 Hashed=249323
> InternalReservation=180 ExternalReservation=0 Min Free=128
> 2006-03-07 09:34:16.18 spid78 Procedure Cache: TotalProcs=2430
> TotalPages=8709 InUsePages=5151
> 2006-03-07 09:34:16.18 spid78 Dynamic Memory Manager: Stolen=10115 OS
> Reserved=1392
> OS Committed=1355
> OS In Use=1342
> Query Plan=8623 Optimizer=0
> General=1822
> Utilities=9 Connection=862
> 2006-03-07 09:34:16.20 spid78 Global Memory Objects: Resource=1493
> Locks=56
> SQLCache=142 Replication=2
> LockBytes=2 ServerGlobal=43
> Xact=29
> 2006-03-07 09:34:16.20 spid78 Query Memory Manager: Grants=0 Waiting=0
> Maximum=202611 Available=202611
> 2006-03-07 09:34:16.37 spid78 Error: 17803, Severity: 20, State: 12
> 2006-03-07 09:34:16.37 spid78 Insufficient memory available..
> 2006-03-07 09:36:09.73 spid78 WARNING: Failed to reserve contiguous
> memory of Size= 2883584.
> 2006-03-07 09:36:09.74 spid78 Buffer Distribution: Stolen=1714 Free=58
25
> Procedures=8285
> Inram=0 Dirty=10813 Kept=0
> I/O=0, Latched=122, Other=238404
> 2006-03-07 09:36:09.74 spid78 Buffer Counts: Commited=265163
> Target=286067 Hashed=249339
> InternalReservation=286 ExternalReservation=0 Min Free=128
> 2006-03-07 09:36:09.74 spid78 Procedure Cache: TotalProcs=2424
> TotalPages=8285 InUsePages=4827
> 2006-03-07 09:36:09.74 spid78 Dynamic Memory Manager: Stolen=9999 OS
> Reserved=1376
> OS Committed=1339
> OS In Use=1322
> Query Plan=8496 Optimizer=0
> General=1822
> Utilities=9 Connection=853
> 2006-03-07 09:36:09.74 spid78 Global Memory Objects: Resource=1493
> Locks=56
> SQLCache=142 Replication=2
> LockBytes=2 ServerGlobal=43
> Xact=29
> 2006-03-07 09:36:09.74 spid78 Query Memory Manager: Grants=0 Waiting=0
> Maximum=209646 Available=209646
> 2006-03-07 09:36:09.74 spid78 Error: 17803, Severity: 20, State: 12
> 2006-03-07 09:36:09.74 spid78 Insufficient memory available..
> 2006-03-07 09:40:44.73 spid90 WARNING: Failed to reserve contiguous
> memory of Size= 393216.
> 2006-03-07 09:40:44.75 spid90 Buffer Distribution: Stolen=1387 Free=61
21
> Procedures=8391
> Inram=0 Dirty=10573 Kept=0
> I/O=0, Latched=129, Other=238697
> 2006-03-07 09:40:44.75 spid90 Buffer Counts: Commited=265298
> Target=291738 Hashed=249399
> InternalReservation=304 ExternalReservation=112 Min Free=128
> 2006-03-07 09:40:44.75 spid90 Procedure Cache: TotalProcs=2482
> TotalPages=8391 InUsePages=4895
> 2006-03-07 09:40:44.75 spid90 Dynamic Memory Manager: Stolen=9757 OS
> Reserved=1392
> OS Committed=1355
> OS In Use=1336
> Query Plan=8623 Optimizer=35
> General=1830
> Utilities=9 Connection=307
> 2006-03-07 09:40:44.75 spid90 Global Memory Objects: Resource=1493
> Locks=292
> SQLCache=145 Replication=2
> LockBytes=2 ServerGlobal=43
> Xact=29
> 2006-03-07 09:40:44.75 spid90 Query Memory Manager: Grants=1 Waiting=0
> Maximum=213526 Available=213393
> 2006-03-07 09:40:44.77 spid90 Error: 17803, Severity: 20, State: 12
> 2006-03-07 09:40:44.77 spid90 Insufficient memory available..
> 2006-03-07 09:40:48.69 spid90 WARNING: Failed to reserve contiguous
> memory of Size= 393216.
> 2006-03-07 09:40:48.69 spid90 Buffer Distribution: Stolen=1317 Free=62
65
> Procedures=8397
> Inram=0 Dirty=10489 Kept=0
> I/O=0, Latched=129, Other=238701
> 2006-03-07 09:40:48.69 spid90 Buffer Counts: Commited=265298
> Target=290968 Hashed=249319
> InternalReservation=311 ExternalReservation=40 Min Free=128
> 2006-03-07 09:40:48.69 spid90 Procedure Cache: TotalProcs=2483
> TotalPages=8397 InUsePages=4882
> 2006-03-07 09:40:48.69 spid90 Dynamic Memory Manager: Stolen=9696 OS
> Reserved=1392
> OS Committed=1355
> OS In Use=1338
> Query Plan=8614 Optimizer=0
> General=1828
> Utilities=9 Connection=301
> 2006-03-07 09:40:48.69 spid90 Global Memory Objects: Resource=1493
> Locks=249
> SQLCache=145 Replication=2
> LockBytes=2 ServerGlobal=43
> Xact=29
> 2006-03-07 09:40:48.69 spid90 Query Memory Manager: Grants=1 Waiting=0
> Maximum=213975 Available=213917
> 2006-03-07 09:40:48.70 spid90 Error: 17803, Severity: 20, State: 12
> 2006-03-07 09:40:48.70 spid90 Insufficient memory available..
> 2006-03-07 09:41:02.89 spid85 WARNING: Failed to reserve contiguous
> memory of Size= 393216.
> 2006-03-07 09:41:02.91 spid85 Buffer Distribution: Stolen=1289 Free=62
92
> Procedures=8398
> Inram=0 Dirty=10496 Kept=0
> I/O=0, Latched=128, Other=238695
> 2006-03-07 09:41:02.91 spid85 Buffer Counts: Commited=265298
> Target=291100 Hashed=249319
> InternalReservation=311 ExternalReservation=0 Min Free=128
> 2006-03-07 09:41:02.91 spid85 Procedure Cache: TotalProcs=2484
> TotalPages=8398 InUsePages=4882
> 2006-03-07 09:41:02.91 spid85 Dynamic Memory Manager: Stolen=9687 OS
> Reserved=1392
> OS Committed=1355
> OS In Use=1334
> Query Plan=8612 Optimizer=0
> General=1822
> Utilities=9 Connection=296
> 2006-03-07 09:41:02.91 spid85 Global Memory Objects: Resource=1493
> Locks=249
> SQLCache=145 Replication=2
> LockBytes=2 ServerGlobal=43
> Xact=29
> 2006-03-07 09:41:02.91 spid85 Query Memory Manager: Grants=0 Waiting=0
> Maximum=213745 Available=213745
> 2006-03-07 09:41:02.91 spid85 Error: 17803, Severity: 20, State: 12
> 2006-03-07 09:41:02.91 spid85 Insufficient memory available..
> 2006-03-07 09:42:12.80 spid83 WARNING: Failed to reserve contiguous
> memory of Size= 393216.
> 2006-03-07 09:42:12.80 spid83 Buffer Distribution: Stolen=1273 Free=60
15
> Procedures=8407
> Inram=0 Dirty=10506 Kept=0
> I/O=0, Latched=126, Other=238974
> 2006-03-07 09:42:12.80 spid83 Buffer Counts: Commited=265301
> Target=286566 Hashed=249606
> InternalReservation=306 ExternalReservation=0 Min Free=128
> 2006-03-07 09:42:12.80 spid83 Procedure Cache: TotalProcs=2489
> TotalPages=8407 InUsePages=4892
> 2006-03-07 09:42:12.80 spid83 Dynamic Memory Manager: Stolen=9680 OS
> Reserved=1392
> OS Committed=1355
> OS In Use=1334
> Query Plan=8627 Optimizer=0
> General=1816
> Utilities=9 Connection=280
> 2006-03-07 09:42:12.80 spid83 Global Memory Objects: Resource=1493
> Locks=214
> SQLCache=145 Replication=2
> LockBytes=2 ServerGlobal=43
> Xact=29
> 2006-03-07 09:42:12.80 spid83 Query Memory Manager: Grants=0 Waiting=0
> Maximum=210349 Available=210349
> 2006-03-07 09:42:12.81 spid83 Error: 17803, Severity: 20, State: 12
> 2006-03-07 09:42:12.81 spid83 Insufficient memory available..
> 2006-03-07 09:43:01.86 spid83 WARNING: Failed to reserve contiguous
> memory of Size= 393216.
> 2006-03-07 09:43:01.88 spid83 Buffer Distribution: Stolen=1267 Free=60
13
> Procedures=8415
> Inram=0 Dirty=10511 Kept=0
> I/O=0, Latched=126, Other=238969
> 2006-03-07 09:43:01.88 spid83 Buffer Counts: Commited=265301
> Target=283566 Hashed=249606
> InternalReservation=304 ExternalReservation=0 Min Free=128
> 2006-03-07 09:43:01.88 spid83 Procedure Cache: TotalProcs=2490
> TotalPages=8415 InUsePages=4895
> 2006-03-07 09:43:01.88 spid83 Dynamic Memory Manager: Stolen=9682 OS
> Reserved=1392
> OS Committed=1355
> OS In Use=1334
> Query Plan=8635 Optimizer=0
> General=1816
> Utilities=9 Connection=280
> 2006-03-07 09:43:01.88 spid83 Global Memory Objects: Resource=1493
> Locks=186
> SQLCache=145 Replication=2
> LockBytes=2 ServerGlobal=43
> Xact=29
> 2006-03-07 09:43:01.88 spid83 Query Memory Manager: Grants=0 Waiting=0
> Maximum=208224 Available=208224
> 2006-03-07 09:43:01.88 spid83 Error: 17803, Severity: 20, State: 12
> 2006-03-07 09:43:01.88 spid83 Insufficient memory available..
> 2006-03-07 09:44:51.07 spid83 WARNING: Failed to reserve contiguous
> memory of Size= 393216.
> 2006-03-07 09:44:51.07 spid83 Buffer Distribution: Stolen=1246 Free=59
38
> Procedures=8451
> Inram=0 Dirty=10539 Kept=0
> I/O=0, Latched=125, Other=239070
> 2006-03-07 09:44:51.07 spid83 Buffer Counts: Commited=265369
> Target=276731 Hashed=249734
> InternalReservation=304 ExternalReservation=0 Min Free=128
> 2006-03-07 09:44:51.07 spid83 Procedure Cache: TotalProcs=2519
> TotalPages=8451 InUsePages=4915
> 2006-03-07 09:44:51.07 spid83 Dynamic Memory Manager: Stolen=9697 OS
> Reserved=1392
> OS Committed=1355
> OS In Use=1334
> Query Plan=8669 Optimizer=0
> General=1819
> Utilities=9 Connection=284
> 2006-03-07 09:44:51.07 spid83 Global Memory Objects: Resource=1493
> Locks=145
> SQLCache=146 Replication=2
> LockBytes=2 ServerGlobal=43
> Xact=29
> 2006-03-07 09:44:51.07 spid83 Query Memory Manager: Grants=0 Waiting=0
> Maximum=203051 Available=203051
> 2006-03-07 09:44:51.08 spid83 Error: 17803, Severity: 20, State: 12
> 2006-03-07 09:44:51.08 spid83 Insufficient memory available..
> 2006-03-07 09:48:35.73 spid53 WARNING: Failed to reserve contiguous
> memory of Size= 2883584.
> 2006-03-07 09:48:35.81 spid53 Buffer Distribution: Stolen=1852 Free=25
4
> Procedures=8279
> Inram=0 Dirty=8797 Kept=0
> I/O=0, Latched=123, Other=275965
> 2006-03-07 09:48:35.81 spid53 Buffer Counts: Commited=295270
> Target=295270 Hashed=284885
> InternalReservation=301 ExternalReservation=60 Min Free=280
> 2006-03-07 09:48:35.81 spid53 Procedure Cache: TotalProcs=2532
> TotalPages=8279 InUsePages=4576
> 2006-03-07 09:48:35.81 spid53 Dynamic Memory Manager: Stolen=10113 OS
> Reserved=1376|||There are at least a couple of situations that lead to this
error that are fixed in SP 4. You may also want to check the
network packet size being used by the clients. You can
capture this in profiler. See the following for more
information:
http://support.microsoft.com/?id=903002
Some processes that can affect the mem to leave space would
be sp_OA* procedures, most extended stored procedures, XML
operations.
-Sue
On Fri, 17 Mar 2006 02:23:57 -0800, David
<David@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Please could I have a response to this post - it has now been over 3 busine
ss
>days.
>I'm looking for help with where I should start my investigation - I don't
>understand the reason for the 'not enough storage space' message, as there
is
>plenty of storage space! The error logs suggest a problem with memory -
>'Failed to reserve contiguous memory of Size= 262144'. Presumably this is t
he
>underlying problem? If so, it must be due to something that has changed on
>all the servers, as we have now seen the problem on 2 of the 4 instances (1
>from each cluster). Should I be looking at the jobs that are running with a
>view to identifying memory leaks, or is it more likely to be due to somethi
ng
>that has changed at OS level - perhaps a patch that has been applied?
>Finally, does SQL Server SP4 contain anything to specifically fix this
>problem?
>At the moment, we're getting round the issue by stopping and restarting
>instances every night, which is obviously far from satisfactory.
>Regards,
>David.
>"David" wrote:
>sql

Failed to reserve contiguous memory

Hello,
(I have to repost it under different header since I didn't get any comment
or answer)
We faced a problem running PERL script which creates big XML file and
inserts it into ntext data type column of the table.
When XML file is relatively small – the process runs OK, but with big file
s
we get the following error appearing in the SQL server log:
2006-04-06 16:01:13.97 spid117 WARNING: Failed to reserve contiguous memory
of Size= 19333120.
2006-04-06 16:01:14.05 spid117 Query Memory Manager: Grants=5 Waiting=0
Maximum=152183 Available=151908
2006-04-06 16:01:14.05 spid117 Global Memory Objects: Resource=2921
Locks=29503 ...
2006-04-06 16:01:14.05 spid117 Dynamic Memory Manager: Stolen=34679 OS
Reserved=5272 ...
2006-04-06 16:01:14.05 spid117 Procedure Cache: TotalProcs=763
TotalPages=2142 InUsePages=899
2006-04-06 16:01:14.05 spid117 Buffer Counts: Commited=337792 Target=337792
Hashed=302526...
2006-04-06 16:01:14.05 spid117 Buffer Distribution: Stolen=32594 Free=530
Procedures=2142...
I checked several common things posted by Microsoft such as network packet
size and sql_variant data type – there is no any connection to our problem
.
Surprisingly, but the same insert works OK when performed from query
analyzer, even for a big files.
There quite amount of posts on the issue implying MemToLive
configuration, however this is a production server, I can't 'play' with it.
I would be very grateful for your help.
Thanks,If you can get the same hardware configuration and the same volume of
the same type of queries in a test environment, then you could probably
recreate your scenario there and "play" with fragmentation in
MemToLeave. When we called Microsoft, it came down to playing with the
-g parameter to increase the amount of memory allocated in MemToLeave
at startup. But still, that only works to a point because the
fragmentation isn't predictable - which is why it will work sometimes
(ie when you run it through QA) and fails at other times... We've just
had to push it back to developers to implement retries.
This just reinforces my opinion that all those nice little non-database
add-ons made to attract developers to SQL Server should really just
never be used...
BB|||If the same insert works ok when performed in QA, even for a big file.
That narrows the problem down to the PERL script rather. It could be a
time out issue. What library you use to connect to SQL Server in perl?
May be you should post more details in the PERL user group and see if
people have similar experiences.
Mel

Failed to reserve contiguous memory

Guys,
does anyone know what to do when you get this kind of
error message: My server is windows 2000 sp4, sql2000
sp3a, Here is the min and max memory configuration in
sp_config
min memory per query (KB) 512 2147483647
min server memory (MB) 0 2147483647
WARNING: Failed to reserve contiguous memory of Size= 65536.
Buffer Distribution: Stolen=4294948690 Free=412
Procedures=142775
Inram=0 Dirty=1232 Kept=0
I/O=0, Latched=41, Other=105186
Does anyone know what needs to be done. It happened
yester at 4pm and hasn't happened again. Let me know
please. Thanks.
Mary LouChances are it is related to one of these:
http://support.microsoft.com/default.aspx?scid=kb;en-us;832674&Product=sql2k
http://support.microsoft.com/default.aspx?scid=kb;en-us;818095&Product=sql2k
http://support.microsoft.com/default.aspx?scid=kb;en-us;316749&Product=sql2k
Andrew J. Kelly SQL MVP
"Mary Lou" <anonymous@.discussions.microsoft.com> wrote in message
news:197a01c4a25a$62bf7f50$a301280a@.phx.gbl...
> Guys,
> does anyone know what to do when you get this kind of
> error message: My server is windows 2000 sp4, sql2000
> sp3a, Here is the min and max memory configuration in
> sp_config
> min memory per query (KB) 512 2147483647
> min server memory (MB) 0 2147483647
>
> WARNING: Failed to reserve contiguous memory of Size=> 65536.
> Buffer Distribution: Stolen=4294948690 Free=412
> Procedures=142775
> Inram=0 Dirty=1232 Kept=0
> I/O=0, Latched=41, Other=105186
>
> Does anyone know what needs to be done. It happened
> yester at 4pm and hasn't happened again. Let me know
> please. Thanks.
> Mary Lou

Thursday, March 29, 2012

Failed to start jobs in sql agent after restart service

Failed to start jobs in sql agent after restart service

Failed to start a new trace - SQL PROFILER

Failed to set proper user name ('domain\username') for the connection

Failed to set proper user name ('domain\username') for the connection

Failed to set proper user name ('domain\username') for the connection

failed to search using d.DateCreated >='05/07/2007' AND d.DateCreated<= '05/07/2007'

Failed to script out Dropping Default Constraint script for table columns

Failed to schedule DTS in SQL 2005

Failed To Save Package File

Failed To Save Package File

Failed to retrieve long data for column

Failed to retrieve long data for column

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

Failed to retrieve data for this request.

failed to restore db from SQL 2005 to SQL 2000

failed to restore db from SQL 2005 to SQL 2000

Failed to reserve contiguous memory

Failed to reserve contiguous memory

Failed to reserve contiguous memory

Extracting Sql

Blog Archive

About Me