SQL Server Q&A

Symptoms
If you use Bcp.exe to export a table to a flat file, the process may fail with an exception error if you use a format file to change the order of the columns during the export. The exception error occurs within the Bcp.exe file and the following error message appears:

The instruction at “memory_address” referenced memory at “memory_address”.
The memory could not be “written”.The output file is created but no rows are exported.
Resolution
Use a view to change the order of the columns to export. Then, use the view when you perform the bulk copy program and have the format file match the order of the columns in the view.

Symptoms
A standalone computer (without any network protocols installed) fails to connect locally to the Microsoft SQL Server 7.0 Desktop Edition. If you attempt to connect by using either Query Analyzer or SQL Enterprise Manager, the following error message occurs:

Unable to connect to server \\SRVNAME:
Server: Msg 17, Level 16, State 1
[Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server does not exist or access denied. Attempting to connect by using ODBCPING causes this error message to occur:

COULD NOT CONNECT TO SQL SERVER
SQLState: 08001 Native Error: 17
Info. Message: [Microsoft][ODBC SQL Server Driver][Shared Memory]SQL
Server does not exist or access denied.
SQLState: 01000 Native Error: 53
Info. Message: [Microsoft][ODBC SQL Server Driver][Shared
Memory]ConnectionOpen(Connect()). ISQL can still connect, when you specify the SQL Server machine name. ISQL makes a DB Library connection and uses shared memory.
Resolution
The inability to connect locally, by using the machine name, occurs after Microsoft Data Access Components (MDAC) 2.6 has been applied to the desktop computer and the connection attempts to make the connection by using the Shared Memory protocol. Shared Memory is used when you specify the machine name to connect and no network protocols are installed on the desktop computer.

Symptoms
An access violation (AV) exception may occur when SQL Server tries to free the relevant memory block. The following is a sample short stack dump that you may see in the error log:

* Short Stack Dump* 0040A829 Module(sqlservr+0000A829) (CVariableInfo::CVarBlock::PvbJoin+00000035)* 0040A782 Module(sqlservr+0000A782) (CVariableInfo::PviRelease+00000056)* 0040A72C Module(sqlservr+0000A72C) (CVarPageMgr::Release+00000014)* 00401B52 Module(sqlservr+00001B52) (CMemObj::Free+0000001E)* 00401AFD Module(sqlservr+00001AFD) (CMemThread::Free+00000044)* 00401B26 Module(sqlservr+00001B26) (commondelete+0000001B)* 00560988 Module(sqlservr+00160988) (CSql::~CSql+00000021)* 0053E4B9 Module(sqlservr+0013E4B9) (CSqlMgr::DerefSql+00000065)* 0053EAC8 Module(sqlservr+0013EAC8) (CCompPlan::~CCompPlan+00000051)* 0053EA4B Module(sqlservr+0013EA4B) (CCompPlan::`vector deleting destructor’+0000000B)* 00440B4F Module(sqlservr+00040B4F) (CCacheObject::Release+000000D8)* 005D2F4F Module(sqlservr+001D2F4F) (CCache::FRemoveOne+00000316)* 0081AB31 Module(sqlservr+0041AB31) (BPool::ReplenishFreeList+00000144)* 0040AFAB Module(sqlservr+0000AFAB) (BPool::HelpLazyWriter+00000037)* 004306F5 Module(sqlservr+000306F5) (BPool::ReadAhead+00000074)* 00431A6D Module(sqlservr+00031A6D) (SDES::ReadAhead+0000006D)* 00521D63 Module(sqlservr+00121D63) (UnorderedPageSupplier::AddToQueue+00000546)* 00521938 Module(sqlservr+00121938) (UnorderedPageSupplier::GetNextPage+0000006C)* 00405810 Module(sqlservr+00005810) (SDES::GetBiDi+0000019C)* 0043362A Module(sqlservr+0003362A) (RowsetSS::FetchNextRow+000000B0)* 00433506 Module(sqlservr+00033506) (CQScanRowset::GetRowWithPrefetch+00000040)* 00521769 Module(sqlservr+00121769) (CQScanTableScan::GetRow+0000005F)* 0042C876 Module(sqlservr+0002C876) (CRowPrefetchDelayMgr::PopulateBuffer+0000001F)* 0042C334 Module(sqlservr+0002C334) (CRowPrefetchDelayMgr::GetRowUnordered+0000001E)* 0042C247 Module(sqlservr+0002C247) (CRowPrefetchDelayMgr::GetRow+0000001B)* 0042C22B Module(sqlservr+0002C22B) (CQScanRangePrefetchDelay::GetRow+00000014)* 0042D02F Module(sqlservr+0002D02F) (CQScanNLJoin::GetRow+0000010B)* 006D58CB Module(sqlservr+002D58CB) (CQScanConcat::GetRow+0000001C)* 005285B0 Module(sqlservr+001285B0) (CQScanSort::BuildSortTable+00000047)* 00528560 Module(sqlservr+00128560) (CQScanSort::Open+00000032)* 0041D92F Module(sqlservr+0001D92F) (CQueryScan::Startup+0000010D)* 0041925F Module(sqlservr+0001925F) (CStmtQuery::ErsqExecuteQuery+0000026B)* 00432F55 Module(sqlservr+00032F55) (CStmtSelect::XretExecute+00000229)* 0040F403 Module(sqlservr+0000F403) (CMsqlExecContext::ExecuteStmts+000002D9)* 0040EA95 Module(sqlservr+0000EA95) (CMsqlExecContext::Execute+000001B6)* 00410159 Module(sqlservr+00010159) (CSQLSource::Execute+00000331)* 005F74B6 Module(sqlservr+001F74B6) (CStmtExecStr::XretExecute+0000032E)* 0040F403 Module(sqlservr+0000F403) (CMsqlExecContext::ExecuteStmts+000002D9)* 0040EA95 Module(sqlservr+0000EA95) (CMsqlExecContext::Execute+000001B6)* 00410159 Module(sqlservr+00010159) (CSQLSource::Execute+00000331)* 00429DD3 Module(sqlservr+00029DD3) (execrpc+000004CB)* 0042904D Module(sqlservr+0002904D) (execute_rpc+00000019)* 00410FCE Module(sqlservr+00010FCE) (process_commands+00000210)* 41073379 Module(UMS+00003379) (ProcessWorkRequests+0000024A)* 41073071 Module(UMS+00003071) (ThreadStartRoutine+000000BD)* 7800A27B Module(MSVCRT+0000A27B) (beginthread+000000CE)* 77E5758A Module(KERNEL32+0000758A) (SetFilePointer+0000018A)
Resolution
SQL Server works with double-byte Unicode data internally; therefore, SQL Manager assumes an even length buffer.However, under some unexpected situations the buffer length can become an odd number. For example, this behavior might occur with an invalid Tabular Data Stream (TDS) language event. When the buffer length becomes an odd number, SQL Manager cannot allocate a sufficient memory block. This leads to a potential memory scratch and can cause the access violation that is described in the “Symptoms” section when the relevant memory block is freed later.

Symptoms
If you use Bcp.exe to export a table to a flat file, the process may fail with an exception error if you use a format file to change the order of the columns during the export. The exception error occurs within the Bcp.exe file and the following error message appears:

The instruction at “memory_address” referenced memory at “memory_address”.
The memory could not be “written”.The output file is created but no rows are exported.
Resolution
Use a view to change the order of the columns to export. Then, use the view when you perform the bulk copy program and have the format file match the order of the columns in the view.

Symptoms
This article describes the following about this hotfix release:The issues that are fixed by the hotfix packageThe prerequisites for installing the hotfix packageWhether you must restart the computer after you install the hotfix packageWhether the hotfix package is replaced by any other hotfix packageWhether you must make any registry changesThe files that are contained in the hotfix package
Resolution
In Microsoft SQL Server 2005, when you call a method of a component object model (COM) object by using the sp_OAMethod stored procedure or the sp_OAGetProperty stored procedure, a memory leak occurs. For the sp_OAMethod stored procedure, this problem occurs if the method returns the result in the output parameter of the method. For the sp_OAGetProperty stored procedure, this problem occurs if the stored procedure returns a property value in the OUTPUT parameter.
Additionally, if the memory leaks exhausts the non-buffer pool memory region, the following error message is logged in the SQL Server error log file:

2008-05-01 08:35:07.80 spid535 Downgrading backup log buffers from 1024K to 64K
2008-05-06 09:35:22.55 spid77 Failed Virtual Allocate Bytes: FAIL_VIRTUAL_RESERVE 1048576
2008-05-06 10:48:04.85 spid593 Error: 701, Severity: 17, State: 123.
2008-05-06 10:48:04.85 spid593 There is insufficient system memory to run this query.

Symptoms
Microsoft SQL Server performs dynamic memory management based on the memory requirements of the current load and activities on the system. On a Windows Server 2003 or a Windows XP or later version system,SQL Server can use the memory notification mechanisms that are provided by the QueryMemoryResourceNotification Windows API. On a Microsoft Windows 2000 Server-based system, SQL Server periodically calculates the free physical memory on the system by using the native Windows API. Based on this information from the QueryMemoryResourceNotification Windows API or from the memory calculation, SQL Server responds to the current memory situation on a specific system. This provides the following benefits:The system does not page out the working set of the SQL Server process.The necessary database pages are available in memory to reduce physical I/O needs.For more information, see the “Dynamic memory management” topic and the “Server memory options” topic in SQL Server Books Online.
Resolution
In 64-bit editions of SQL Server,various problems may occur. For example, the following problems may occur:The performance of SQL Serverdecreases suddenly.A computer that is running SQL Serverstops responding for a short time.A time-out occurs for applications that connect to SQL Server.Problems occur when you run even simple commands or use applications on the system.If you installed SQL Server 2005 Service Pack 2 (SP2) or a later version, one of the following error messages is logged in the SQL Server error log when these problems occur:
Error message 1

date time spid1s A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 1086400, committed (KB): 2160928, memory utilization: 50%.
Error message 2

date time spid1s A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 315 seconds. Working set (KB): 410156, committed (KB): 2201296, memory utilization: 18%.
Error message 3

date time spid1s A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 646 seconds. Working set (KB): 901904, committed (KB): 2215752, memory utilization: 40%.Additionally, a known problem exists in SQL Server in which you may receive an instance of this error message. The error message is logged in the SQL Server Error log during the startup process of SQL Server. The error message might be a false warning. Therefore, it might not indicate that working set trim has actually occurred. For more information, visit the following Microsoft Web site:
http://blogs.msdn.com/psssql/archive/2009/05/12/sql-server-reports-working-set-trim-warning-message-during-early-startup-phase.aspx(http://blogs.msdn.com/psssql/archive/2009/05/12/sql-server-reports-working-set-trim-warning-message-during-early-startup-phase.aspx) The Microsoft SQL Server support team has also observed additional error messages or warnings that are recorded in the SQL Server Error log or in the Windows Event logs. These messages resemble the following:

2009-05-05 15:43:56.01 ServerResource Monitor (0×13c43) Worker 0×0412C1E8 appears to be non-yielding on Node 0. Memory freed: 34152 KB. Approx CPU Used: kernel 171 ms, user 140 ms, Interval: 125093.2009-05-05 12:54:52.18 Server* *******************************************************************************2009-05-05 12:54:52.18 Server* BEGIN STACK DUMP:2009-05-05 12:54:52.18 Server*05/05/08 12:54:52 spid 02009-05-05 12:54:52.18 Server* Non-yielding Resource Monitor2009-05-05 12:54:52.18 Server* *******************************************************************************2009-06-10 09:13:53.44 Server* *******************************************************************************2009-06-10 09:13:53.44 Server* BEGIN STACK DUMP:2009-06-10 09:13:53.44 Server*06/10/09 09:13:53 spid 02009-06-10 09:13:53.44 Server* Non-yielding IOCP Listener2009-06-10 09:13:53.44 Server* *******************************************************************************2009-06-10 09:13:55.85 spid2sLazyWriter: warning, no free buffers found.2009-07-15 13:27:45.35 spid4sAppDomain xx (SQLCLR.dbo[runtime].xx) is marked for unload due to memory pressure.2009-07-15 13:27:45.35 spid4sAppDomain xx (SQLCLR.dbo[runtime].xx) unloaded.2009-07-15 13:37:51.42 LogonError: 17189, Severity: 16, State: 1.2009-07-15 13:37:51.42 LogonSQL Server failed with error code 0xc0000000 to spawn a thread to process a new login or connection. Check the SQL Server error log and the Windows event logs for information about possible related problems. [CLIENT: xx.xxx.xx.xx]Event Type: ErrorEvent Source: SQLBrowserEvent ID: 8Description: The SQLBrowser service was unable to process a client request. These additional error messages or warnings may appear together with the “A significant part of sql server process memory has been paged out” error message. In several instances, these additional error messages or warnings did not appear together with the error message.
If you notice one of these error messages or warnings, consider the SQL Server working set paging that is discussed in this article as one possible cause but not as the only cause. These additional error messages or warnings could be logged because of various other conditions or causes.