none
Connection pooling does not work RRS feed

  • Question

  • not work = sp_reset_connection is followed by a logout and the next is followed by a login, it simply logs in and logs out at -each- batch operation.
    No matter how I try to find the reason for this.

    Configuration
    SQL Server 2005 Sp3

    Using Linq to SQL (I use the C# using statement, contexts + connections are disposed)
    Using ASP.NET 3.5
    Using VISTA (development) And Windows 2008 (production).
    IIS7

     (unfortunately I cannot attach a picture) but this is what I see.

    RPC:Completed exec sp_reset_connection  .Net SqlClient Data Provider  mydb 0 0 0 0 880 57 2009-01-04 23:28:55.750 2009-01-04 23:28:55.750 0X00000000000000002600730070005F00720065007300650074005F0063006F006E006E0065006300740069006F006E00
    Audit Login -- network protocol: TCP/IP

    [snip]
    set datefirst 7
    set transaction isolation level read committed
     .Net SqlClient Data Provider  mydb     880 57 2009-01-04 23:28:55.750  
    RPC:Completed exec sp_executesql N'SELECT [t0].[id], [t0].[textid], [t0].[wordid], [t0].[IsCapitalized], [t0].[AddSpace], [t0].[IsAllCaps]
    FROM [dbo].[TextFootNotes] AS [t0]
    WHERE [t0].[textid] = @x1',N'@x1 int',@x1=266 .Net SqlClient Data Provider  mydb 0 32 0 0 880 57 2009-01-04 23:28:55.750 2009-01-04 23:28:55.750
    Audit Logout  .Net SqlClient Data Provider  mydb 0 3019 0 0 880 57 2009-01-04 23:28:55.750 2009-01-04 23:28:55.750 
    RPC:Completed exec sp_reset_connection  .Net SqlClient Data Provider  mydb 0 0 0 0 880 57 2009-01-04 23:28:55.750 2009-01-04 23:28:55.750 0X00000000000000002600730070005F00720065007300650074005F0063006F006E006E0065006300740069006F006E00
    Audit Login -- network protocol: TCP/IP
    [snip]
     .Net SqlClient Data Provider  mydb     880 57 2009-01-04 23:28:55.750  
    RPC:Completed exec sp_executesql N'SELECT [t0].[id], [t0].[textid], [t0].[wordid], [t0].[IsCapitalized], [t0].[AddSpace], [t0].[IsAllCaps]
    FROM [dbo].[TextFootNotes] AS [t0]
    WHERE [t0].[textid] = @x1',N'@x1 int',@x1=268 .Net SqlClient Data Provider  mydb 0 30 0 0 880 57 2009-01-04 23:28:55.750 2009-01-04 23:28:55.750

    Audit Logout  .Net SqlClient Data Provider  mydb 0 3049 0 0 880 57 2009-01-04 23:28:55.750 2009-01-04 23:28:55.750 
    RPC:Completed exec sp_reset_connection  .Net SqlClient Data Provider  mydb 0 0 0 0 880 57 2009-01-04 23:28:55.750 2009-01-04 23:28:55.750 0X00000000000000002600730070005F00720065007300650074005F0063006F006E006E0065006300740069006F006E00
    Audit Login -- network protocol: TCP/IP
    [snip]
    set transaction isolation level read committed
     .Net SqlClient Data Provider  mydb     880 57 2009-01-04 23:28:55.750  
    RPC:Completed exec sp_executesql N'SELECT [t0].[id], [t0].[textid], [t0].[wordid], [t0].[IsCapitalized], [t0].[AddSpace], [t0].[IsAllCaps]
    FROM [dbo].[TextFootNotes] AS [t0]
    WHERE [t0].[textid] = @x1',N'@x1 int',@x1=270 .Net SqlClient Data Provider  mydb 0 2 0 0 880 57 2009-01-04 23:28:55.750 2009-01-04 23:28:55.750
    Audit Logout  .Net SqlClient Data Provider  mydb 0 3051 0 0 880 57 2009-01-04 23:28:55.750 2009-01-04 23:28:55.750 
    RPC:Completed exec sp_reset_connection  .Net SqlClient Data Provider  mydb 0 0 0 0 880 57 2009-01-04 23:28:55.750 2009-01-04 23:28:55.750 0X00000000000000002600730070005F00720065007300650074005F0063006F006E006E0065006300740069006F006E00
    Audit Login -- network protocol: TCP/IP
    [snip]
    set transaction isolation level read committed
     .Net SqlClient Data Provider  mydb     880 57 2009-01-04 23:28:55.750  
    RPC:Completed exec sp_executesql N'SELECT [t0].[id], [t0].[textid], [t0].[wordid], [t0].[IsCapitalized], [t0].[AddSpace], [t0].[IsAllCaps]
    FROM [dbo].[TextFootNotes] AS [t0]
    WHERE [t0].[textid] = @x1',N'@x1 int',@x1=272 .Net SqlClient Data Provider  mydb 0 2 0 0 880 57 2009-01-04 23:28:55.767 2009-01-04 23:28:55.767
    Audit Logout  .Net SqlClient Data Provider  mydb 0 3053 0 16 880 57 2009-01-04 23:28:55.750 2009-01-04 23:28:55.767 
    RPC:Completed exec sp_reset_connection  .Net SqlClient Data Provider  mydb 0 0 0 0 880 57 2009-01-04 23:28:55.767 2009-01-04 23:28:55.767 0X00000000000000002600730070005F00720065007300650074005F0063006F006E006E0065006300740069006F006E00
    Audit Login -- network protocol: TCP/IP
    [snip]
     .Net SqlClient Data Provider  mydb     880 57 2009-01-04 23:28:55.767  
    RPC:Completed exec sp_executesql N'SELECT [t0].[id], [t0].[textid], [t0].[wordid], [t0].[IsCapitalized], [t0].[AddSpace], [t0].[IsAllCaps]
    FROM [dbo].[TextFootNotes] AS [t0]
    WHERE [t0].[textid] = @x1',N'@x1 int',@x1=274 .Net SqlClient Data Provider  mydb 0 28 0 0 880 57 2009-01-04 23:28:55.767 2009-01-04 23:28:55.767
    Audit Logout  .Net SqlClient Data Provider  mydb 0 3081 0 0 880 57 2009-01-04 23:28:55.767 2009-01-04 23:28:55.767 

    • Moved by Anton Klimov - MSFT Monday, January 5, 2009 9:43 PM (Moved from SQL Server Data Access to ADO.NET Data Providers)
    Sunday, January 4, 2009 10:33 PM