Has anyone seen anything like this before? One of our production servers lost
the ability establish any trusted connections list night from 5:47 until we
rebooted it this morning. From that point on all attempts for SQL jobs
running under domain ids on the system and any BizTalk processes trying to
connect failed with the message "Login failed for user '(null)'. Reason: Not
associated with a trusted SQL Server connection.". The couple jobs that we
have running under standard Standard users (other than sa) had no problems.
None of our other servers had problems and the network looked ok, so I don't
think it was a domain or network connectivity problem. The reboot resolved
the problem, so it doesn't look like anything related to the domain userids
themself had changed, just SQL suddenly started failing to be able to use
them.
Looking in the SQL Server Log I see the following messages logged (all from
the same spid) at the same time all this started. I'm interpreting these as
meaning the server ran out of memory, but can not think of any reason why
this could have caused trusted connections to fail from that point on. BTW -
We have the latest service packs and patches (ver 8.00.818) installed. Any
ideas? Has anyone else ever seen anything like this before?
Query Memory Manager: Grants=0 Waiting=0 Maximum=150632 Available=150632
Global Memory Objects: Resource=2705 Locks=93
SQLCache=102 Replication=2
LockBytes=2 ServerGlobal=45
Xact=215
Dynamic Memory Manager: Stolen=9211 OS Reserved=16632
OS Committed=16593
OS In Use=12841
Query Plan=3349 Optimizer=0
General=14718
Utilities=13 Connection=3870
Procedure Cache: TotalProcs=922 TotalPages=3400 InUsePages=1759
Buffer Counts: Commited=208408 Target=208408 Hashed=198709
InternalReservation=201 ExternalReservation=0 Min Free=128
Buffer Distribution: Stolen=5811 Free=488 Procedures=3400
Inram=0 Dirty=46274 Kept=0
I/O=0, Latched=35, Other=152400
WARNING: Failed to reserve contiguous memory of Size= 65536.
The message
WARNING: Failed to reserve contiguous memory of Size= 65536.
shows that you have some pressure it seems in your MemoryToleave area of
sql server's address pool.
Here are a couple of things regarding this error. Try tshooting this, else
you might want to call into PSS and open up a support case -
There are two main areas of memory within SQL Server's address space, the
buffer pool (BPool) and a second memory pool sometimes called the
"MemToLeave" area. The contents of the SQL Server buffer pool include
cached table data, workspace memory used during query execution for
in-memory sorts or hashes, most cached stored procedure and query plans,
memory for locks and other internal structures, and the majority of other
miscellaneous memory needs of the SQL Server. SQL Server 7.0 introduced
dynamic memory management to the buffer pool, which means that the amount
of memory under SQL Server's direct control may grow and shrink in response
to internal SQL Server needs and external memory pressure from other
applications. It is normal for the size of the SQL Server buffer pool to
increase over time until most memory on the server is consumed. This
design can give the false appearance of a memory leak in the SQL Server
buffer pool when operating under normal circumstances. For more detailed
information please reference the Books Online articles "Server Memory
Options", "Memory Architecture", and (SQL Server 2000 only) "Effects of min
and max server memory".
The other significant memory area is sometimes called MemToLeave, and it is
primarily used by non-SQL Server code that happens to be executing within
the SQL Server process. The MemToLeave area is memory that is left
unallocated and unreserved, primarily for code that is not part of the core
SQL Server and therefore does not know how to access memory in the SQL
Server buffer pool. Some examples of components that may use this memory
include extended stored procedures, OLE Automation/COM objects, linked
server OLEDB providers and ODBC drivers, MAPI components used by SQLMail,
and thread stacks (one-half MB per thread). This does not just include the
.EXE and .DLL binary images for these components; any memory allocated at
runtime by the components listed above will also be allocated from the
MemToLeave area. Non-SQL Server code makes its memory allocation requests
directly from the OS, not from the SQL Server buffer pool. The entire SQL
Server buffer pool is reserved at server startup, so any requests for
memory made directly from the operating system must be satisfied from the
MemToLeave area, which is the only source of unreserved memory in the SQL
Server address space. SQL Server itself also uses the MemToLeave memory
area for certain allocations; for example, SQL Server 7.0 stores procedure
plans in the MemToLeave area if they are too large for a single 8KB buffer
pool page.
How to Determine Whether the Error Points to a Memory Shortage in Buffer
Pool or MemToLeave
If the 17803 error in the SQL Server errorlog is accompanied by one of the
following error messages, the memory pressure is most likely in the
MemToLeave area (see section "Troubleshooting MemToLeave Memory Pressure"
below). If the messages below do not appear with the 17803, start from
section "Troubleshooting Buffer Pool Memory Pressure".
Errors that imply insufficient contiguous free memory in the MemToLeave
area:
WARNING: Failed to reserve contiguous memory of Size= 65536.
WARNING: Clearing procedure cache to free contiguous memory.
Error: 17802, Severity: 18, State: 3 Could not create server event
thread.
SQL Server could not spawn process_loginread thread
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' reported an error. The provider ran out of
memory.
Troubleshooting MemToLeave Memory Pressure
If the 17803 and associated errors point to a shortage of memory in
MemToLeave, the items below list some of the most common causes of this
type of problem and suggest ways to alleviate the issue.
- If the server runs many concurrent linked server queries against SQL
Servers using SQLOLEDB or MSDASQL, upgrade to MDAC 2.5 or MDAC 2.6 on
the server. The MDAC 2.5 and 2.6 versions of SQLOLEDB are more
conservative with their initial memory allocations.
Perhaps the most common cause of out-of-memory conditions in the MemToLeave
area is a memory leak in a non-SQL Server component running inside the SQL
address space. Items to check:
-Check the errorlog for messages like "Using 'abc.dll' version '123' to
execute extended stored procedure 'xp_abc'." If the dll referenced in this
message is not a Microsoft-provided DLL you may want to consider moving it
out of the production server's address space
-Determine whether COM objects are being executed inside SQL Server's
address space with the sp_OA stored procedures. If sp_OA is being used you
will see ODSOLE70.DLL which hosts sp_OACreate being loaded and the
following message in the errorlog:
Using 'odsole70.dll' version '2000.80.382' to execute extended stored
procedure 'sp_OACreate'.
If sp_OA stored procedures are being used, ensure that the COM objects
are being loaded out of process by passing 4 to the optional third
parameter for sp_OACreate (e.g. "EXEC sp_OACreate 'SQLDMO.SQLServer', @.obj
OUTPUT, 4").
-If linked servers using third-party OLEDB providers or ODBC drivers are
in use, these are also a possible cause of memory leaks. Evaluate whether
the linked servers can be disabled for a time as a troubleshooting step to
see whether this prevents the leak, or examine whether the provider is
still fully functional once it has been configured to run out of process by
setting the AllowInProcess registry value for the provider to 0 (the
AllowInProcess value can be found at HKLM\Software\Microsoft\MSSQLServer(or
MSSQL$instance key)\Providers\[ProviderName]).
- If the server supports heavy linked server activity or must run
memory-hungry non-SQL Server code inside the SQL Server process, you
may simply need to adjust the size of the MemToLeave area to make more
memory available to non-SQL Server memory consumers. "-g" is an
optional SQL Server startup parameter that can be used to increase the
size of the MemToLeave area. The default -g memory size is 128MB in SQL
Server 7.0 and 256MB in SQL Server 2000. You can increase the size of
the MemToLeave area by an additioal 128MB by adding -g256 (SQL 7.0) or
-g384 (SQL 2000) as a server startup parameter. This setting will take
effect the next time the SQL Server service is started. Startup
parameters are added in the "General" tab of the Server Properties
dialog in Enterprise Manager.
Troubleshooting Buffer Pool Memory Pressure
Because of SQL Server's dynamic memory managment, it is not unusual for a
significant leak in the MemToLeave area to initially manifest itself as a
shortage of buffers in buffer pool because SQL Server will dynamically
scale down the size of the buffer pool in response to the growing number of
bytes committed within the MemToLeave area. Similarly, a memory hungry or
leaking application running on the same box can cause SQL Server to release
almost all of its BPool memory, leading to a 17803. To rule out these two
alternatives and confirm that the problem is confined to BPool, start by
looking at the Performance Monitor log you collected.
If the counter "Process(sqlservr):Working Set" was much lower than the
amount of physical RAM on the server while the insufficient memory errors
were occurring, look for another process that holds most of the memory and
pursue that process as the root of the memory pressure.
If "Process(sqlservr):Working Set" accounts for most or nearly all of the
physical RAM on the server but if "SQL Server:Memory Manager:Target Server
Memory(KB)" is only a fraction of this amount of memory, the root cause of
the problem is likely a leak in MemToLeave that had the side effect of
causing skrinkage of BPool. In this case follow the suggestions in the
previous section "Troubleshooting MemToLeave Memory Pressure".
If the root cause of the problem is a leak in or excessive demand for bpool
memory, the following should generally be true:
- Buffer pool should have already been grown to its maximum size. In
other words, "SQL Server:Memory Manager:Total Server Memory(KB)" should
be equal to "SQL Server:Memory Manager:Target Server Memory(KB)".
- Buffer pool should consume the majority of physical RAM allocated to
the SQL process ("SQL Server:Memory Manager:Target Server Memory(KB)"
should account for the majority of "Process(sqlservr):Working Set".)
- There should be high lazywriter activity ("SQLServer:Buffer Manager -
Lazy Writer Buffers/sec"). If the problem appears to be BPool memory
pressure and you see no lazywriter activity, something may be blocking
lazywriter. Consider getting one or more DBCC STACKDUMPs while in this
state.
If you have determined that the problem is a leak or excessive demand for
buffer pool memory, use perfmon to determine what is consuming the most
buffer pages. Counters to examine include:
- "SQLServer:Buffer Manager - Cache Size (pages)" (procedure cache)
- "SQLServer:Cache Manager - Cache Pages(Adhoc/Cursor/Stored Proc/etc)"
- "SQLServer:Memory Manager - Granted Workspace Memory (KB)" (query
memory)
- "SQLServer:Memory Manager - SQL Cache Memory (KB)" (cached data pages)
- "SQLServer:Memory Manager - Lock Memory (KB)"
- "SQLServer:Memory Manager - Optimizer Memory (KB)"
- "SQLServer:Buffer Manager - Stolen Pages"
Hope that helps!
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
|||SQL Server cache's connection information, in the MEM TO LEAVE region. If
that was the process that was attempting to make a memory reservation in
this region, then that would account for your errors.
What is happening to cause this is too many external memory processes
executing on the server. Although SQL Server has wonderful Dynamic Memory
managers for the internal processes, it has to appeal to the OS to manage
the external reservation calls. Over time, the MEM TO LEAVE region get
fragmented and the only fix is to clear them segments. Thus, at least a
recycling of the SQL Server services if not a reboot of the system.
You can counter balance this by reducing the number of external process
calls and/or adjust the amount of memory left to the MEM TO LEAVE region
using the /g startup parameter. Howerver, the sizing of this parameter
needs to be adjusted with the assistance of the PSS staff.
A good first start, however, would be to increase from the default for this
parameter from 256 to 384, which represents MB removed from the Buffer Pool
allocation in addition to what is reserved for the UMS Worker threads, 128
MB with the default 256 threads configuration.
Sincerely,
Anthony Thomas
"Jeff Turlington" <Jeff Turlington@.discussions.microsoft.com> wrote in
message news:D9783602-FAD2-4730-BB96-FB7CD2438605@.microsoft.com...
Has anyone seen anything like this before? One of our production servers
lost
the ability establish any trusted connections list night from 5:47 until we
rebooted it this morning. From that point on all attempts for SQL jobs
running under domain ids on the system and any BizTalk processes trying to
connect failed with the message "Login failed for user '(null)'. Reason: Not
associated with a trusted SQL Server connection.". The couple jobs that we
have running under standard Standard users (other than sa) had no problems.
None of our other servers had problems and the network looked ok, so I don't
think it was a domain or network connectivity problem. The reboot resolved
the problem, so it doesn't look like anything related to the domain userids
themself had changed, just SQL suddenly started failing to be able to use
them.
Looking in the SQL Server Log I see the following messages logged (all from
the same spid) at the same time all this started. I'm interpreting these as
meaning the server ran out of memory, but can not think of any reason why
this could have caused trusted connections to fail from that point on. BTW -
We have the latest service packs and patches (ver 8.00.818) installed. Any
ideas? Has anyone else ever seen anything like this before?
Query Memory Manager: Grants=0 Waiting=0 Maximum=150632 Available=150632
Global Memory Objects: Resource=2705 Locks=93
SQLCache=102 Replication=2
LockBytes=2 ServerGlobal=45
Xact=215
Dynamic Memory Manager: Stolen=9211 OS Reserved=16632
OS Committed=16593
OS In Use=12841
Query Plan=3349 Optimizer=0
General=14718
Utilities=13 Connection=3870
Procedure Cache: TotalProcs=922 TotalPages=3400 InUsePages=1759
Buffer Counts: Commited=208408 Target=208408 Hashed=198709
InternalReservation=201 ExternalReservation=0 Min Free=128
Buffer Distribution: Stolen=5811 Free=488 Procedures=3400
Inram=0 Dirty=46274 Kept=0
I/O=0, Latched=35, Other=152400
WARNING: Failed to reserve contiguous memory of Size= 65536.
Showing posts with label production. Show all posts
Showing posts with label production. Show all posts
Thursday, March 22, 2012
Couldn't establish trusted connections after SQL memory problems
Has anyone seen anything like this before? One of our production servers los
t
the ability establish any trusted connections list night from 5:47 until we
rebooted it this morning. From that point on all attempts for SQL jobs
running under domain ids on the system and any BizTalk processes trying to
connect failed with the message "Login failed for user '(null)'. Reason: Not
associated with a trusted SQL Server connection.". The couple jobs that we
have running under standard Standard users (other than sa) had no problems.
None of our other servers had problems and the network looked ok, so I don't
think it was a domain or network connectivity problem. The reboot resolved
the problem, so it doesn't look like anything related to the domain userids
themself had changed, just SQL suddenly started failing to be able to use
them.
Looking in the SQL Server Log I see the following messages logged (all from
the same spid) at the same time all this started. I'm interpreting these as
meaning the server ran out of memory, but can not think of any reason why
this could have caused trusted connections to fail from that point on. BTW -
We have the latest service packs and patches (ver 8.00.818) installed. Any
ideas? Has anyone else ever seen anything like this before?
Query Memory Manager: Grants=0 Waiting=0 Maximum=150632 Available=150632
Global Memory Objects: Resource=2705 Locks=93
SQLCache=102 Replication=2
LockBytes=2 ServerGlobal=45
Xact=215
Dynamic Memory Manager: Stolen=9211 OS Reserved=16632
OS Committed=16593
OS In Use=12841
Query Plan=3349 Optimizer=0
General=14718
Utilities=13 Connection=3870
Procedure Cache: TotalProcs=922 TotalPages=3400 InUsePages=1759
Buffer Counts: Commited=208408 Target=208408 Hashed=198709
InternalReservation=201 ExternalReservation=0 Min Free=128
Buffer Distribution: Stolen=5811 Free=488 Procedures=3400
Inram=0 Dirty=46274 Kept=0
I/O=0, Latched=35, Other=152400
WARNING: Failed to reserve contiguous memory of Size= 65536.The message
WARNING: Failed to reserve contiguous memory of Size= 65536.
shows that you have some pressure it seems in your MemoryToleave area of
sql server's address pool.
Here are a couple of things regarding this error. Try tshooting this, else
you might want to call into PSS and open up a support case -
There are two main areas of memory within SQL Server's address space, the
buffer pool (BPool) and a second memory pool sometimes called the
"MemToLeave" area. The contents of the SQL Server buffer pool include
cached table data, workspace memory used during query execution for
in-memory sorts or hashes, most cached stored procedure and query plans,
memory for locks and other internal structures, and the majority of other
miscellaneous memory needs of the SQL Server. SQL Server 7.0 introduced
dynamic memory management to the buffer pool, which means that the amount
of memory under SQL Server's direct control may grow and shrink in response
to internal SQL Server needs and external memory pressure from other
applications. It is normal for the size of the SQL Server buffer pool to
increase over time until most memory on the server is consumed. This
design can give the false appearance of a memory leak in the SQL Server
buffer pool when operating under normal circumstances. For more detailed
information please reference the Books Online articles "Server Memory
Options", "Memory Architecture", and (SQL Server 2000 only) "Effects of min
and max server memory".
The other significant memory area is sometimes called MemToLeave, and it is
primarily used by non-SQL Server code that happens to be executing within
the SQL Server process. The MemToLeave area is memory that is left
unallocated and unreserved, primarily for code that is not part of the core
SQL Server and therefore does not know how to access memory in the SQL
Server buffer pool. Some examples of components that may use this memory
include extended stored procedures, OLE Automation/COM objects, linked
server OLEDB providers and ODBC drivers, MAPI components used by SQLMail,
and thread stacks (one-half MB per thread). This does not just include the
.EXE and .DLL binary images for these components; any memory allocated at
runtime by the components listed above will also be allocated from the
MemToLeave area. Non-SQL Server code makes its memory allocation requests
directly from the OS, not from the SQL Server buffer pool. The entire SQL
Server buffer pool is reserved at server startup, so any requests for
memory made directly from the operating system must be satisfied from the
MemToLeave area, which is the only source of unreserved memory in the SQL
Server address space. SQL Server itself also uses the MemToLeave memory
area for certain allocations; for example, SQL Server 7.0 stores procedure
plans in the MemToLeave area if they are too large for a single 8KB buffer
pool page.
How to Determine Whether the Error Points to a Memory Shortage in Buffer
Pool or MemToLeave
If the 17803 error in the SQL Server errorlog is accompanied by one of the
following error messages, the memory pressure is most likely in the
MemToLeave area (see section "Troubleshooting MemToLeave Memory Pressure"
below). If the messages below do not appear with the 17803, start from
section "Troubleshooting Buffer Pool Memory Pressure".
Errors that imply insufficient contiguous free memory in the MemToLeave
area:
WARNING: Failed to reserve contiguous memory of Size= 65536.
WARNING: Clearing procedure cache to free contiguous memory.
Error: 17802, Severity: 18, State: 3 Could not create server event
thread.
SQL Server could not spawn process_loginread thread
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' reported an error. The provider ran out of
memory.
Troubleshooting MemToLeave Memory Pressure
If the 17803 and associated errors point to a shortage of memory in
MemToLeave, the items below list some of the most common causes of this
type of problem and suggest ways to alleviate the issue.
- If the server runs many concurrent linked server queries against SQL
Servers using SQLOLEDB or MSDASQL, upgrade to MDAC 2.5 or MDAC 2.6 on
the server. The MDAC 2.5 and 2.6 versions of SQLOLEDB are more
conservative with their initial memory allocations.
Perhaps the most common cause of out-of-memory conditions in the MemToLeave
area is a memory leak in a non-SQL Server component running inside the SQL
address space. Items to check:
- Check the errorlog for messages like "Using 'abc.dll' version '123' to
execute extended stored procedure 'xp_abc'." If the dll referenced in this
message is not a Microsoft-provided DLL you may want to consider moving it
out of the production server's address space
- Determine whether COM objects are being executed inside SQL Server's
address space with the sp_OA stored procedures. If sp_OA is being used you
will see ODSOLE70.DLL which hosts sp_OACreate being loaded and the
following message in the errorlog:
Using 'odsole70.dll' version '2000.80.382' to execute extended stored
procedure 'sp_OACreate'.
If sp_OA stored procedures are being used, ensure that the COM objects
are being loaded out of process by passing 4 to the optional third
parameter for sp_OACreate (e.g. "EXEC sp_OACreate 'SQLDMO.SQLServer', @.obj
OUTPUT, 4").
- If linked servers using third-party OLEDB providers or ODBC drivers are
in use, these are also a possible cause of memory leaks. Evaluate whether
the linked servers can be disabled for a time as a troubleshooting step to
see whether this prevents the leak, or examine whether the provider is
still fully functional once it has been configured to run out of process by
setting the AllowInProcess registry value for the provider to 0 (the
AllowInProcess value can be found at HKLM\Software\Microsoft\MSSQLServer(or
MSSQL$instance key)\Providers\[ProviderName]).
- If the server supports heavy linked server activity or must run
memory-hungry non-SQL Server code inside the SQL Server process, you
may simply need to adjust the size of the MemToLeave area to make more
memory available to non-SQL Server memory consumers. "-g" is an
optional SQL Server startup parameter that can be used to increase the
size of the MemToLeave area. The default -g memory size is 128MB in SQL
Server 7.0 and 256MB in SQL Server 2000. You can increase the size of
the MemToLeave area by an additioal 128MB by adding -g256 (SQL 7.0) or
-g384 (SQL 2000) as a server startup parameter. This setting will take
effect the next time the SQL Server service is started. Startup
parameters are added in the "General" tab of the Server Properties
dialog in Enterprise Manager.
Troubleshooting Buffer Pool Memory Pressure
Because of SQL Server's dynamic memory managment, it is not unusual for a
significant leak in the MemToLeave area to initially manifest itself as a
shortage of buffers in buffer pool because SQL Server will dynamically
scale down the size of the buffer pool in response to the growing number of
bytes committed within the MemToLeave area. Similarly, a memory hungry or
leaking application running on the same box can cause SQL Server to release
almost all of its BPool memory, leading to a 17803. To rule out these two
alternatives and confirm that the problem is confined to BPool, start by
looking at the Performance Monitor log you collected.
If the counter "Process(sqlservr):Working Set" was much lower than the
amount of physical RAM on the server while the insufficient memory errors
were occurring, look for another process that holds most of the memory and
pursue that process as the root of the memory pressure.
If "Process(sqlservr):Working Set" accounts for most or nearly all of the
physical RAM on the server but if "SQL Server:Memory Manager:Target Server
Memory(KB)" is only a fraction of this amount of memory, the root cause of
the problem is likely a leak in MemToLeave that had the side effect of
causing skrinkage of BPool. In this case follow the suggestions in the
previous section "Troubleshooting MemToLeave Memory Pressure".
If the root cause of the problem is a leak in or excessive demand for bpool
memory, the following should generally be true:
- Buffer pool should have already been grown to its maximum size. In
other words, "SQL Server:Memory Manager:Total Server Memory(KB)" should
be equal to "SQL Server:Memory Manager:Target Server Memory(KB)".
- Buffer pool should consume the majority of physical RAM allocated to
the SQL process ("SQL Server:Memory Manager:Target Server Memory(KB)"
should account for the majority of "Process(sqlservr):Working Set".)
- There should be high lazywriter activity ("SQLServer:Buffer Manager -
Lazy Writer Buffers/sec"). If the problem appears to be BPool memory
pressure and you see no lazywriter activity, something may be blocking
lazywriter. Consider getting one or more DBCC STACKDUMPs while in this
state.
If you have determined that the problem is a leak or excessive demand for
buffer pool memory, use perfmon to determine what is consuming the most
buffer pages. Counters to examine include:
- "SQLServer:Buffer Manager - Cache Size (pages)" (procedure cache)
- "SQLServer:Cache Manager - Cache Pages(Adhoc/Cursor/Stored Proc/etc)"
- "SQLServer:Memory Manager - Granted Workspace Memory (KB)" (query
memory)
- "SQLServer:Memory Manager - SQL Cache Memory (KB)" (cached data pages)
- "SQLServer:Memory Manager - Lock Memory (KB)"
- "SQLServer:Memory Manager - Optimizer Memory (KB)"
- "SQLServer:Buffer Manager - Stolen Pages"
Hope that helps!
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.|||SQL Server cache's connection information, in the MEM TO LEAVE region. If
that was the process that was attempting to make a memory reservation in
this region, then that would account for your errors.
What is happening to cause this is too many external memory processes
executing on the server. Although SQL Server has wonderful Dynamic Memory
managers for the internal processes, it has to appeal to the OS to manage
the external reservation calls. Over time, the MEM TO LEAVE region get
fragmented and the only fix is to clear them segments. Thus, at least a
recycling of the SQL Server services if not a reboot of the system.
You can counter balance this by reducing the number of external process
calls and/or adjust the amount of memory left to the MEM TO LEAVE region
using the /g startup parameter. Howerver, the sizing of this parameter
needs to be adjusted with the assistance of the PSS staff.
A good first start, however, would be to increase from the default for this
parameter from 256 to 384, which represents MB removed from the Buffer Pool
allocation in addition to what is reserved for the UMS Worker threads, 128
MB with the default 256 threads configuration.
Sincerely,
Anthony Thomas
"Jeff Turlington" <Jeff Turlington@.discussions.microsoft.com> wrote in
message news:D9783602-FAD2-4730-BB96-FB7CD2438605@.microsoft.com...
Has anyone seen anything like this before? One of our production servers
lost
the ability establish any trusted connections list night from 5:47 until we
rebooted it this morning. From that point on all attempts for SQL jobs
running under domain ids on the system and any BizTalk processes trying to
connect failed with the message "Login failed for user '(null)'. Reason: Not
associated with a trusted SQL Server connection.". The couple jobs that we
have running under standard Standard users (other than sa) had no problems.
None of our other servers had problems and the network looked ok, so I don't
think it was a domain or network connectivity problem. The reboot resolved
the problem, so it doesn't look like anything related to the domain userids
themself had changed, just SQL suddenly started failing to be able to use
them.
Looking in the SQL Server Log I see the following messages logged (all from
the same spid) at the same time all this started. I'm interpreting these as
meaning the server ran out of memory, but can not think of any reason why
this could have caused trusted connections to fail from that point on. BTW -
We have the latest service packs and patches (ver 8.00.818) installed. Any
ideas? Has anyone else ever seen anything like this before?
Query Memory Manager: Grants=0 Waiting=0 Maximum=150632 Available=150632
Global Memory Objects: Resource=2705 Locks=93
SQLCache=102 Replication=2
LockBytes=2 ServerGlobal=45
Xact=215
Dynamic Memory Manager: Stolen=9211 OS Reserved=16632
OS Committed=16593
OS In Use=12841
Query Plan=3349 Optimizer=0
General=14718
Utilities=13 Connection=3870
Procedure Cache: TotalProcs=922 TotalPages=3400 InUsePages=1759
Buffer Counts: Commited=208408 Target=208408 Hashed=198709
InternalReservation=201 ExternalReservation=0 Min Free=128
Buffer Distribution: Stolen=5811 Free=488 Procedures=3400
Inram=0 Dirty=46274 Kept=0
I/O=0, Latched=35, Other=152400
WARNING: Failed to reserve contiguous memory of Size= 65536.
t
the ability establish any trusted connections list night from 5:47 until we
rebooted it this morning. From that point on all attempts for SQL jobs
running under domain ids on the system and any BizTalk processes trying to
connect failed with the message "Login failed for user '(null)'. Reason: Not
associated with a trusted SQL Server connection.". The couple jobs that we
have running under standard Standard users (other than sa) had no problems.
None of our other servers had problems and the network looked ok, so I don't
think it was a domain or network connectivity problem. The reboot resolved
the problem, so it doesn't look like anything related to the domain userids
themself had changed, just SQL suddenly started failing to be able to use
them.
Looking in the SQL Server Log I see the following messages logged (all from
the same spid) at the same time all this started. I'm interpreting these as
meaning the server ran out of memory, but can not think of any reason why
this could have caused trusted connections to fail from that point on. BTW -
We have the latest service packs and patches (ver 8.00.818) installed. Any
ideas? Has anyone else ever seen anything like this before?
Query Memory Manager: Grants=0 Waiting=0 Maximum=150632 Available=150632
Global Memory Objects: Resource=2705 Locks=93
SQLCache=102 Replication=2
LockBytes=2 ServerGlobal=45
Xact=215
Dynamic Memory Manager: Stolen=9211 OS Reserved=16632
OS Committed=16593
OS In Use=12841
Query Plan=3349 Optimizer=0
General=14718
Utilities=13 Connection=3870
Procedure Cache: TotalProcs=922 TotalPages=3400 InUsePages=1759
Buffer Counts: Commited=208408 Target=208408 Hashed=198709
InternalReservation=201 ExternalReservation=0 Min Free=128
Buffer Distribution: Stolen=5811 Free=488 Procedures=3400
Inram=0 Dirty=46274 Kept=0
I/O=0, Latched=35, Other=152400
WARNING: Failed to reserve contiguous memory of Size= 65536.The message
WARNING: Failed to reserve contiguous memory of Size= 65536.
shows that you have some pressure it seems in your MemoryToleave area of
sql server's address pool.
Here are a couple of things regarding this error. Try tshooting this, else
you might want to call into PSS and open up a support case -
There are two main areas of memory within SQL Server's address space, the
buffer pool (BPool) and a second memory pool sometimes called the
"MemToLeave" area. The contents of the SQL Server buffer pool include
cached table data, workspace memory used during query execution for
in-memory sorts or hashes, most cached stored procedure and query plans,
memory for locks and other internal structures, and the majority of other
miscellaneous memory needs of the SQL Server. SQL Server 7.0 introduced
dynamic memory management to the buffer pool, which means that the amount
of memory under SQL Server's direct control may grow and shrink in response
to internal SQL Server needs and external memory pressure from other
applications. It is normal for the size of the SQL Server buffer pool to
increase over time until most memory on the server is consumed. This
design can give the false appearance of a memory leak in the SQL Server
buffer pool when operating under normal circumstances. For more detailed
information please reference the Books Online articles "Server Memory
Options", "Memory Architecture", and (SQL Server 2000 only) "Effects of min
and max server memory".
The other significant memory area is sometimes called MemToLeave, and it is
primarily used by non-SQL Server code that happens to be executing within
the SQL Server process. The MemToLeave area is memory that is left
unallocated and unreserved, primarily for code that is not part of the core
SQL Server and therefore does not know how to access memory in the SQL
Server buffer pool. Some examples of components that may use this memory
include extended stored procedures, OLE Automation/COM objects, linked
server OLEDB providers and ODBC drivers, MAPI components used by SQLMail,
and thread stacks (one-half MB per thread). This does not just include the
.EXE and .DLL binary images for these components; any memory allocated at
runtime by the components listed above will also be allocated from the
MemToLeave area. Non-SQL Server code makes its memory allocation requests
directly from the OS, not from the SQL Server buffer pool. The entire SQL
Server buffer pool is reserved at server startup, so any requests for
memory made directly from the operating system must be satisfied from the
MemToLeave area, which is the only source of unreserved memory in the SQL
Server address space. SQL Server itself also uses the MemToLeave memory
area for certain allocations; for example, SQL Server 7.0 stores procedure
plans in the MemToLeave area if they are too large for a single 8KB buffer
pool page.
How to Determine Whether the Error Points to a Memory Shortage in Buffer
Pool or MemToLeave
If the 17803 error in the SQL Server errorlog is accompanied by one of the
following error messages, the memory pressure is most likely in the
MemToLeave area (see section "Troubleshooting MemToLeave Memory Pressure"
below). If the messages below do not appear with the 17803, start from
section "Troubleshooting Buffer Pool Memory Pressure".
Errors that imply insufficient contiguous free memory in the MemToLeave
area:
WARNING: Failed to reserve contiguous memory of Size= 65536.
WARNING: Clearing procedure cache to free contiguous memory.
Error: 17802, Severity: 18, State: 3 Could not create server event
thread.
SQL Server could not spawn process_loginread thread
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' reported an error. The provider ran out of
memory.
Troubleshooting MemToLeave Memory Pressure
If the 17803 and associated errors point to a shortage of memory in
MemToLeave, the items below list some of the most common causes of this
type of problem and suggest ways to alleviate the issue.
- If the server runs many concurrent linked server queries against SQL
Servers using SQLOLEDB or MSDASQL, upgrade to MDAC 2.5 or MDAC 2.6 on
the server. The MDAC 2.5 and 2.6 versions of SQLOLEDB are more
conservative with their initial memory allocations.
Perhaps the most common cause of out-of-memory conditions in the MemToLeave
area is a memory leak in a non-SQL Server component running inside the SQL
address space. Items to check:
- Check the errorlog for messages like "Using 'abc.dll' version '123' to
execute extended stored procedure 'xp_abc'." If the dll referenced in this
message is not a Microsoft-provided DLL you may want to consider moving it
out of the production server's address space
- Determine whether COM objects are being executed inside SQL Server's
address space with the sp_OA stored procedures. If sp_OA is being used you
will see ODSOLE70.DLL which hosts sp_OACreate being loaded and the
following message in the errorlog:
Using 'odsole70.dll' version '2000.80.382' to execute extended stored
procedure 'sp_OACreate'.
If sp_OA stored procedures are being used, ensure that the COM objects
are being loaded out of process by passing 4 to the optional third
parameter for sp_OACreate (e.g. "EXEC sp_OACreate 'SQLDMO.SQLServer', @.obj
OUTPUT, 4").
- If linked servers using third-party OLEDB providers or ODBC drivers are
in use, these are also a possible cause of memory leaks. Evaluate whether
the linked servers can be disabled for a time as a troubleshooting step to
see whether this prevents the leak, or examine whether the provider is
still fully functional once it has been configured to run out of process by
setting the AllowInProcess registry value for the provider to 0 (the
AllowInProcess value can be found at HKLM\Software\Microsoft\MSSQLServer(or
MSSQL$instance key)\Providers\[ProviderName]).
- If the server supports heavy linked server activity or must run
memory-hungry non-SQL Server code inside the SQL Server process, you
may simply need to adjust the size of the MemToLeave area to make more
memory available to non-SQL Server memory consumers. "-g" is an
optional SQL Server startup parameter that can be used to increase the
size of the MemToLeave area. The default -g memory size is 128MB in SQL
Server 7.0 and 256MB in SQL Server 2000. You can increase the size of
the MemToLeave area by an additioal 128MB by adding -g256 (SQL 7.0) or
-g384 (SQL 2000) as a server startup parameter. This setting will take
effect the next time the SQL Server service is started. Startup
parameters are added in the "General" tab of the Server Properties
dialog in Enterprise Manager.
Troubleshooting Buffer Pool Memory Pressure
Because of SQL Server's dynamic memory managment, it is not unusual for a
significant leak in the MemToLeave area to initially manifest itself as a
shortage of buffers in buffer pool because SQL Server will dynamically
scale down the size of the buffer pool in response to the growing number of
bytes committed within the MemToLeave area. Similarly, a memory hungry or
leaking application running on the same box can cause SQL Server to release
almost all of its BPool memory, leading to a 17803. To rule out these two
alternatives and confirm that the problem is confined to BPool, start by
looking at the Performance Monitor log you collected.
If the counter "Process(sqlservr):Working Set" was much lower than the
amount of physical RAM on the server while the insufficient memory errors
were occurring, look for another process that holds most of the memory and
pursue that process as the root of the memory pressure.
If "Process(sqlservr):Working Set" accounts for most or nearly all of the
physical RAM on the server but if "SQL Server:Memory Manager:Target Server
Memory(KB)" is only a fraction of this amount of memory, the root cause of
the problem is likely a leak in MemToLeave that had the side effect of
causing skrinkage of BPool. In this case follow the suggestions in the
previous section "Troubleshooting MemToLeave Memory Pressure".
If the root cause of the problem is a leak in or excessive demand for bpool
memory, the following should generally be true:
- Buffer pool should have already been grown to its maximum size. In
other words, "SQL Server:Memory Manager:Total Server Memory(KB)" should
be equal to "SQL Server:Memory Manager:Target Server Memory(KB)".
- Buffer pool should consume the majority of physical RAM allocated to
the SQL process ("SQL Server:Memory Manager:Target Server Memory(KB)"
should account for the majority of "Process(sqlservr):Working Set".)
- There should be high lazywriter activity ("SQLServer:Buffer Manager -
Lazy Writer Buffers/sec"). If the problem appears to be BPool memory
pressure and you see no lazywriter activity, something may be blocking
lazywriter. Consider getting one or more DBCC STACKDUMPs while in this
state.
If you have determined that the problem is a leak or excessive demand for
buffer pool memory, use perfmon to determine what is consuming the most
buffer pages. Counters to examine include:
- "SQLServer:Buffer Manager - Cache Size (pages)" (procedure cache)
- "SQLServer:Cache Manager - Cache Pages(Adhoc/Cursor/Stored Proc/etc)"
- "SQLServer:Memory Manager - Granted Workspace Memory (KB)" (query
memory)
- "SQLServer:Memory Manager - SQL Cache Memory (KB)" (cached data pages)
- "SQLServer:Memory Manager - Lock Memory (KB)"
- "SQLServer:Memory Manager - Optimizer Memory (KB)"
- "SQLServer:Buffer Manager - Stolen Pages"
Hope that helps!
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.|||SQL Server cache's connection information, in the MEM TO LEAVE region. If
that was the process that was attempting to make a memory reservation in
this region, then that would account for your errors.
What is happening to cause this is too many external memory processes
executing on the server. Although SQL Server has wonderful Dynamic Memory
managers for the internal processes, it has to appeal to the OS to manage
the external reservation calls. Over time, the MEM TO LEAVE region get
fragmented and the only fix is to clear them segments. Thus, at least a
recycling of the SQL Server services if not a reboot of the system.
You can counter balance this by reducing the number of external process
calls and/or adjust the amount of memory left to the MEM TO LEAVE region
using the /g startup parameter. Howerver, the sizing of this parameter
needs to be adjusted with the assistance of the PSS staff.
A good first start, however, would be to increase from the default for this
parameter from 256 to 384, which represents MB removed from the Buffer Pool
allocation in addition to what is reserved for the UMS Worker threads, 128
MB with the default 256 threads configuration.
Sincerely,
Anthony Thomas
"Jeff Turlington" <Jeff Turlington@.discussions.microsoft.com> wrote in
message news:D9783602-FAD2-4730-BB96-FB7CD2438605@.microsoft.com...
Has anyone seen anything like this before? One of our production servers
lost
the ability establish any trusted connections list night from 5:47 until we
rebooted it this morning. From that point on all attempts for SQL jobs
running under domain ids on the system and any BizTalk processes trying to
connect failed with the message "Login failed for user '(null)'. Reason: Not
associated with a trusted SQL Server connection.". The couple jobs that we
have running under standard Standard users (other than sa) had no problems.
None of our other servers had problems and the network looked ok, so I don't
think it was a domain or network connectivity problem. The reboot resolved
the problem, so it doesn't look like anything related to the domain userids
themself had changed, just SQL suddenly started failing to be able to use
them.
Looking in the SQL Server Log I see the following messages logged (all from
the same spid) at the same time all this started. I'm interpreting these as
meaning the server ran out of memory, but can not think of any reason why
this could have caused trusted connections to fail from that point on. BTW -
We have the latest service packs and patches (ver 8.00.818) installed. Any
ideas? Has anyone else ever seen anything like this before?
Query Memory Manager: Grants=0 Waiting=0 Maximum=150632 Available=150632
Global Memory Objects: Resource=2705 Locks=93
SQLCache=102 Replication=2
LockBytes=2 ServerGlobal=45
Xact=215
Dynamic Memory Manager: Stolen=9211 OS Reserved=16632
OS Committed=16593
OS In Use=12841
Query Plan=3349 Optimizer=0
General=14718
Utilities=13 Connection=3870
Procedure Cache: TotalProcs=922 TotalPages=3400 InUsePages=1759
Buffer Counts: Commited=208408 Target=208408 Hashed=198709
InternalReservation=201 ExternalReservation=0 Min Free=128
Buffer Distribution: Stolen=5811 Free=488 Procedures=3400
Inram=0 Dirty=46274 Kept=0
I/O=0, Latched=35, Other=152400
WARNING: Failed to reserve contiguous memory of Size= 65536.
Couldn't establish trusted connections after SQL memory problems
Has anyone seen anything like this before? One of our production servers lost
the ability establish any trusted connections list night from 5:47 until we
rebooted it this morning. From that point on all attempts for SQL jobs
running under domain ids on the system and any BizTalk processes trying to
connect failed with the message "Login failed for user '(null)'. Reason: Not
associated with a trusted SQL Server connection.". The couple jobs that we
have running under standard Standard users (other than sa) had no problems.
None of our other servers had problems and the network looked ok, so I don't
think it was a domain or network connectivity problem. The reboot resolved
the problem, so it doesn't look like anything related to the domain userids
themself had changed, just SQL suddenly started failing to be able to use
them.
Looking in the SQL Server Log I see the following messages logged (all from
the same spid) at the same time all this started. I'm interpreting these as
meaning the server ran out of memory, but can not think of any reason why
this could have caused trusted connections to fail from that point on. BTW -
We have the latest service packs and patches (ver 8.00.818) installed. Any
ideas? Has anyone else ever seen anything like this before?
Query Memory Manager: Grants=0 Waiting=0 Maximum=150632 Available=150632
Global Memory Objects: Resource=2705 Locks=93
SQLCache=102 Replication=2
LockBytes=2 ServerGlobal=45
Xact=215
Dynamic Memory Manager: Stolen=9211 OS Reserved=16632
OS Committed=16593
OS In Use=12841
Query Plan=3349 Optimizer=0
General=14718
Utilities=13 Connection=3870
Procedure Cache: TotalProcs=922 TotalPages=3400 InUsePages=1759
Buffer Counts: Commited=208408 Target=208408 Hashed=198709
InternalReservation=201 ExternalReservation=0 Min Free=128
Buffer Distribution: Stolen=5811 Free=488 Procedures=3400
Inram=0 Dirty=46274 Kept=0
I/O=0, Latched=35, Other=152400
WARNING: Failed to reserve contiguous memory of Size= 65536.The message
WARNING: Failed to reserve contiguous memory of Size= 65536.
shows that you have some pressure it seems in your MemoryToleave area of
sql server's address pool.
Here are a couple of things regarding this error. Try tshooting this, else
you might want to call into PSS and open up a support case -
There are two main areas of memory within SQL Server's address space, the
buffer pool (BPool) and a second memory pool sometimes called the
"MemToLeave" area. The contents of the SQL Server buffer pool include
cached table data, workspace memory used during query execution for
in-memory sorts or hashes, most cached stored procedure and query plans,
memory for locks and other internal structures, and the majority of other
miscellaneous memory needs of the SQL Server. SQL Server 7.0 introduced
dynamic memory management to the buffer pool, which means that the amount
of memory under SQL Server's direct control may grow and shrink in response
to internal SQL Server needs and external memory pressure from other
applications. It is normal for the size of the SQL Server buffer pool to
increase over time until most memory on the server is consumed. This
design can give the false appearance of a memory leak in the SQL Server
buffer pool when operating under normal circumstances. For more detailed
information please reference the Books Online articles "Server Memory
Options", "Memory Architecture", and (SQL Server 2000 only) "Effects of min
and max server memory".
The other significant memory area is sometimes called MemToLeave, and it is
primarily used by non-SQL Server code that happens to be executing within
the SQL Server process. The MemToLeave area is memory that is left
unallocated and unreserved, primarily for code that is not part of the core
SQL Server and therefore does not know how to access memory in the SQL
Server buffer pool. Some examples of components that may use this memory
include extended stored procedures, OLE Automation/COM objects, linked
server OLEDB providers and ODBC drivers, MAPI components used by SQLMail,
and thread stacks (one-half MB per thread). This does not just include the
EXE and .DLL binary images for these components; any memory allocated at
runtime by the components listed above will also be allocated from the
MemToLeave area. Non-SQL Server code makes its memory allocation requests
directly from the OS, not from the SQL Server buffer pool. The entire SQL
Server buffer pool is reserved at server startup, so any requests for
memory made directly from the operating system must be satisfied from the
MemToLeave area, which is the only source of unreserved memory in the SQL
Server address space. SQL Server itself also uses the MemToLeave memory
area for certain allocations; for example, SQL Server 7.0 stores procedure
plans in the MemToLeave area if they are too large for a single 8KB buffer
pool page.
How to Determine Whether the Error Points to a Memory Shortage in Buffer
Pool or MemToLeave
If the 17803 error in the SQL Server errorlog is accompanied by one of the
following error messages, the memory pressure is most likely in the
MemToLeave area (see section "Troubleshooting MemToLeave Memory Pressure"
below). If the messages below do not appear with the 17803, start from
section "Troubleshooting Buffer Pool Memory Pressure".
Errors that imply insufficient contiguous free memory in the MemToLeave
area:
WARNING: Failed to reserve contiguous memory of Size= 65536.
WARNING: Clearing procedure cache to free contiguous memory.
Error: 17802, Severity: 18, State: 3 Could not create server event
thread.
SQL Server could not spawn process_loginread thread
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' reported an error. The provider ran out of
memory.
Troubleshooting MemToLeave Memory Pressure
If the 17803 and associated errors point to a shortage of memory in
MemToLeave, the items below list some of the most common causes of this
type of problem and suggest ways to alleviate the issue.
- If the server runs many concurrent linked server queries against SQL
Servers using SQLOLEDB or MSDASQL, upgrade to MDAC 2.5 or MDAC 2.6 on
the server. The MDAC 2.5 and 2.6 versions of SQLOLEDB are more
conservative with their initial memory allocations.
Perhaps the most common cause of out-of-memory conditions in the MemToLeave
area is a memory leak in a non-SQL Server component running inside the SQL
address space. Items to check:
- Check the errorlog for messages like "Using 'abc.dll' version '123' to
execute extended stored procedure 'xp_abc'." If the dll referenced in this
message is not a Microsoft-provided DLL you may want to consider moving it
out of the production server's address space
- Determine whether COM objects are being executed inside SQL Server's
address space with the sp_OA stored procedures. If sp_OA is being used you
will see ODSOLE70.DLL which hosts sp_OACreate being loaded and the
following message in the errorlog:
Using 'odsole70.dll' version '2000.80.382' to execute extended stored
procedure 'sp_OACreate'.
If sp_OA stored procedures are being used, ensure that the COM objects
are being loaded out of process by passing 4 to the optional third
parameter for sp_OACreate (e.g. "EXEC sp_OACreate 'SQLDMO.SQLServer', @.obj
OUTPUT, 4").
- If linked servers using third-party OLEDB providers or ODBC drivers are
in use, these are also a possible cause of memory leaks. Evaluate whether
the linked servers can be disabled for a time as a troubleshooting step to
see whether this prevents the leak, or examine whether the provider is
still fully functional once it has been configured to run out of process by
setting the AllowInProcess registry value for the provider to 0 (the
AllowInProcess value can be found at HKLM\Software\Microsoft\MSSQLServer(or
MSSQL$instance key)\Providers\[ProviderName]).
- If the server supports heavy linked server activity or must run
memory-hungry non-SQL Server code inside the SQL Server process, you
may simply need to adjust the size of the MemToLeave area to make more
memory available to non-SQL Server memory consumers. "-g" is an
optional SQL Server startup parameter that can be used to increase the
size of the MemToLeave area. The default -g memory size is 128MB in SQL
Server 7.0 and 256MB in SQL Server 2000. You can increase the size of
the MemToLeave area by an additioal 128MB by adding -g256 (SQL 7.0) or
-g384 (SQL 2000) as a server startup parameter. This setting will take
effect the next time the SQL Server service is started. Startup
parameters are added in the "General" tab of the Server Properties
dialog in Enterprise Manager.
Troubleshooting Buffer Pool Memory Pressure
Because of SQL Server's dynamic memory managment, it is not unusual for a
significant leak in the MemToLeave area to initially manifest itself as a
shortage of buffers in buffer pool because SQL Server will dynamically
scale down the size of the buffer pool in response to the growing number of
bytes committed within the MemToLeave area. Similarly, a memory hungry or
leaking application running on the same box can cause SQL Server to release
almost all of its BPool memory, leading to a 17803. To rule out these two
alternatives and confirm that the problem is confined to BPool, start by
looking at the Performance Monitor log you collected.
If the counter "Process(sqlservr):Working Set" was much lower than the
amount of physical RAM on the server while the insufficient memory errors
were occurring, look for another process that holds most of the memory and
pursue that process as the root of the memory pressure.
If "Process(sqlservr):Working Set" accounts for most or nearly all of the
physical RAM on the server but if "SQL Server:Memory Manager:Target Server
Memory(KB)" is only a fraction of this amount of memory, the root cause of
the problem is likely a leak in MemToLeave that had the side effect of
causing skrinkage of BPool. In this case follow the suggestions in the
previous section "Troubleshooting MemToLeave Memory Pressure".
If the root cause of the problem is a leak in or excessive demand for bpool
memory, the following should generally be true:
- Buffer pool should have already been grown to its maximum size. In
other words, "SQL Server:Memory Manager:Total Server Memory(KB)" should
be equal to "SQL Server:Memory Manager:Target Server Memory(KB)".
- Buffer pool should consume the majority of physical RAM allocated to
the SQL process ("SQL Server:Memory Manager:Target Server Memory(KB)"
should account for the majority of "Process(sqlservr):Working Set".)
- There should be high lazywriter activity ("SQLServer:Buffer Manager -
Lazy Writer Buffers/sec"). If the problem appears to be BPool memory
pressure and you see no lazywriter activity, something may be blocking
lazywriter. Consider getting one or more DBCC STACKDUMPs while in this
state.
If you have determined that the problem is a leak or excessive demand for
buffer pool memory, use perfmon to determine what is consuming the most
buffer pages. Counters to examine include:
- "SQLServer:Buffer Manager - Cache Size (pages)" (procedure cache)
- "SQLServer:Cache Manager - Cache Pages(Adhoc/Cursor/Stored Proc/etc)"
- "SQLServer:Memory Manager - Granted Workspace Memory (KB)" (query
memory)
- "SQLServer:Memory Manager - SQL Cache Memory (KB)" (cached data pages)
- "SQLServer:Memory Manager - Lock Memory (KB)"
- "SQLServer:Memory Manager - Optimizer Memory (KB)"
- "SQLServer:Buffer Manager - Stolen Pages"
Hope that helps!
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.|||SQL Server cache's connection information, in the MEM TO LEAVE region. If
that was the process that was attempting to make a memory reservation in
this region, then that would account for your errors.
What is happening to cause this is too many external memory processes
executing on the server. Although SQL Server has wonderful Dynamic Memory
managers for the internal processes, it has to appeal to the OS to manage
the external reservation calls. Over time, the MEM TO LEAVE region get
fragmented and the only fix is to clear them segments. Thus, at least a
recycling of the SQL Server services if not a reboot of the system.
You can counter balance this by reducing the number of external process
calls and/or adjust the amount of memory left to the MEM TO LEAVE region
using the /g startup parameter. Howerver, the sizing of this parameter
needs to be adjusted with the assistance of the PSS staff.
A good first start, however, would be to increase from the default for this
parameter from 256 to 384, which represents MB removed from the Buffer Pool
allocation in addition to what is reserved for the UMS Worker threads, 128
MB with the default 256 threads configuration.
Sincerely,
Anthony Thomas
"Jeff Turlington" <Jeff Turlington@.discussions.microsoft.com> wrote in
message news:D9783602-FAD2-4730-BB96-FB7CD2438605@.microsoft.com...
Has anyone seen anything like this before? One of our production servers
lost
the ability establish any trusted connections list night from 5:47 until we
rebooted it this morning. From that point on all attempts for SQL jobs
running under domain ids on the system and any BizTalk processes trying to
connect failed with the message "Login failed for user '(null)'. Reason: Not
associated with a trusted SQL Server connection.". The couple jobs that we
have running under standard Standard users (other than sa) had no problems.
None of our other servers had problems and the network looked ok, so I don't
think it was a domain or network connectivity problem. The reboot resolved
the problem, so it doesn't look like anything related to the domain userids
themself had changed, just SQL suddenly started failing to be able to use
them.
Looking in the SQL Server Log I see the following messages logged (all from
the same spid) at the same time all this started. I'm interpreting these as
meaning the server ran out of memory, but can not think of any reason why
this could have caused trusted connections to fail from that point on. BTW -
We have the latest service packs and patches (ver 8.00.818) installed. Any
ideas? Has anyone else ever seen anything like this before?
Query Memory Manager: Grants=0 Waiting=0 Maximum=150632 Available=150632
Global Memory Objects: Resource=2705 Locks=93
SQLCache=102 Replication=2
LockBytes=2 ServerGlobal=45
Xact=215
Dynamic Memory Manager: Stolen=9211 OS Reserved=16632
OS Committed=16593
OS In Use=12841
Query Plan=3349 Optimizer=0
General=14718
Utilities=13 Connection=3870
Procedure Cache: TotalProcs=922 TotalPages=3400 InUsePages=1759
Buffer Counts: Commited=208408 Target=208408 Hashed=198709
InternalReservation=201 ExternalReservation=0 Min Free=128
Buffer Distribution: Stolen=5811 Free=488 Procedures=3400
Inram=0 Dirty=46274 Kept=0
I/O=0, Latched=35, Other=152400
WARNING: Failed to reserve contiguous memory of Size= 65536.
the ability establish any trusted connections list night from 5:47 until we
rebooted it this morning. From that point on all attempts for SQL jobs
running under domain ids on the system and any BizTalk processes trying to
connect failed with the message "Login failed for user '(null)'. Reason: Not
associated with a trusted SQL Server connection.". The couple jobs that we
have running under standard Standard users (other than sa) had no problems.
None of our other servers had problems and the network looked ok, so I don't
think it was a domain or network connectivity problem. The reboot resolved
the problem, so it doesn't look like anything related to the domain userids
themself had changed, just SQL suddenly started failing to be able to use
them.
Looking in the SQL Server Log I see the following messages logged (all from
the same spid) at the same time all this started. I'm interpreting these as
meaning the server ran out of memory, but can not think of any reason why
this could have caused trusted connections to fail from that point on. BTW -
We have the latest service packs and patches (ver 8.00.818) installed. Any
ideas? Has anyone else ever seen anything like this before?
Query Memory Manager: Grants=0 Waiting=0 Maximum=150632 Available=150632
Global Memory Objects: Resource=2705 Locks=93
SQLCache=102 Replication=2
LockBytes=2 ServerGlobal=45
Xact=215
Dynamic Memory Manager: Stolen=9211 OS Reserved=16632
OS Committed=16593
OS In Use=12841
Query Plan=3349 Optimizer=0
General=14718
Utilities=13 Connection=3870
Procedure Cache: TotalProcs=922 TotalPages=3400 InUsePages=1759
Buffer Counts: Commited=208408 Target=208408 Hashed=198709
InternalReservation=201 ExternalReservation=0 Min Free=128
Buffer Distribution: Stolen=5811 Free=488 Procedures=3400
Inram=0 Dirty=46274 Kept=0
I/O=0, Latched=35, Other=152400
WARNING: Failed to reserve contiguous memory of Size= 65536.The message
WARNING: Failed to reserve contiguous memory of Size= 65536.
shows that you have some pressure it seems in your MemoryToleave area of
sql server's address pool.
Here are a couple of things regarding this error. Try tshooting this, else
you might want to call into PSS and open up a support case -
There are two main areas of memory within SQL Server's address space, the
buffer pool (BPool) and a second memory pool sometimes called the
"MemToLeave" area. The contents of the SQL Server buffer pool include
cached table data, workspace memory used during query execution for
in-memory sorts or hashes, most cached stored procedure and query plans,
memory for locks and other internal structures, and the majority of other
miscellaneous memory needs of the SQL Server. SQL Server 7.0 introduced
dynamic memory management to the buffer pool, which means that the amount
of memory under SQL Server's direct control may grow and shrink in response
to internal SQL Server needs and external memory pressure from other
applications. It is normal for the size of the SQL Server buffer pool to
increase over time until most memory on the server is consumed. This
design can give the false appearance of a memory leak in the SQL Server
buffer pool when operating under normal circumstances. For more detailed
information please reference the Books Online articles "Server Memory
Options", "Memory Architecture", and (SQL Server 2000 only) "Effects of min
and max server memory".
The other significant memory area is sometimes called MemToLeave, and it is
primarily used by non-SQL Server code that happens to be executing within
the SQL Server process. The MemToLeave area is memory that is left
unallocated and unreserved, primarily for code that is not part of the core
SQL Server and therefore does not know how to access memory in the SQL
Server buffer pool. Some examples of components that may use this memory
include extended stored procedures, OLE Automation/COM objects, linked
server OLEDB providers and ODBC drivers, MAPI components used by SQLMail,
and thread stacks (one-half MB per thread). This does not just include the
EXE and .DLL binary images for these components; any memory allocated at
runtime by the components listed above will also be allocated from the
MemToLeave area. Non-SQL Server code makes its memory allocation requests
directly from the OS, not from the SQL Server buffer pool. The entire SQL
Server buffer pool is reserved at server startup, so any requests for
memory made directly from the operating system must be satisfied from the
MemToLeave area, which is the only source of unreserved memory in the SQL
Server address space. SQL Server itself also uses the MemToLeave memory
area for certain allocations; for example, SQL Server 7.0 stores procedure
plans in the MemToLeave area if they are too large for a single 8KB buffer
pool page.
How to Determine Whether the Error Points to a Memory Shortage in Buffer
Pool or MemToLeave
If the 17803 error in the SQL Server errorlog is accompanied by one of the
following error messages, the memory pressure is most likely in the
MemToLeave area (see section "Troubleshooting MemToLeave Memory Pressure"
below). If the messages below do not appear with the 17803, start from
section "Troubleshooting Buffer Pool Memory Pressure".
Errors that imply insufficient contiguous free memory in the MemToLeave
area:
WARNING: Failed to reserve contiguous memory of Size= 65536.
WARNING: Clearing procedure cache to free contiguous memory.
Error: 17802, Severity: 18, State: 3 Could not create server event
thread.
SQL Server could not spawn process_loginread thread
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' reported an error. The provider ran out of
memory.
Troubleshooting MemToLeave Memory Pressure
If the 17803 and associated errors point to a shortage of memory in
MemToLeave, the items below list some of the most common causes of this
type of problem and suggest ways to alleviate the issue.
- If the server runs many concurrent linked server queries against SQL
Servers using SQLOLEDB or MSDASQL, upgrade to MDAC 2.5 or MDAC 2.6 on
the server. The MDAC 2.5 and 2.6 versions of SQLOLEDB are more
conservative with their initial memory allocations.
Perhaps the most common cause of out-of-memory conditions in the MemToLeave
area is a memory leak in a non-SQL Server component running inside the SQL
address space. Items to check:
- Check the errorlog for messages like "Using 'abc.dll' version '123' to
execute extended stored procedure 'xp_abc'." If the dll referenced in this
message is not a Microsoft-provided DLL you may want to consider moving it
out of the production server's address space
- Determine whether COM objects are being executed inside SQL Server's
address space with the sp_OA stored procedures. If sp_OA is being used you
will see ODSOLE70.DLL which hosts sp_OACreate being loaded and the
following message in the errorlog:
Using 'odsole70.dll' version '2000.80.382' to execute extended stored
procedure 'sp_OACreate'.
If sp_OA stored procedures are being used, ensure that the COM objects
are being loaded out of process by passing 4 to the optional third
parameter for sp_OACreate (e.g. "EXEC sp_OACreate 'SQLDMO.SQLServer', @.obj
OUTPUT, 4").
- If linked servers using third-party OLEDB providers or ODBC drivers are
in use, these are also a possible cause of memory leaks. Evaluate whether
the linked servers can be disabled for a time as a troubleshooting step to
see whether this prevents the leak, or examine whether the provider is
still fully functional once it has been configured to run out of process by
setting the AllowInProcess registry value for the provider to 0 (the
AllowInProcess value can be found at HKLM\Software\Microsoft\MSSQLServer(or
MSSQL$instance key)\Providers\[ProviderName]).
- If the server supports heavy linked server activity or must run
memory-hungry non-SQL Server code inside the SQL Server process, you
may simply need to adjust the size of the MemToLeave area to make more
memory available to non-SQL Server memory consumers. "-g" is an
optional SQL Server startup parameter that can be used to increase the
size of the MemToLeave area. The default -g memory size is 128MB in SQL
Server 7.0 and 256MB in SQL Server 2000. You can increase the size of
the MemToLeave area by an additioal 128MB by adding -g256 (SQL 7.0) or
-g384 (SQL 2000) as a server startup parameter. This setting will take
effect the next time the SQL Server service is started. Startup
parameters are added in the "General" tab of the Server Properties
dialog in Enterprise Manager.
Troubleshooting Buffer Pool Memory Pressure
Because of SQL Server's dynamic memory managment, it is not unusual for a
significant leak in the MemToLeave area to initially manifest itself as a
shortage of buffers in buffer pool because SQL Server will dynamically
scale down the size of the buffer pool in response to the growing number of
bytes committed within the MemToLeave area. Similarly, a memory hungry or
leaking application running on the same box can cause SQL Server to release
almost all of its BPool memory, leading to a 17803. To rule out these two
alternatives and confirm that the problem is confined to BPool, start by
looking at the Performance Monitor log you collected.
If the counter "Process(sqlservr):Working Set" was much lower than the
amount of physical RAM on the server while the insufficient memory errors
were occurring, look for another process that holds most of the memory and
pursue that process as the root of the memory pressure.
If "Process(sqlservr):Working Set" accounts for most or nearly all of the
physical RAM on the server but if "SQL Server:Memory Manager:Target Server
Memory(KB)" is only a fraction of this amount of memory, the root cause of
the problem is likely a leak in MemToLeave that had the side effect of
causing skrinkage of BPool. In this case follow the suggestions in the
previous section "Troubleshooting MemToLeave Memory Pressure".
If the root cause of the problem is a leak in or excessive demand for bpool
memory, the following should generally be true:
- Buffer pool should have already been grown to its maximum size. In
other words, "SQL Server:Memory Manager:Total Server Memory(KB)" should
be equal to "SQL Server:Memory Manager:Target Server Memory(KB)".
- Buffer pool should consume the majority of physical RAM allocated to
the SQL process ("SQL Server:Memory Manager:Target Server Memory(KB)"
should account for the majority of "Process(sqlservr):Working Set".)
- There should be high lazywriter activity ("SQLServer:Buffer Manager -
Lazy Writer Buffers/sec"). If the problem appears to be BPool memory
pressure and you see no lazywriter activity, something may be blocking
lazywriter. Consider getting one or more DBCC STACKDUMPs while in this
state.
If you have determined that the problem is a leak or excessive demand for
buffer pool memory, use perfmon to determine what is consuming the most
buffer pages. Counters to examine include:
- "SQLServer:Buffer Manager - Cache Size (pages)" (procedure cache)
- "SQLServer:Cache Manager - Cache Pages(Adhoc/Cursor/Stored Proc/etc)"
- "SQLServer:Memory Manager - Granted Workspace Memory (KB)" (query
memory)
- "SQLServer:Memory Manager - SQL Cache Memory (KB)" (cached data pages)
- "SQLServer:Memory Manager - Lock Memory (KB)"
- "SQLServer:Memory Manager - Optimizer Memory (KB)"
- "SQLServer:Buffer Manager - Stolen Pages"
Hope that helps!
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.|||SQL Server cache's connection information, in the MEM TO LEAVE region. If
that was the process that was attempting to make a memory reservation in
this region, then that would account for your errors.
What is happening to cause this is too many external memory processes
executing on the server. Although SQL Server has wonderful Dynamic Memory
managers for the internal processes, it has to appeal to the OS to manage
the external reservation calls. Over time, the MEM TO LEAVE region get
fragmented and the only fix is to clear them segments. Thus, at least a
recycling of the SQL Server services if not a reboot of the system.
You can counter balance this by reducing the number of external process
calls and/or adjust the amount of memory left to the MEM TO LEAVE region
using the /g startup parameter. Howerver, the sizing of this parameter
needs to be adjusted with the assistance of the PSS staff.
A good first start, however, would be to increase from the default for this
parameter from 256 to 384, which represents MB removed from the Buffer Pool
allocation in addition to what is reserved for the UMS Worker threads, 128
MB with the default 256 threads configuration.
Sincerely,
Anthony Thomas
"Jeff Turlington" <Jeff Turlington@.discussions.microsoft.com> wrote in
message news:D9783602-FAD2-4730-BB96-FB7CD2438605@.microsoft.com...
Has anyone seen anything like this before? One of our production servers
lost
the ability establish any trusted connections list night from 5:47 until we
rebooted it this morning. From that point on all attempts for SQL jobs
running under domain ids on the system and any BizTalk processes trying to
connect failed with the message "Login failed for user '(null)'. Reason: Not
associated with a trusted SQL Server connection.". The couple jobs that we
have running under standard Standard users (other than sa) had no problems.
None of our other servers had problems and the network looked ok, so I don't
think it was a domain or network connectivity problem. The reboot resolved
the problem, so it doesn't look like anything related to the domain userids
themself had changed, just SQL suddenly started failing to be able to use
them.
Looking in the SQL Server Log I see the following messages logged (all from
the same spid) at the same time all this started. I'm interpreting these as
meaning the server ran out of memory, but can not think of any reason why
this could have caused trusted connections to fail from that point on. BTW -
We have the latest service packs and patches (ver 8.00.818) installed. Any
ideas? Has anyone else ever seen anything like this before?
Query Memory Manager: Grants=0 Waiting=0 Maximum=150632 Available=150632
Global Memory Objects: Resource=2705 Locks=93
SQLCache=102 Replication=2
LockBytes=2 ServerGlobal=45
Xact=215
Dynamic Memory Manager: Stolen=9211 OS Reserved=16632
OS Committed=16593
OS In Use=12841
Query Plan=3349 Optimizer=0
General=14718
Utilities=13 Connection=3870
Procedure Cache: TotalProcs=922 TotalPages=3400 InUsePages=1759
Buffer Counts: Commited=208408 Target=208408 Hashed=198709
InternalReservation=201 ExternalReservation=0 Min Free=128
Buffer Distribution: Stolen=5811 Free=488 Procedures=3400
Inram=0 Dirty=46274 Kept=0
I/O=0, Latched=35, Other=152400
WARNING: Failed to reserve contiguous memory of Size= 65536.
Tuesday, March 20, 2012
Could RS's data mining be used in a production setting?
I just have a simple question that can hopefully answer whether I should dive too deeply into Reporting/Analysis Services. I'm looking to run some clustering algorithms on user-inputted keywords on objects within my database (please see http://flickr.com/photos/tags/friends/clusters/ for an example of what I'm talking about). It seems to me that the various reporting methods that SQL Server provides would be an ideal, quick and easy way of providing this for my SQL Server 2005 database. I envision having some sort of job that would get the data from the reporting service daily or hourly and insert it into my main database.
Can the reporting services provide this type of functionality? And if so, would this be scalable? I would want to be able to access this clustered data in much the same way I do queries across my database and would want them to be done quickly and efficiently.
Thank you for your help.Can you clarify what you mean by "get the data from the reporting service"? In general, you could have a mining model and produce a report from it or you could run a report that creates a temporary (session) mining model.|||I'm not entirely sure on the concept myself. I was thinking that accessing the mined data from whatever form reporting service uses on a production site would be too slow, wouldn't scale. Rather, I'd like to migrate that data and transform it on some schedule to the main SQL Server database so that the queries would be quicker and scalable.|||
When you process a data mining structure, the discovered patterns are saved in an SSAS database (the same storage format as with a cube) so the data mining prediction queries should be fast. Nevertheless, I would suggest a POC to prove that your scalability requirements are met.
sqlMonday, March 19, 2012
Could not open FCB for invalid file ID 46336 in database 'Production'..
For about two hours last evening our database started producing these errors
and various jobs failed.
This morning all seems quiet. However we do rebuild our indexes overnight.
Should we worry about this?
The jobs that failed do not process any text columns (the only mention I
found on microsofts site).
The File ID cannot relate to sysfiles. Does anyone know what is does relate
to?
SQL Server 2000 SP3A.
Hi,
Please run DBCC checktable to see if there are any corruption related errors on this table. Take full database backup before you execute dbcc checktable.
If the output come clear then you do not want to worry any more.
Thanks
Hari
MCDBA
-- Paul Cahill wrote: --
For about two hours last evening our database started producing these errors
and various jobs failed.
This morning all seems quiet. However we do rebuild our indexes overnight.
Should we worry about this?
The jobs that failed do not process any text columns (the only mention I
found on microsofts site).
The File ID cannot relate to sysfiles. Does anyone know what is does relate
to?
SQL Server 2000 SP3A.
|||Thanks Hari
How do I tell which table. I tried offering up the number above as an ID to
sysobjects but nothing came back.
Paul
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:F3226200-D6DC-4939-8773-4E5CFC0FA567@.microsoft.com...
> Hi,
> Please run DBCC checktable to see if there are any corruption related
errors on this table. Take full database backup before you execute dbcc
checktable.
> If the output come clear then you do not want to worry any more.
> Thanks
> Hari
> MCDBA
>
> -- Paul Cahill wrote: --
> For about two hours last evening our database started producing these
errors
> and various jobs failed.
> This morning all seems quiet. However we do rebuild our indexes
overnight.
> Should we worry about this?
> The jobs that failed do not process any text columns (the only
mention I
> found on microsofts site).
> The File ID cannot relate to sysfiles. Does anyone know what is does
relate
> to?
> SQL Server 2000 SP3A.
>
>
|||Hi,
I feel that it should be a problem with tempdb. If it is the case no issues.
Anyway can you execute DBCC CHECKDB('dbname') for that database and ensure
no errors.
Thanks
Hari
MCDBA
"Paul Cahill" <XYZpaul.cahillXYZ@.blueyonder.co.uk> wrote in message
news:O0Ga6aBNEHA.2844@.tk2msftngp13.phx.gbl...
> Thanks Hari
> How do I tell which table. I tried offering up the number above as an ID
to[vbcol=seagreen]
> sysobjects but nothing came back.
> Paul
>
> "Hari" <hari_prasad_k@.hotmail.com> wrote in message
> news:F3226200-D6DC-4939-8773-4E5CFC0FA567@.microsoft.com...
> errors on this table. Take full database backup before you execute dbcc
> checktable.
these[vbcol=seagreen]
> errors
> overnight.
> mention I
does
> relate
>
|||I was starting to suspect as much.
Let's just assume this never happened
Paul
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:Oom$kfBNEHA.3420@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I feel that it should be a problem with tempdb. If it is the case no
issues.
> Anyway can you execute DBCC CHECKDB('dbname') for that database and ensure
> no errors.
> Thanks
> Hari
> MCDBA
>
> "Paul Cahill" <XYZpaul.cahillXYZ@.blueyonder.co.uk> wrote in message
> news:O0Ga6aBNEHA.2844@.tk2msftngp13.phx.gbl...
> to
> these
> does
>
and various jobs failed.
This morning all seems quiet. However we do rebuild our indexes overnight.
Should we worry about this?
The jobs that failed do not process any text columns (the only mention I
found on microsofts site).
The File ID cannot relate to sysfiles. Does anyone know what is does relate
to?
SQL Server 2000 SP3A.
Hi,
Please run DBCC checktable to see if there are any corruption related errors on this table. Take full database backup before you execute dbcc checktable.
If the output come clear then you do not want to worry any more.
Thanks
Hari
MCDBA
-- Paul Cahill wrote: --
For about two hours last evening our database started producing these errors
and various jobs failed.
This morning all seems quiet. However we do rebuild our indexes overnight.
Should we worry about this?
The jobs that failed do not process any text columns (the only mention I
found on microsofts site).
The File ID cannot relate to sysfiles. Does anyone know what is does relate
to?
SQL Server 2000 SP3A.
|||Thanks Hari
How do I tell which table. I tried offering up the number above as an ID to
sysobjects but nothing came back.
Paul
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:F3226200-D6DC-4939-8773-4E5CFC0FA567@.microsoft.com...
> Hi,
> Please run DBCC checktable to see if there are any corruption related
errors on this table. Take full database backup before you execute dbcc
checktable.
> If the output come clear then you do not want to worry any more.
> Thanks
> Hari
> MCDBA
>
> -- Paul Cahill wrote: --
> For about two hours last evening our database started producing these
errors
> and various jobs failed.
> This morning all seems quiet. However we do rebuild our indexes
overnight.
> Should we worry about this?
> The jobs that failed do not process any text columns (the only
mention I
> found on microsofts site).
> The File ID cannot relate to sysfiles. Does anyone know what is does
relate
> to?
> SQL Server 2000 SP3A.
>
>
|||Hi,
I feel that it should be a problem with tempdb. If it is the case no issues.
Anyway can you execute DBCC CHECKDB('dbname') for that database and ensure
no errors.
Thanks
Hari
MCDBA
"Paul Cahill" <XYZpaul.cahillXYZ@.blueyonder.co.uk> wrote in message
news:O0Ga6aBNEHA.2844@.tk2msftngp13.phx.gbl...
> Thanks Hari
> How do I tell which table. I tried offering up the number above as an ID
to[vbcol=seagreen]
> sysobjects but nothing came back.
> Paul
>
> "Hari" <hari_prasad_k@.hotmail.com> wrote in message
> news:F3226200-D6DC-4939-8773-4E5CFC0FA567@.microsoft.com...
> errors on this table. Take full database backup before you execute dbcc
> checktable.
these[vbcol=seagreen]
> errors
> overnight.
> mention I
does
> relate
>
|||I was starting to suspect as much.
Let's just assume this never happened
Paul
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:Oom$kfBNEHA.3420@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I feel that it should be a problem with tempdb. If it is the case no
issues.
> Anyway can you execute DBCC CHECKDB('dbname') for that database and ensure
> no errors.
> Thanks
> Hari
> MCDBA
>
> "Paul Cahill" <XYZpaul.cahillXYZ@.blueyonder.co.uk> wrote in message
> news:O0Ga6aBNEHA.2844@.tk2msftngp13.phx.gbl...
> to
> these
> does
>
Could not open FCB for invalid file ID 46336 in database 'Production'..
For about two hours last evening our database started producing these errors
and various jobs failed.
This morning all seems quiet. However we do rebuild our indexes overnight.
Should we worry about this?
The jobs that failed do not process any text columns (the only mention I
found on microsofts site).
The File ID cannot relate to sysfiles. Does anyone know what is does relate
to?
SQL Server 2000 SP3A.Hi
Please run DBCC checktable to see if there are any corruption related errors on this table. Take full database backup before you execute dbcc checktable.
If the output come clear then you do not want to worry any more
Thank
Har
MCDB
-- Paul Cahill wrote: --
For about two hours last evening our database started producing these error
and various jobs failed
This morning all seems quiet. However we do rebuild our indexes overnight
Should we worry about this
The jobs that failed do not process any text columns (the only mention
found on microsofts site)
The File ID cannot relate to sysfiles. Does anyone know what is does relat
to
SQL Server 2000 SP3A|||Thanks Hari
How do I tell which table. I tried offering up the number above as an ID to
sysobjects but nothing came back.
Paul
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:F3226200-D6DC-4939-8773-4E5CFC0FA567@.microsoft.com...
> Hi,
> Please run DBCC checktable to see if there are any corruption related
errors on this table. Take full database backup before you execute dbcc
checktable.
> If the output come clear then you do not want to worry any more.
> Thanks
> Hari
> MCDBA
>
> -- Paul Cahill wrote: --
> For about two hours last evening our database started producing these
errors
> and various jobs failed.
> This morning all seems quiet. However we do rebuild our indexes
overnight.
> Should we worry about this?
> The jobs that failed do not process any text columns (the only
mention I
> found on microsofts site).
> The File ID cannot relate to sysfiles. Does anyone know what is does
relate
> to?
> SQL Server 2000 SP3A.
>
>|||Hi,
I feel that it should be a problem with tempdb. If it is the case no issues.
Anyway can you execute DBCC CHECKDB('dbname') for that database and ensure
no errors.
Thanks
Hari
MCDBA
"Paul Cahill" <XYZpaul.cahillXYZ@.blueyonder.co.uk> wrote in message
news:O0Ga6aBNEHA.2844@.tk2msftngp13.phx.gbl...
> Thanks Hari
> How do I tell which table. I tried offering up the number above as an ID
to
> sysobjects but nothing came back.
> Paul
>
> "Hari" <hari_prasad_k@.hotmail.com> wrote in message
> news:F3226200-D6DC-4939-8773-4E5CFC0FA567@.microsoft.com...
> > Hi,
> >
> > Please run DBCC checktable to see if there are any corruption related
> errors on this table. Take full database backup before you execute dbcc
> checktable.
> > If the output come clear then you do not want to worry any more.
> >
> > Thanks
> > Hari
> > MCDBA
> >
> >
> > -- Paul Cahill wrote: --
> >
> > For about two hours last evening our database started producing
these
> errors
> > and various jobs failed.
> > This morning all seems quiet. However we do rebuild our indexes
> overnight.
> > Should we worry about this?
> >
> > The jobs that failed do not process any text columns (the only
> mention I
> > found on microsofts site).
> >
> > The File ID cannot relate to sysfiles. Does anyone know what is
does
> relate
> > to?
> >
> > SQL Server 2000 SP3A.
> >
> >
> >
>|||I was starting to suspect as much.
Let's just assume this never happened :)
Paul
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:Oom$kfBNEHA.3420@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I feel that it should be a problem with tempdb. If it is the case no
issues.
> Anyway can you execute DBCC CHECKDB('dbname') for that database and ensure
> no errors.
> Thanks
> Hari
> MCDBA
>
> "Paul Cahill" <XYZpaul.cahillXYZ@.blueyonder.co.uk> wrote in message
> news:O0Ga6aBNEHA.2844@.tk2msftngp13.phx.gbl...
> > Thanks Hari
> >
> > How do I tell which table. I tried offering up the number above as an ID
> to
> > sysobjects but nothing came back.
> >
> > Paul
> >
> >
> >
> > "Hari" <hari_prasad_k@.hotmail.com> wrote in message
> > news:F3226200-D6DC-4939-8773-4E5CFC0FA567@.microsoft.com...
> > > Hi,
> > >
> > > Please run DBCC checktable to see if there are any corruption related
> > errors on this table. Take full database backup before you execute dbcc
> > checktable.
> > > If the output come clear then you do not want to worry any more.
> > >
> > > Thanks
> > > Hari
> > > MCDBA
> > >
> > >
> > > -- Paul Cahill wrote: --
> > >
> > > For about two hours last evening our database started producing
> these
> > errors
> > > and various jobs failed.
> > > This morning all seems quiet. However we do rebuild our indexes
> > overnight.
> > > Should we worry about this?
> > >
> > > The jobs that failed do not process any text columns (the only
> > mention I
> > > found on microsofts site).
> > >
> > > The File ID cannot relate to sysfiles. Does anyone know what is
> does
> > relate
> > > to?
> > >
> > > SQL Server 2000 SP3A.
> > >
> > >
> > >
> >
> >
>
and various jobs failed.
This morning all seems quiet. However we do rebuild our indexes overnight.
Should we worry about this?
The jobs that failed do not process any text columns (the only mention I
found on microsofts site).
The File ID cannot relate to sysfiles. Does anyone know what is does relate
to?
SQL Server 2000 SP3A.Hi
Please run DBCC checktable to see if there are any corruption related errors on this table. Take full database backup before you execute dbcc checktable.
If the output come clear then you do not want to worry any more
Thank
Har
MCDB
-- Paul Cahill wrote: --
For about two hours last evening our database started producing these error
and various jobs failed
This morning all seems quiet. However we do rebuild our indexes overnight
Should we worry about this
The jobs that failed do not process any text columns (the only mention
found on microsofts site)
The File ID cannot relate to sysfiles. Does anyone know what is does relat
to
SQL Server 2000 SP3A|||Thanks Hari
How do I tell which table. I tried offering up the number above as an ID to
sysobjects but nothing came back.
Paul
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:F3226200-D6DC-4939-8773-4E5CFC0FA567@.microsoft.com...
> Hi,
> Please run DBCC checktable to see if there are any corruption related
errors on this table. Take full database backup before you execute dbcc
checktable.
> If the output come clear then you do not want to worry any more.
> Thanks
> Hari
> MCDBA
>
> -- Paul Cahill wrote: --
> For about two hours last evening our database started producing these
errors
> and various jobs failed.
> This morning all seems quiet. However we do rebuild our indexes
overnight.
> Should we worry about this?
> The jobs that failed do not process any text columns (the only
mention I
> found on microsofts site).
> The File ID cannot relate to sysfiles. Does anyone know what is does
relate
> to?
> SQL Server 2000 SP3A.
>
>|||Hi,
I feel that it should be a problem with tempdb. If it is the case no issues.
Anyway can you execute DBCC CHECKDB('dbname') for that database and ensure
no errors.
Thanks
Hari
MCDBA
"Paul Cahill" <XYZpaul.cahillXYZ@.blueyonder.co.uk> wrote in message
news:O0Ga6aBNEHA.2844@.tk2msftngp13.phx.gbl...
> Thanks Hari
> How do I tell which table. I tried offering up the number above as an ID
to
> sysobjects but nothing came back.
> Paul
>
> "Hari" <hari_prasad_k@.hotmail.com> wrote in message
> news:F3226200-D6DC-4939-8773-4E5CFC0FA567@.microsoft.com...
> > Hi,
> >
> > Please run DBCC checktable to see if there are any corruption related
> errors on this table. Take full database backup before you execute dbcc
> checktable.
> > If the output come clear then you do not want to worry any more.
> >
> > Thanks
> > Hari
> > MCDBA
> >
> >
> > -- Paul Cahill wrote: --
> >
> > For about two hours last evening our database started producing
these
> errors
> > and various jobs failed.
> > This morning all seems quiet. However we do rebuild our indexes
> overnight.
> > Should we worry about this?
> >
> > The jobs that failed do not process any text columns (the only
> mention I
> > found on microsofts site).
> >
> > The File ID cannot relate to sysfiles. Does anyone know what is
does
> relate
> > to?
> >
> > SQL Server 2000 SP3A.
> >
> >
> >
>|||I was starting to suspect as much.
Let's just assume this never happened :)
Paul
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:Oom$kfBNEHA.3420@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I feel that it should be a problem with tempdb. If it is the case no
issues.
> Anyway can you execute DBCC CHECKDB('dbname') for that database and ensure
> no errors.
> Thanks
> Hari
> MCDBA
>
> "Paul Cahill" <XYZpaul.cahillXYZ@.blueyonder.co.uk> wrote in message
> news:O0Ga6aBNEHA.2844@.tk2msftngp13.phx.gbl...
> > Thanks Hari
> >
> > How do I tell which table. I tried offering up the number above as an ID
> to
> > sysobjects but nothing came back.
> >
> > Paul
> >
> >
> >
> > "Hari" <hari_prasad_k@.hotmail.com> wrote in message
> > news:F3226200-D6DC-4939-8773-4E5CFC0FA567@.microsoft.com...
> > > Hi,
> > >
> > > Please run DBCC checktable to see if there are any corruption related
> > errors on this table. Take full database backup before you execute dbcc
> > checktable.
> > > If the output come clear then you do not want to worry any more.
> > >
> > > Thanks
> > > Hari
> > > MCDBA
> > >
> > >
> > > -- Paul Cahill wrote: --
> > >
> > > For about two hours last evening our database started producing
> these
> > errors
> > > and various jobs failed.
> > > This morning all seems quiet. However we do rebuild our indexes
> > overnight.
> > > Should we worry about this?
> > >
> > > The jobs that failed do not process any text columns (the only
> > mention I
> > > found on microsofts site).
> > >
> > > The File ID cannot relate to sysfiles. Does anyone know what is
> does
> > relate
> > > to?
> > >
> > > SQL Server 2000 SP3A.
> > >
> > >
> > >
> >
> >
>
Could not open FCB for invalid file ID 46336 in database 'Production'..
For about two hours last evening our database started producing these errors
and various jobs failed.
This morning all seems quiet. However we do rebuild our indexes overnight.
Should we worry about this?
The jobs that failed do not process any text columns (the only mention I
found on microsofts site).
The File ID cannot relate to sysfiles. Does anyone know what is does relate
to?
SQL Server 2000 SP3A.Hi,
Please run DBCC checktable to see if there are any corruption related errors
on this table. Take full database backup before you execute dbcc checktable
.
If the output come clear then you do not want to worry any more.
Thanks
Hari
MCDBA
-- Paul Cahill wrote: --
For about two hours last evening our database started producing these errors
and various jobs failed.
This morning all seems quiet. However we do rebuild our indexes overnight.
Should we worry about this?
The jobs that failed do not process any text columns (the only mention I
found on microsofts site).
The File ID cannot relate to sysfiles. Does anyone know what is does relate
to?
SQL Server 2000 SP3A.|||Thanks Hari
How do I tell which table. I tried offering up the number above as an ID to
sysobjects but nothing came back.
Paul
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:F3226200-D6DC-4939-8773-4E5CFC0FA567@.microsoft.com...
> Hi,
> Please run DBCC checktable to see if there are any corruption related
errors on this table. Take full database backup before you execute dbcc
checktable.
> If the output come clear then you do not want to worry any more.
> Thanks
> Hari
> MCDBA
>
> -- Paul Cahill wrote: --
> For about two hours last evening our database started producing these
errors
> and various jobs failed.
> This morning all seems quiet. However we do rebuild our indexes
overnight.
> Should we worry about this?
> The jobs that failed do not process any text columns (the only
mention I
> found on microsofts site).
> The File ID cannot relate to sysfiles. Does anyone know what is does
relate
> to?
> SQL Server 2000 SP3A.
>
>|||Hi,
I feel that it should be a problem with tempdb. If it is the case no issues.
Anyway can you execute DBCC CHECKDB('dbname') for that database and ensure
no errors.
Thanks
Hari
MCDBA
"Paul Cahill" <XYZpaul.cahillXYZ@.blueyonder.co.uk> wrote in message
news:O0Ga6aBNEHA.2844@.tk2msftngp13.phx.gbl...
> Thanks Hari
> How do I tell which table. I tried offering up the number above as an ID
to
> sysobjects but nothing came back.
> Paul
>
> "Hari" <hari_prasad_k@.hotmail.com> wrote in message
> news:F3226200-D6DC-4939-8773-4E5CFC0FA567@.microsoft.com...
> errors on this table. Take full database backup before you execute dbcc
> checktable.
these[vbcol=seagreen]
> errors
> overnight.
> mention I
does[vbcol=seagreen]
> relate
>|||I was starting to suspect as much.
Let's just assume this never happened
Paul
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:Oom$kfBNEHA.3420@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I feel that it should be a problem with tempdb. If it is the case no
issues.
> Anyway can you execute DBCC CHECKDB('dbname') for that database and ensure
> no errors.
> Thanks
> Hari
> MCDBA
>
> "Paul Cahill" <XYZpaul.cahillXYZ@.blueyonder.co.uk> wrote in message
> news:O0Ga6aBNEHA.2844@.tk2msftngp13.phx.gbl...
> to
> these
> does
>
and various jobs failed.
This morning all seems quiet. However we do rebuild our indexes overnight.
Should we worry about this?
The jobs that failed do not process any text columns (the only mention I
found on microsofts site).
The File ID cannot relate to sysfiles. Does anyone know what is does relate
to?
SQL Server 2000 SP3A.Hi,
Please run DBCC checktable to see if there are any corruption related errors
on this table. Take full database backup before you execute dbcc checktable
.
If the output come clear then you do not want to worry any more.
Thanks
Hari
MCDBA
-- Paul Cahill wrote: --
For about two hours last evening our database started producing these errors
and various jobs failed.
This morning all seems quiet. However we do rebuild our indexes overnight.
Should we worry about this?
The jobs that failed do not process any text columns (the only mention I
found on microsofts site).
The File ID cannot relate to sysfiles. Does anyone know what is does relate
to?
SQL Server 2000 SP3A.|||Thanks Hari
How do I tell which table. I tried offering up the number above as an ID to
sysobjects but nothing came back.
Paul
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:F3226200-D6DC-4939-8773-4E5CFC0FA567@.microsoft.com...
> Hi,
> Please run DBCC checktable to see if there are any corruption related
errors on this table. Take full database backup before you execute dbcc
checktable.
> If the output come clear then you do not want to worry any more.
> Thanks
> Hari
> MCDBA
>
> -- Paul Cahill wrote: --
> For about two hours last evening our database started producing these
errors
> and various jobs failed.
> This morning all seems quiet. However we do rebuild our indexes
overnight.
> Should we worry about this?
> The jobs that failed do not process any text columns (the only
mention I
> found on microsofts site).
> The File ID cannot relate to sysfiles. Does anyone know what is does
relate
> to?
> SQL Server 2000 SP3A.
>
>|||Hi,
I feel that it should be a problem with tempdb. If it is the case no issues.
Anyway can you execute DBCC CHECKDB('dbname') for that database and ensure
no errors.
Thanks
Hari
MCDBA
"Paul Cahill" <XYZpaul.cahillXYZ@.blueyonder.co.uk> wrote in message
news:O0Ga6aBNEHA.2844@.tk2msftngp13.phx.gbl...
> Thanks Hari
> How do I tell which table. I tried offering up the number above as an ID
to
> sysobjects but nothing came back.
> Paul
>
> "Hari" <hari_prasad_k@.hotmail.com> wrote in message
> news:F3226200-D6DC-4939-8773-4E5CFC0FA567@.microsoft.com...
> errors on this table. Take full database backup before you execute dbcc
> checktable.
these[vbcol=seagreen]
> errors
> overnight.
> mention I
does[vbcol=seagreen]
> relate
>|||I was starting to suspect as much.
Let's just assume this never happened
Paul
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:Oom$kfBNEHA.3420@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I feel that it should be a problem with tempdb. If it is the case no
issues.
> Anyway can you execute DBCC CHECKDB('dbname') for that database and ensure
> no errors.
> Thanks
> Hari
> MCDBA
>
> "Paul Cahill" <XYZpaul.cahillXYZ@.blueyonder.co.uk> wrote in message
> news:O0Ga6aBNEHA.2844@.tk2msftngp13.phx.gbl...
> to
> these
> does
>
Saturday, February 25, 2012
Could not establish trust relationship with remote server.........
Please....help....I've to go live with the production environment - next
week...
http://www.microsoft.com/sql/community/newsgroups/dgbrowser/en-us/default.mspx?&guid=&sloc=en-us&dg=microsoft.public.sqlserver.reportingsvcs&p=1&tid=a80ed727-334a-47c9-a6c8-2b76dfc141cd
Many thanks...Bye the way...I'm using the Reporting Service Developer Edition on my Test
serverâ?¦
"Dominic" wrote:
> Please....help....I've to go live with the production environment - next
> week...
> http://www.microsoft.com/sql/community/newsgroups/dgbrowser/en-us/default.mspx?&guid=&sloc=en-us&dg=microsoft.public.sqlserver.reportingsvcs&p=1&tid=a80ed727-334a-47c9-a6c8-2b76dfc141cd
> Many thanks...|||Hi Dominic,
I had exactly the same problem as you described. I'm also using the
Reporting Server Developer Edition too on our test system. After alot of
effort I tracked it down to the SSL certificate we have on our test server
not been written to the "Trusted Certificated List". To Fix this I used
selfSSL with the option /T see below this fixed it with no other changes
required. If I missed out the /T option I got exactly the same error as you.
Regards,
Microsoft (R) SelfSSL Version 1.0
Copyright (C) 2003 Microsoft Corporation. All rights reserved.
Installs self-signed SSL certificate into IIS.
SELFSSL [/T] [/N:cn] [/K:key size] [/S:site id] [/P:port]
/T Adds the self-signed certificate to "Trusted Certificates"
list. The local browser will trust the self-signed
certificate
if this flag is specified.
/N:cn Specifies the common name of the certificate. The computer
name is used if not specified.
/K:key size Specifies the key length. Default is 1024.
/V:validity days Specifies the validity of the certificate. Default is 7 days.
/S:site id Specifies the id of the site. Default is 1 (Default Site).
/P:port Specifies the SSL port. Default is 443.
/Q Quiet mode. You will not be prompted when SSL settings are
overwritten.
I ran selfssl on the command line with the following options
selfssl.exe /T /N:CN=EDGE /K:1024 /V:365 /S:1 /P:443
"Dominic" wrote:
> Bye the way...I'm using the Reporting Service Developer Edition on my Test
> serverâ?¦
>
> "Dominic" wrote:
> > Please....help....I've to go live with the production environment - next
> > week...
> >
> > http://www.microsoft.com/sql/community/newsgroups/dgbrowser/en-us/default.mspx?&guid=&sloc=en-us&dg=microsoft.public.sqlserver.reportingsvcs&p=1&tid=a80ed727-334a-47c9-a6c8-2b76dfc141cd
> >
> > Many thanks...
week...
http://www.microsoft.com/sql/community/newsgroups/dgbrowser/en-us/default.mspx?&guid=&sloc=en-us&dg=microsoft.public.sqlserver.reportingsvcs&p=1&tid=a80ed727-334a-47c9-a6c8-2b76dfc141cd
Many thanks...Bye the way...I'm using the Reporting Service Developer Edition on my Test
serverâ?¦
"Dominic" wrote:
> Please....help....I've to go live with the production environment - next
> week...
> http://www.microsoft.com/sql/community/newsgroups/dgbrowser/en-us/default.mspx?&guid=&sloc=en-us&dg=microsoft.public.sqlserver.reportingsvcs&p=1&tid=a80ed727-334a-47c9-a6c8-2b76dfc141cd
> Many thanks...|||Hi Dominic,
I had exactly the same problem as you described. I'm also using the
Reporting Server Developer Edition too on our test system. After alot of
effort I tracked it down to the SSL certificate we have on our test server
not been written to the "Trusted Certificated List". To Fix this I used
selfSSL with the option /T see below this fixed it with no other changes
required. If I missed out the /T option I got exactly the same error as you.
Regards,
Microsoft (R) SelfSSL Version 1.0
Copyright (C) 2003 Microsoft Corporation. All rights reserved.
Installs self-signed SSL certificate into IIS.
SELFSSL [/T] [/N:cn] [/K:key size] [/S:site id] [/P:port]
/T Adds the self-signed certificate to "Trusted Certificates"
list. The local browser will trust the self-signed
certificate
if this flag is specified.
/N:cn Specifies the common name of the certificate. The computer
name is used if not specified.
/K:key size Specifies the key length. Default is 1024.
/V:validity days Specifies the validity of the certificate. Default is 7 days.
/S:site id Specifies the id of the site. Default is 1 (Default Site).
/P:port Specifies the SSL port. Default is 443.
/Q Quiet mode. You will not be prompted when SSL settings are
overwritten.
I ran selfssl on the command line with the following options
selfssl.exe /T /N:CN=EDGE /K:1024 /V:365 /S:1 /P:443
"Dominic" wrote:
> Bye the way...I'm using the Reporting Service Developer Edition on my Test
> serverâ?¦
>
> "Dominic" wrote:
> > Please....help....I've to go live with the production environment - next
> > week...
> >
> > http://www.microsoft.com/sql/community/newsgroups/dgbrowser/en-us/default.mspx?&guid=&sloc=en-us&dg=microsoft.public.sqlserver.reportingsvcs&p=1&tid=a80ed727-334a-47c9-a6c8-2b76dfc141cd
> >
> > Many thanks...
Subscribe to:
Posts (Atom)