SQL Server Performance

SQL Success Audits

Discussion in 'SQL Server 2005 General Developer Questions' started by dbuser123, Feb 12, 2007.

  1. dbuser123 New Member

    we have built an application which maintains Qs in the database. We constantly
    create ADO connections using a connection string. and we close connections
    when done with the operation.

    so this is constantly creating login/logout success audits in SQL server.

    Some customer has these audits setup and is upset that they see
    thousands of these audits in the eventlog every day.

    Have we designed the app improperly ? or the customer just being a pain ?
  2. MohammedU New Member

    You can use connection pooling instead login/logout for each time...<br /><br />Customer always right<img src='/community/emoticons/emotion-1.gif' alt=':)' /> when you are selling something<img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br /<a target="_blank" href=http://msdn2.microsoft.com/en-us/library/ms810829.aspx>http://msdn2.microsoft.com/en-us/library/ms810829.aspx</a><br /<a target="_blank" href=http://www.sql-server-performance.com/sk_connection_pooling_myths.asp>http://www.sql-server-performance.com/sk_connection_pooling_myths.asp</a><br /<a target="_blank" href=http://www.dotnetspider.com/kb/Article1290.aspx>http://www.dotnetspider.com/kb/Article1290.aspx</a><br /><br /><br />MohammedU.<br />Moderator<br />SQL-Server-Performance.com
  3. satya Moderator

    What kind of events you are auditing, if it is set to ALL then yes it is a normal behaviour to see such 'more' events in a day. You have to consider what kind of events you would like to monitor or take a thorough check of process at that client.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  4. dbuser123 New Member

    I always thought connection pooling is by default enabled if the connectionstring does not change. no ? how do i go about enabling it using ADO ? can i use the connectionstring for that ?

    The customer is seeing successful login/logout events in security log.

  5. satya Moderator

    http://www.sql-server-performance.com/bl_asp_ado.asp

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  6. dbuser123 New Member

    im already closing the connections properly and performance is pretty good.

    Is there anything special that i need to do to enable connection pooling ? I did go thru' the link that you sent me and im closing the connection and also setting it to null.
  7. dbuser123 New Member

    im using SQL Oledb provider for SQL Server.
  8. satya Moderator

    quote:For instance, currently the Microsoft OLEDB provider for ODBC uses ODBC 3.0 to connect to data sources. This means that OLEDB can take advantage of ODBC 3.0 connection pooling. ADO uses this OLEDB provider to connect to Datasources. Both ADO and this OLEDB provider can take advantage of ODBC 3.0's connection pooling.

    Currently the only OLEDB provider using ODBC is Microsoft's OLEDB provider, it can take advantage of ODBC connection pooling. Because ADO uses OLEDB, ADO can also take advantage of ODBC connection pooling. Active Server pages can also use connection pooling if they use ADO as their database interface

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  9. dbuser123 New Member

    Thanks for the response. The customer is breathing down my neck <img src='/community/emoticons/emotion-6.gif' alt=':(' /> <br /><br />But do i need to do anything special to enable this Connection Pooling with the Microsoft SQL Oledb driver ?
  10. MohammedU New Member

  11. dbuser123 New Member

    When i run this<br /><br />SELECT spid, dbid,uid=rtrim(loginame), Program_name=rtrim(Program_name),<br />dbname=db_name(dbid), status=rtrim(status) FROM master.dbo.sysprocesses <br /><br /><br />I see the connection showing up and in the next second it is gone.<br /><br /><br />The connectionstring i have is<br /><br />Provider=SQLOLEDB.1;Integrated Security=SSPI<img src='/community/emoticons/emotion-4.gif' alt=';P' />ersist Security Info=False<img src='/community/emoticons/emotion-4.gif' alt=';p' />ooling=true;Min Pool Size=3;Max Pool Size=5;Connection Lifetime=120;Initial Catalog=test;Data Source=TESTMACHINESQLEXPRESS;<br /><br /><br />And then in the log, the log grows almost every second 3 entiries !!<br />"Login succeeded for user 'TESTMACHINEUser'. Connection: trusted...."<br />"Login succeeded for user 'TESTMACHINEUser'. Connection: trusted...."<br />"Login succeeded for user 'TESTMACHINEUser'. Connection: trusted...."<br />
  12. dbuser123 New Member

Share This Page