asked
SQL Server Login issue
Hi folks, There is a log in issue with SQL Server 2005 SP2 which came all of a sudden today on one of my production server. The error message is as follows - A connection was successfully established with the server,but then an error occured during the pre-login handshake.When connection to SQL Server 2005 ,this failure may be caused by the fact that under the default setting SQL Server does not allow remote connections.(provider:TCP Provider,error: 0 - An existing connection was forcibly closed by the remote host.)(Microsoft SQL Server,Error:10054) I am not even able to login with windows authentication. Any idea ? Regards, Hemant
It looks like you are running out of memory and therefore no new memory could be allocated for the new incoming connection. So after you restart the service, you are able to login, but after some period of time, the memory is consumed and no new memory could be allocated for the incoming connection. This can happen, when there is set no upper limit for the SQL Server memory and all the memory is consubed by SQL server and other processes. As there is no limit on SQL server memory, SQL Server tries to request new memory from system, but there is no other memory available and therefore sql server fails to allocate new memory. Please check whether you have set upper memory limit for the sql server instance.
Hi, as the message says, did you checked the TCP protocol whether it is enabled in the SQL Server Configuraion Manager?
The login didn't work for any account, or only for your account? Did the login work at least when connecting locally? I mean connect to the server by RD and try to connect to server using SSMS with Shared Memory (needs to be enabled in the SQL Server Configuration Manager). Then check whether rights are property set for the TSQL Default TCP Endpoint. (When you select Public role in the server roles and select properties, in the securables should be granted Connect on the TSQL Default TCP Endpoint.
Then, did you take a look on the SQL Server logs, if there are some details?
Here is the log details - 2011-05-23 10:28:09.08 spid2s LazyWriter: warning, no free buffers found. 2011-05-23 10:28:09.33 spid2s Memory Manager VM Reserved = 8552736 KB VM Committed = 8535272 KB AWE Allocated = 0 KB Reserved Memory = 1024 KB Reserved Memory In Use = 0 KB 2011-05-23 10:28:09.33 spid2s Memory node Id = 0 VM Reserved = 8546976 KB VM Committed = 8529600 KB AWE Allocated = 0 KB SinglePage Allocator = 3094312 KB MultiPage Allocator = 106736 KB 2011-05-23 10:28:09.34 spid2s MEMORYCLERK_SQLGENERAL (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 62392 KB MultiPage Allocator = 6416 KB 2011-05-23 10:28:09.34 spid2s MEMORYCLERK_SQLBUFFERPOOL (Total) VM Reserved = 8413184 KB VM Committed = 8396800 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 0 KB MultiPage Allocator = 8 KB 2011-05-23 10:28:09.34 spid2s MEMORYCLERK_SQLQUERYEXEC (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 560 KB MultiPage Allocator = 104 KB 2011-05-23 10:28:09.34 spid2s MEMORYCLERK_SQLOPTIMIZER (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 2032 KB MultiPage Allocator = 112 KB 2011-05-23 10:28:09.34 spid2s MEMORYCLERK_SQLUTILITIES (Total) VM Reserved = 480 KB VM Committed = 480 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 416 KB MultiPage Allocator = 0 KB 2011-05-23 10:28:09.34 spid2s MEMORYCLERK_SQLSTORENG (Total) VM Reserved = 3264 KB VM Committed = 3264 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 5760 KB MultiPage Allocator = 1056 KB 2011-05-23 10:28:09.34 spid2s MEMORYCLERK_SQLCONNECTIONPOOL (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 4648 KB MultiPage Allocator = 0 KB 2011-05-23 10:28:09.34 spid2s MEMORYCLERK_SQLCLR (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 8 KB MultiPage Allocator = 0 KB 2011-05-23 10:28:09.34 spid2s MEMORYCLERK_SQLSERVICEBROKER (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 72 KB MultiPage Allocator = 304 KB 2011-05-23 10:28:09.37 spid2s MEMORYCLERK_SQLHTTP (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 8 KB MultiPage Allocator = 0 KB 2011-05-23 10:28:09.37 spid2s MEMORYCLERK_SNI (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 472 KB MultiPage Allocator = 16 KB 2011-05-23 10:28:09.37 spid2s MEMORYCLERK_FULLTEXT (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 16 KB MultiPage Allocator = 0 KB 2011-05-23 10:28:09.37 spid2s MEMORYCLERK_SQLXP (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 16 KB MultiPage Allocator = 0 KB 2011-05-23 10:28:09.37 spid2s MEMORYCLERK_BHF (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 424 KB MultiPage Allocator = 0 KB 2011-05-23 10:28:09.37 spid2s MEMORYCLERK_SQLQERESERVATIONS (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 5408 KB MultiPage Allocator = 0 KB 2011-05-23 10:28:09.37 spid2s MEMORYCLERK_HOST (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 200 KB MultiPage Allocator = 112 KB 2011-05-23 10:28:09.37 spid2s MEMORYCLERK_SOSNODE (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 23048 KB MultiPage Allocator = 16320 KB 2011-05-23 10:28:09.37 spid2s MEMORYCLERK_SQLSERVICEBROKERTRANSPORT (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 48 KB MultiPage Allocator = 0 KB 2011-05-23 10:28:09.37 spid2s CACHESTORE_OBJCP (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 242112 KB MultiPage Allocator = 2760 KB 2011-05-23 10:28:09.37 spid2s CACHESTORE_SQLCP (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 2677888 KB MultiPage Allocator = 77000 KB 2011-05-23 10:28:09.37 spid2s CACHESTORE_PHDR (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 31328 KB MultiPage Allocator = 1688 KB 2011-05-23 10:28:09.37 spid2s CACHESTORE_XPROC (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 88 KB MultiPage Allocator = 0 KB 2011-05-23 10:28:09.37 spid2s CACHESTORE_TEMPTABLES (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 80 KB MultiPage Allocator = 0 KB 2011-05-23 10:28:09.37 spid2s CACHESTORE_NOTIF (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 16 KB MultiPage Allocator = 0 KB 2011-05-23 10:28:09.37 spid2s CACHESTORE_VIEWDEFINITIONS (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 16 KB MultiPage Allocator = 0 KB 2011-05-23 10:28:09.37 spid2s CACHESTORE_XMLDBTYPE (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 8 KB MultiPage Allocator = 0 KB 2011-05-23 10:28:09.37 spid2s CACHESTORE_XMLDBELEMENT (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 8 KB MultiPage Allocator = 0 KB 2011-05-23 10:28:09.37 spid2s CACHESTORE_XMLDBATTRIBUTE (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 8 KB MultiPage Allocator = 0 KB 2011-05-23 10:28:09.37 spid2s CACHESTORE_STACKFRAMES (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 0 KB MultiPage Allocator = 8 KB 2011-05-23 10:28:09.37 spid2s CACHESTORE_BROKERTBLACS (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 72 KB MultiPage Allocator = 0 KB 2011-05-23 10:28:09.37 spid2s CACHESTORE_BROKERKEK (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 8 KB MultiPage Allocator = 0 KB 2011-05-23 10:28:09.37 spid2s CACHESTORE_BROKERDSH (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 8 KB MultiPage Allocator = 0 KB 2011-05-23 10:28:09.37 spid2s CACHESTORE_BROKERUSERCERTLOOKUP (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 8 KB MultiPage Allocator = 0 KB 2011-05-23 10:28:09.37 spid2s CACHESTORE_BROKERRSB (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 8 KB MultiPage Allocator = 0 KB 2011-05-23 10:28:09.37 spid2s CACHESTORE_BROKERREADONLY (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 32 KB MultiPage Allocator = 0 KB 2011-05-23 10:28:09.37 spid2s CACHESTORE_BROKERTO (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 8 KB MultiPage Allocator = 0 KB 2011-05-23 10:28:09.37 spid2s CACHESTORE_EVENTS (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 16 KB MultiPage Allocator = 0 KB 2011-05-23 10:28:09.37 spid2s CACHESTORE_SYSTEMROWSET (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 1432 KB MultiPage Allocator = 0 KB 2011-05-23 10:28:09.37 spid2s USERSTORE_SCHEMAMGR (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 13976 KB MultiPage Allocator = 88 KB 2011-05-23 10:28:09.37 spid2s USERSTORE_DBMETADATA (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 10896 KB MultiPage Allocator = 0 KB 2011-05-23 10:28:09.37 spid2s USERSTORE_TOKENPERM (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 568 KB MultiPage Allocator = 0 KB 2011-05-23 10:28:09.37 spid2s USERSTORE_OBJPERM (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 208 KB MultiPage Allocator = 0 KB 2011-05-23 10:28:09.37 spid2s USERSTORE_SXC (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 1792 KB MultiPage Allocator = 0 KB 2011-05-23 10:28:09.37 spid2s OBJECTSTORE_LBSS (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 128 KB MultiPage Allocator = 512 KB 2011-05-23 10:28:09.37 spid2s OBJECTSTORE_SNI_PACKET (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 7304 KB MultiPage Allocator = 48 KB 2011-05-23 10:28:09.37 spid2s OBJECTSTORE_SERVICE_BROKER (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 272 KB MultiPage Allocator = 0 KB 2011-05-23 10:28:09.37 spid2s OBJECTSTORE_LOCK_MANAGER (Total) VM Reserved = 16384 KB VM Committed = 16384 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 5864 KB MultiPage Allocator = 0 KB 2011-05-23 10:28:09.37 spid2s Buffer Distribution: Stolen=12581 Free=0 Cached=374247 Database (clean)=637968 Database (dirty)=7384 I/O=0, Latched=12 2011-05-23 10:28:09.37 spid2s Buffer Counts: Committed=1032192 Target=1048458 Hashed=645364 Internal Reservation=209691 External Reservation=674 Stolen Potential=608533 Min Free=256 Visible=1048458 Available Paging File=2287669248 2011-05-23 10:28:09.37 spid2s Procedure Cache: TotalProcs=28328 TotalPages=379108 InUsePages=15448 2011-05-23 10:28:09.37 spid2s Process physical/virtual memory pressure: 0/0 System physical memory pressure: 0 2011-05-23 10:28:09.40 spid2s Global Memory Objects: Resource= 516 Locks= 736 SE Schema Mgr= 1757 SQLCache= 7172 Replication= 515 ServerGlobal= 53 XPGlobal= 2 Xact= 217 SETLS= 8 DatasetMemObjs= 16 SubpDescPmos= 10 SortTables= 2 2011-05-23 10:28:09.40 spid2s Query Memory Objects: Grants=5 Waiting=0 Maximum=558340 Available=557664 Limit=558355 2011-05-23 10:28:09.40 spid2s Query Memory Objects: Next Request=0 Waiting For=0 Cost=0 Timeout=0 Wait Time=0 Last Target=571155 2011-05-23 10:28:09.40 spid2s Small Query Memory Objects: Grants=0 Waiting=0 Maximum=12800 Available=12800 Limit=12800 2011-05-23 10:28:09.40 spid2s Optimization Queue Overall Memory = 6884573184 Target Memory = 3506307072 Last Notification = GROW Timeout = 6 Early Termination Factor = 5 2011-05-23 10:28:09.40 spid2s Small Gateway Configured Units = 32 Available Units = 32 Acquires = 0 Waiters = 0 Threshold Factor = 380000 Threshold = 380000 2011-05-23 10:28:09.40 spid2s Medium Gateway Configured Units = 8 Available Units = 8 Acquires = 0 Waiters = 0 Threshold Factor = 12 Threshold = -1 2011-05-23 10:28:09.40 spid2s Big Gateway Configured Units = 1 Available Units = 1 Acquires = 0 Waiters = 0 Threshold Factor = 8 Threshold = -1 2011-05-23 10:28:09.40 spid2s MEMORYBROKER_FOR_CACHE Allocations = 374251 Rate = 0 Target Allocations = 789729 Future Allocations = 0 Last Notification = GROW 2011-05-23 10:28:09.40 spid2s MEMORYBROKER_FOR_STEAL Allocations = 12538 Rate = 0 Target Allocations = 428016 Future Allocations = 0 Last Notification = GROW 2011-05-23 10:28:09.40 spid2s MEMORYBROKER_FOR_RESERVE Allocations = 676 Rate = 0 Target Allocations = 609246 Future Allocations = 193768 Last Notification = GROW
