July 26, 2013 at 12:54 am
Single Server: @@Version
Microsoft SQL Server 2012 (SP1) - 11.0.3368.0 (X64)
May 22 2013 17:10:44
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
Mem: 72GB
CPU: 16 cores (2 physical)
Error in order of desc:
The client was unable to reuse a session with SPID 356, which had been reset for connection pooling. The failure ID is 46. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.
Login failed for user '<<User Name>>'. Reason: Failed to open the database '<<Database>>' configured in the login object while revalidating the login on the connection. [CLIENT: xx.xx.xx.xx]
The Issue:
So what happens is that username and database connect to the server 24/7 thousands of times with no problems and then every once in a while these errors occur. The problem is about once a day the errors occur hundreds of times and the application fails because it can't connect. It takes a restart and things go back to normal.
I know it was a kind of bug in 2008 but this is 2012. I've also done the THREAD checks and sp_who2 and things seem normal. Any ideas??
P.S.
I know what failure ID 46 is but the database exists and is available at the time.
Thanks
July 26, 2013 at 1:44 am
Yes its for error state 29 but 46 is likely going to be from the same sort of resource contention.
To help understand the state 46 error, this guy could reproduce it by dropping the user from the db.
As per the first link, what are the top wait stats during or around the error? And what is your MAXDOP and MAX server memory set to?
select top 10 *
from sys.dm_os_wait_stats
where wait_type not in --remove common waits to identify worst bottlenecks
(
'KSOURCE_WAKEUP', 'SLEEP_BPOOL_FLUSH', 'BROKER_TASK_STOP',
'XE_TIMER_EVENT', 'XE_DISPATCHER_WAIT', 'FT_IFTS_SCHEDULER_IDLE_WAIT',
'SQLTRACE_BUFFER_FLUSH', 'CLR_AUTO_EVENT', 'BROKER_EVENTHANDLER',
'LAZYWRITER_SLEEP', 'BAD_PAGE_PROCESS', 'BROKER_TRANSMITTER',
'CHECKPOINT_QUEUE', 'DBMIRROR_EVENTS_QUEUE', 'LAZYWRITER_SLEEP',
'ONDEMAND_TASK_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'LOGMGR_QUEUE',
'SLEEP_TASK', 'SQLTRACE_BUFFER_FLUSH', 'CLR_MANUAL_EVENT',
'BROKER_RECEIVE_WAITFOR', 'PREEMPTIVE_OS_GETPROCADDRESS',
'PREEMPTIVE_OS_AUTHENTICATIONOPS', 'BROKER_TO_FLUSH'
)
order by wait_time_ms desc
July 26, 2013 at 7:11 am
I will get those for you. Max mem has no limit SQL is allowed to have it all. this is a dedicated server.
Stats: Note it just happened but only a few times it wasn't the time where it does it non-stop but just incase something jumps out at you here you go:
LCK_M_U169568193823266231167565225
THREADPOOL42387154409733728539115
PAGELATCH_EX87256490296094738487217235816
LCK_M_IU106552545721383091911534
PAGELATCH_SH49102244109895766442412991937
LAZYWRITER_SLEEP787889102683121292566171904
LCK_M_IX49822848317013059812053
HADR_FILESTREAM_IOMGR_IOCOMPLETION10108351436276159849150
DIRTY_PAGE_POLL4715045143557910142390
SQLTRACE_INCREMENTAL_FLUSH_SLEEP1282851435373513767
THREADPOOL seems high right?
July 26, 2013 at 8:08 pm
Yes threadpool shouldnt be in the top waits. I would recommend setting your Sql max mem to 68GB as per best practise.
Try that first then investigate further.
July 26, 2013 at 8:22 pm
Doing that now qnd ill report back. I did that with our main clusters that have 256gb of ram but not this one. Ill try it and report back monday.
Thanks
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply