Sarah Henwood : sql server 2005http://sqlblog.com/blogs/sarah_henwood/archive/tags/sql+server+2005/default.aspxTags: sql server 2005enCommunityServer 2.1 SP2 (Build: 61129.1)Connection Failures, SQL 2005 Appears Unresponsivehttp://sqlblog.com/blogs/sarah_henwood/archive/2007/07/23/connection-failures-sql-2005-appears-unresponsive.aspxMon, 23 Jul 2007 12:30:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:1907sarahhen2http://sqlblog.com/blogs/sarah_henwood/comments/1907.aspxhttp://sqlblog.com/blogs/sarah_henwood/commentrss.aspx?PostID=1907<P>Here is a situation one of my client's recently encountered on SQL Server 2005.&nbsp; It was incredibly painful and a challenge to determine what was happening, plus it's an issue that is supposed to be fixed in SP2.&nbsp; So wanted to share the details of the symptoms in case you experience it too.</P>
<P>&nbsp;My client is on a SP2+ build.&nbsp; They recently conducted a successfull migration from SQL Server 2000 to 2005.&nbsp; They have a very high heavy rate of OLTP activity.&nbsp; Things were chugging along just fine for a couple of hours after migration, and then all of a sudden web servers started throwing connectivity errors.&nbsp; A local connection to the server could not even be made.&nbsp; After establishing a connection using the DAC, first thing checked was the state of the SPID's on SQL - I fully expected to see excessive waits due to blocking or some other type of wait.&nbsp; However, in this case it was strange - everything looked as I would expect for a normal functioning system - a small handful of SPID's were waiting on something with short waits you would expect to see at any period in time, but nothing excessive or abnormal looking (no high cpu, no indication of any runaway or expensive queries etc.)&nbsp; The majority of SPID had a zero waittype and none were waiting on working thread.&nbsp; This is a very key symptom of the issue we encountered - that SPID do not look problematic and you do not have a blocking or other wait type of bottleneck.&nbsp; There also are no network connectivity issues or anything else that is typical of this type of symptom.&nbsp; </P>
<P>We then issued a DBCC FREESYSTEMCACHE ('TokenAndPermUserStore')&nbsp;&nbsp;and then immediately, the issue cleared and connections could be made to SQL and processing continue.&nbsp; (Some notice&nbsp;resolution with DBCC FREEPROCCACHE - it is because freeing the procedure cache also&nbsp;dumps this store.&nbsp;&nbsp;For this issue, you only need to dump the tokenandpermuserstore.)&nbsp; I was very surprised since my client was already on SP2.&nbsp; So it appears there still is a different flavor of this issue out there with some varying symptoms.&nbsp; (RE:&nbsp; <A href="http://support.microsoft.com/kb/927396">http://support.microsoft.com/kb/927396</A>)</P>
<P>&nbsp;My client has a support case open with Microsoft and is still being investigated and debugged.&nbsp; In the meantime, the workaround was to monitor the size of the TokenAndPermUserStore and free it when it got over 40MB.&nbsp; Initially we started with a size of 100MB but still sporadically encountered the problem.&nbsp; (I suspect it may be more related to number of entries, but size is what we were able to work around with.)&nbsp; Below is a query you can use that will report the size and number of objects in the store if you want to track and trend.&nbsp; You can add in additional logic if you need to free the cache if it is over a certain size or number of objects.</P>
<P class=EC_MsoNormal>&nbsp; SELECT SUM(single_pages_kb + multi_pages_kb) as CacheSize, entries_count,entries_in_use_count<BR>&nbsp; FROM sys.dm_os_memory_clerks <BR>&nbsp; WHERE name = 'TokenAndPermUserStore'</P>
<P class=EC_MsoNormal>&nbsp;Hope this helps!&nbsp; And I will post with any new updates.</P><img src="http://sqlblog.com/aggbug.aspx?PostID=1907" width="1" height="1">dbcc freeproccachesql server 2005dbcc freesystemcacheconnectioncannot connect