Mark Minasi's Reader Forum
Mark Minasi's Reader Forum
Home | Profile | Register | Active Topics | Active Polls | Members | Search | FAQ | Minasi Forum RSS Feed
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Email, Databases, Sharepoint and more
 SQL Server
 Droping SPIDs
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Curt
Moderator

USA
6789 Posts
Status: offline

Posted - 12/06/2011 :  09:59:33 AM  Show Profile  Visit Curt's Homepage  Reply with Quote
MEMORYCLERK_SQLBUFFERPOOL (node 0) KB
VM Reserved 16809984
VM Committed 15315752
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 408

I'm trying to see what is causing random SPID drops on a SQL server that is the back end for a Share Point site.

I ran DBCC Memorystatus to enumerate the "Memory Clerk" readings.

Allocation pool is very low.
MEMORYCLERK_SQLCONNECTIONPOOL (node 0) KB
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 5152
MultiPage Allocator 0
But I'm getting 18056 error when heavy document transfer to document libs are done by users.

I have a GB of the 16 GB of the machine reserved for the OS.
Any ideas?

Curt Spanburgh
Microsoft Certified Business Solution Specialist.
Dynamics CRM MVP
Contributing Editor, Windows IT Pro

He that is walking with wise persons will become wise, but he that is having dealings with the stupid ones will fare badly.
Proverbs 13:20


Wiseman82
Old Timer

United Kingdom
521 Posts
Status: offline

Posted - 12/06/2011 :  1:51:47 PM  Show Profile  Visit Wiseman82's Homepage  Reply with Quote
I think I've seen a similar problem caused by the application not closing the connections properly. In .NET it should be using this pattern:


//C#
string connString = "Data Source=localhost;Integrated Security=SSPI;Initial Catalog=Northwind;";

using (SqlConnection conn = new SqlConnection(connString))
{
  SqlCommand cmd = conn.CreateCommand();
  cmd.CommandText = "SELECT CustomerId, CompanyName FROM Customers";
  
  conn.Open();

  using (SqlDataReader dr = cmd.ExecuteReader())
  {
    while (dr.Read())
      Console.WriteLine("{0}\t{1}", dr.GetString(0), dr.GetString(1));
  }
}

using pattern ensures that connection is closed and disposed of correctly.

I think I've also seen the issue occur when the application is using a large number of connections simultaneously - I think this can be solved by tweaking the min/max pool sizes in the connection string.

You might also want to check how many connections you have to SQL Server:
select count(*) from sys.dm_exec_sessions

Check the "max worker threads" setting. Default value should be 0 unless you've upgraded from 2000. See here:
http://blogs.msdn.com/b/sqlsakthi/archive/2011/07/06/error-18056-state-29-the-client-was-unable-to-reuse-a-session-the-failure-id-is-29-after-upgrading-sql-2000-to-sql-2008-sp2.aspx

David Wiseman
MCSE (2000/2003), MCSA (2003), MCDBA, MCITP:Database Administrator

Edited by - Wiseman82 on 12/06/2011 1:53:52 PM
Go to Top of Page

Wiseman82
Old Timer

United Kingdom
521 Posts
Status: offline

Posted - 12/06/2011 :  1:58:38 PM  Show Profile  Visit Wiseman82's Homepage  Reply with Quote
Might also be worth checking what version of SQL Server you are running and if any service packs have been applied.

David Wiseman
MCSE (2000/2003), MCSA (2003), MCDBA, MCITP:Database Administrator
Go to Top of Page

Curt
Moderator

USA
6789 Posts
Status: offline

Posted - 12/06/2011 :  4:38:55 PM  Show Profile  Visit Curt's Homepage  Reply with Quote
Excellent David.
My trace files two weeks ago lead me to that theory.

The SP_Reset_connection runs several times a second.
Then -- network protocol: TCP/IP
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed

Then: exec dbo.proc_MSS_GetSchemaHighLevelInfo

quote:
Originally posted by Wiseman82

I think I've seen a similar problem caused by the application not closing the connections properly. In .NET it should be using this pattern:


//C#
string connString = "Data Source=localhost;Integrated Security=SSPI;Initial Catalog=Northwind;";

using (SqlConnection conn = new SqlConnection(connString))
{
  SqlCommand cmd = conn.CreateCommand();
  cmd.CommandText = "SELECT CustomerId, CompanyName FROM Customers";
  
  conn.Open();

  using (SqlDataReader dr = cmd.ExecuteReader())
  {
    while (dr.Read())
      Console.WriteLine("{0}\t{1}", dr.GetString(0), dr.GetString(1));
  }
}

using pattern ensures that connection is closed and disposed of correctly.

I think I've also seen the issue occur when the application is using a large number of connections simultaneously - I think this can be solved by tweaking the min/max pool sizes in the connection string.

You might also want to check how many connections you have to SQL Server:
select count(*) from sys.dm_exec_sessions

Check the "max worker threads" setting. Default value should be 0 unless you've upgraded from 2000. See here:
http://blogs.msdn.com/b/sqlsakthi/archive/2011/07/06/error-18056-state-29-the-client-was-unable-to-reuse-a-session-the-failure-id-is-29-after-upgrading-sql-2000-to-sql-2008-sp2.aspx


Curt Spanburgh
Microsoft Certified Business Solution Specialist.
Dynamics CRM MVP
Contributing Editor, Windows IT Pro

He that is walking with wise persons will become wise, but he that is having dealings with the stupid ones will fare badly.
Proverbs 13:20


Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
Mark Minasi's Reader Forum © 2002-2011 Mark Minasi Go To Top Of Page
This page was generated in 0.12 seconds. Snitz Forums 2000