Logging with SQL Server

  • EdSwiedler

    SSC Enthusiast

    Points: 190

    Comments posted to this topic are about the item Logging with SQL Server

  • david.wright-948385

    SSCarpal Tunnel

    Points: 4028

    Very often logging needs to be done within a transaction and be made available even if the transaction is rolled back. Aren't SB messages rolled back, so in that situation the entries won't be written to the log file? Wouldn't a simpler solution be a stored proc or CLR that writes direct to a file? Although that would mean the calling sessions would have to wait for the file system...

  • david.wright-948385

    SSCarpal Tunnel

    Points: 4028

    ... or even easier than that:

    raiserror("something to log",0,0) with log

    Not ideal cos it clutters the sql and application logs, but very simple and reliable.

  • cmille19

    SSCertifiable

    Points: 5950

    Interesting article, you've obviously put some thought into how to architect a robust logging environment. I wish someone had done the same in my environment, instead hundreds of programs use centralized logging to single table. Now, I'm all for centralized and standardized logging however, if this one table on this one database on this one clustered database server isn't available--everything stops. So, logging must be asynch and not cause the application to fail. I think your solution accomplishes this to some extent by using Service Broker.

    One suggestion, you might want to take a look at a more featured solution that also uses Service Broker and the open source Log4Net framework is described in this Dr Dobb's article:

    Enterprise Application Logging Using the SQL Server 2005 Service Broker

  • rbuscemi

    Grasshopper

    Points: 21

    Use Log4net!!!!!! I been using it for 3 years solid straight to debug software applications (ASP.NET and WinForms). It is open sourced (FREE) and it is bullet proof. Each application has its own log file to write to with different levels of messages. Levels of messages are controlled by the log4net xml file used by the application.

  • Phillip - Texas

    SSC-Addicted

    Points: 415

    Author has provided the full SQL Script for creating the stored procedure. Thanks

  • EdSwiedler

    SSC Enthusiast

    Points: 190

    My error.

    I should have included the entire procedure:

    CREATE PROCEDURE [dbo].[ProcessLogQueue]

    AS

    BEGIN

    SET NOCOUNT ON;

    BEGIN TRANSACTION

    WAITFOR (RECEIVE TOP(1)

    CONVERT (XML, CONVERT (varchar(MAX), message_body) )

    FROM

    LogTargetQueue)

    COMMIT TRANSACTION

    END

  • EdSwiedler

    SSC Enthusiast

    Points: 190

    Yes, I could have done that, and thought about it. I wanted to separate the DB stuff from the OS stuff.

    The issue of a failed transaction, however is something that will take a little thought.

  • SQLRNNR

    SSC Guru

    Points: 281243

    Thanks for the article

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Phillip - Texas

    SSC-Addicted

    Points: 415

    Can anyone post a working code example of a Windows Service? I tried using the code in the article and had to play with it a bit to get it to compile. I then added an installer project and eventually installed the service in Windows. My service starts then stops and I get the message that "your service started then stopped..."

    Here is what I have:

    using System;

    using System.Collections.Generic;

    using System.ComponentModel;

    using System.Data;

    using System.Diagnostics;

    using System.Linq;

    using System.ServiceProcess;

    using System.Text;

    using System.Data.SqlClient;

    using System.Xml;

    using System.IO;

    namespace svcbroker

    {

    public partial class Service1 : ServiceBase

    {

    private bool m_Terminate = false;

    private string LogFolder = "c:\\svcbroker\\LogFolder\\";

    private string ArchiveFolder = "c:\\svcbroker\\ArchiveFolder\\";

    public Service1()

    {

    InitializeComponent();

    }

    protected override void OnStart(string[] args)

    {

    using (SqlConnection Conn = new SqlConnection("Data Source=(local);Initial Catalog=ServiceBroker;Integrated Security=SSPI"))

    {

    Conn.Open();

    using (SqlCommand Cmd = Conn.CreateCommand())

    {

    Cmd.CommandText = "ProcessLogQueue";

    Cmd.CommandType = System.Data.CommandType.StoredProcedure;

    Cmd.CommandTimeout = 0; // no timeout

    while (!this.m_Terminate) // looping until the service is stopped

    {

    string Response = Cmd.ExecuteScalar().ToString(); // execute the command

    if (Response.Length > 0)

    {

    XmlDocument Doc = new XmlDocument();

    Doc.LoadXml(Response);

    XmlNode RootNode = Doc.SelectSingleNode("LOG");

    XmlNode RowNode = RootNode.SelectSingleNode("Row");

    string ProcessName = RowNode.SelectSingleNode("ProcessName").InnerText;

    string MachineName = RowNode.SelectSingleNode("MachineName").InnerText;

    string MachineFolder = Path.Combine(this.LogFolder, MachineName);

    if (!Directory.Exists(MachineFolder))

    {

    try

    {

    Directory.CreateDirectory(MachineFolder);

    }

    catch (Exception Ex)

    {

    // log the failure to a logfile for the Windows Service

    return;

    }

    }

    // create the name of the log file

    string FileName = Path.Combine(MachineFolder, ProcessName + ".log");

    try

    {

    this.CheckLog(MachineName, FileName); // does the log file nee to be archived?

    }

    catch (Exception Ex)

    {

    // log the failure to a logfile for the Windows Service

    return;

    }

    try

    {

    using (StreamWriter SW = new StreamWriter(FileName, true))

    {

    SW.WriteLine(string.Format("{0} {1}", RowNode.SelectSingleNode("LogTime").InnerText,

    RowNode.SelectSingleNode("ProcessMessage").InnerText));

    }

    }

    catch (SqlException Ex)

    {

    // log the failure to a logfile for the Windows Service and quit

    }

    catch (System.Threading.ThreadAbortException)

    {

    // we have been ordered to quit

    }

    catch (Exception Ex)

    {

    // log the failure to a logfile for the Windows Service and quit

    }

    }

    }

    }

    }

    }

    private void CheckLog ( string MachineName, string FileName )

    {

    FileInfo FI = new FileInfo ( FileName );

    if ( FI.Exists )

    {

    DateTime Today = Convert.ToDateTime ( DateTime.Now.ToShortDateString () );

    if ( FI.LastWriteTime < Today )

    {

    string ArchiveFileName = Path.GetFileName ( FileName ).Replace ( ".log", FI.LastWriteTime.ToString ( "yyyyMMdd" ) + ".log" );

    string MachineFolder = Path.Combine ( this.ArchiveFolder, MachineName );

    if ( !Directory.Exists ( MachineFolder ) )

    {

    Directory.CreateDirectory ( MachineFolder );

    }

    FI.MoveTo ( Path.Combine ( MachineFolder, ArchiveFileName ) );

    }

    }

    }

    protected override void OnStop()

    {

    }

    }

    }

  • david.wright-948385

    SSCarpal Tunnel

    Points: 4028

    Looks like you're catching the most likely causes but ignoring them. Take out the try/catch blocks and look in the application log.

  • EdSwiedler

    SSC Enthusiast

    Points: 190

    Phillip - Texas (1/6/2010)


    Can anyone post a working code example of a Windows Service? I tried using the code in the article and had to play with it a bit to get it to compile. I then added an installer project and eventually installed the service in Windows. My service starts then stops and I get the message that "your service started then stopped..."

    Here is what I have:

    using System;

    using System.Collections.Generic;

    using System.ComponentModel;

    using System.Data;

    using System.Diagnostics;

    using System.Linq;

    using System.ServiceProcess;

    using System.Text;

    using System.Data.SqlClient;

    using System.Xml;

    using System.IO;

    namespace svcbroker

    {

    public partial class Service1 : ServiceBase

    {

    private bool m_Terminate = false;

    private string LogFolder = "c:\\svcbroker\\LogFolder\\";

    private string ArchiveFolder = "c:\\svcbroker\\ArchiveFolder\\";

    public Service1()

    {

    InitializeComponent();

    }

    protected override void OnStart(string[] args)

    {

    using (SqlConnection Conn = new SqlConnection("Data Source=(local);Initial Catalog=ServiceBroker;Integrated Security=SSPI"))

    {

    Conn.Open();

    using (SqlCommand Cmd = Conn.CreateCommand())

    {

    Cmd.CommandText = "ProcessLogQueue";

    Cmd.CommandType = System.Data.CommandType.StoredProcedure;

    Cmd.CommandTimeout = 0; // no timeout

    while (!this.m_Terminate) // looping until the service is stopped

    {

    string Response = Cmd.ExecuteScalar().ToString(); // execute the command

    if (Response.Length > 0)

    {

    XmlDocument Doc = new XmlDocument();

    Doc.LoadXml(Response);

    XmlNode RootNode = Doc.SelectSingleNode("LOG");

    XmlNode RowNode = RootNode.SelectSingleNode("Row");

    string ProcessName = RowNode.SelectSingleNode("ProcessName").InnerText;

    string MachineName = RowNode.SelectSingleNode("MachineName").InnerText;

    string MachineFolder = Path.Combine(this.LogFolder, MachineName);

    if (!Directory.Exists(MachineFolder))

    {

    try

    {

    Directory.CreateDirectory(MachineFolder);

    }

    catch (Exception Ex)

    {

    // log the failure to a logfile for the Windows Service

    return;

    }

    }

    // create the name of the log file

    string FileName = Path.Combine(MachineFolder, ProcessName + ".log");

    try

    {

    this.CheckLog(MachineName, FileName); // does the log file nee to be archived?

    }

    catch (Exception Ex)

    {

    // log the failure to a logfile for the Windows Service

    return;

    }

    try

    {

    using (StreamWriter SW = new StreamWriter(FileName, true))

    {

    SW.WriteLine(string.Format("{0} {1}", RowNode.SelectSingleNode("LogTime").InnerText,

    RowNode.SelectSingleNode("ProcessMessage").InnerText));

    }

    }

    catch (SqlException Ex)

    {

    // log the failure to a logfile for the Windows Service and quit

    }

    catch (System.Threading.ThreadAbortException)

    {

    // we have been ordered to quit

    }

    catch (Exception Ex)

    {

    // log the failure to a logfile for the Windows Service and quit

    }

    }

    }

    }

    }

    }

    private void CheckLog ( string MachineName, string FileName )

    {

    FileInfo FI = new FileInfo ( FileName );

    if ( FI.Exists )

    {

    DateTime Today = Convert.ToDateTime ( DateTime.Now.ToShortDateString () );

    if ( FI.LastWriteTime < Today )

    {

    string ArchiveFileName = Path.GetFileName ( FileName ).Replace ( ".log", FI.LastWriteTime.ToString ( "yyyyMMdd" ) + ".log" );

    string MachineFolder = Path.Combine ( this.ArchiveFolder, MachineName );

    if ( !Directory.Exists ( MachineFolder ) )

    {

    Directory.CreateDirectory ( MachineFolder );

    }

    FI.MoveTo ( Path.Combine ( MachineFolder, ArchiveFileName ) );

    }

    }

    }

    protected override void OnStop()

    {

    }

    }

    }

    I will send you a copy in the morning of what I use.

  • EdSwiedler

    SSC Enthusiast

    Points: 190

    Regarding the user of a Windows Service, the following is the code that we use:

    using System;

    using System.ServiceProcess;

    using System.Threading;

    namespace ROAMLogService

    {

    public delegate void ThreadCallback ( string Message );

    public partial class ROAMLogService : ServiceBase

    {

    private ServiceThread ST;

    private Thread m_ServiceThread;

    public ROAMLogService ()

    {

    InitializeComponent ();

    }

    public void StartService ()

    {

    this.ST = new ServiceThread ( new ThreadCallback ( ServiceEnded ) );

    this.m_ServiceThread = new Thread ( new ThreadStart ( ST.Run ) );

    this.m_ServiceThread.Start ();

    }

    private void ServiceEnded ( string Message )

    {

    Thread.Sleep ( 3000 );

    this.StartService ();

    }

    protected override void OnStart ( string[] args )

    {

    this.StartService ();

    }

    protected override void OnStop ()

    {

    this.ST.Terminate = true;

    Thread.Sleep ( 3000 );

    if ( this.m_ServiceThread != null )

    {

    this.m_ServiceThread.Abort ();

    this.m_ServiceThread.Join ();

    }

    }

    }

    }

    The Service Thread is where all of the work is done:

    using System;

    using System.Data.SqlClient;

    using System.IO;

    using System.Xml;

    namespace ROAMLogService

    {

    class ServiceThread

    {

    private bool m_Terminate = false;

    public bool Terminate

    {

    set { this.m_Terminate = value; }

    }

    private string LogConnectionString;

    private string LogFolder = "";

    private string ArchiveFolder = "";

    private ThreadCallback m_Callback;

    public ServiceThread ( ThreadCallback Callback )

    {

    this.m_Callback = Callback;

    this.LogFolder = System.Configuration.ConfigurationManager.AppSettings[ "LogFolder" ].ToString ();

    this.ArchiveFolder = this.LogFolder + @"\Old Logs";

    this.LogConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings[ "Logging" ].ToString ();

    if ( this.LogConnectionString == null )

    {

    this.m_Callback ( "Log ConnectionString not found - exiting" );

    this.m_Terminate = true;

    }

    }

    public void Run ()

    {

    try

    {

    using ( SqlConnection Conn = new SqlConnection ( this.LogConnectionString ) )

    {

    Conn.Open ();

    using ( SqlCommand Cmd = Conn.CreateCommand () )

    {

    Cmd.CommandText = "ProcessLogQueue";

    Cmd.CommandType = System.Data.CommandType.StoredProcedure;

    Cmd.CommandTimeout = 0;

    while ( !this.m_Terminate )

    {

    string Response = Cmd.ExecuteScalar ().ToString ();

    if ( Response.Length > 0 )

    {

    XmlDocument Doc = new XmlDocument ();

    Doc.LoadXml ( Response );

    XmlNode RootNode = Doc.SelectSingleNode ( "LOG" );

    XmlNode RowNode = RootNode.SelectSingleNode ( "Row" );

    string ProcessName = RowNode.SelectSingleNode ( "ProcessName" ).InnerText;

    string MachineName = RowNode.SelectSingleNode ( "MachineName" ).InnerText;

    string MachineFolder = Path.Combine ( this.LogFolder, MachineName );

    if ( !Directory.Exists ( MachineFolder ) )

    {

    try

    {

    Directory.CreateDirectory ( MachineFolder );

    }

    catch ( Exception Ex )

    {

    this.Log ( "Run", Ex.ToString () );

    return;

    }

    }

    if ( System.Diagnostics.Debugger.IsAttached )

    {

    Console.WriteLine ( "Processing message from {0}:{1}", MachineName, ProcessName );

    }

    string FileName = Path.Combine ( MachineFolder, ProcessName + ".log" );

    try

    {

    this.CheckLog ( MachineName, FileName );

    }

    catch ( Exception Ex )

    {

    return;

    }

    using ( StreamWriter SW = new StreamWriter ( FileName, true ) )

    {

    SW.WriteLine ( string.Format ( "{0} {1}", RowNode.SelectSingleNode ( "LogTime" ).InnerText, RowNode.SelectSingleNode ( "ProcessMessage" ).InnerText ) );

    }

    }

    }

    }

    }

    }

    catch ( SqlException Ex )

    {

    this.m_Callback ( Ex.Message );

    }

    catch ( Exception Ex )

    {

    this.m_Callback ( Ex.Message );

    }

    }

    private void CheckLog ( string MachineName, string FileName )

    {

    FileInfo FI = new FileInfo ( FileName );

    if ( FI.Exists )

    {

    DateTime Today = Convert.ToDateTime ( DateTime.Now.ToShortDateString () );

    if ( FI.LastWriteTime < Today )

    {

    string ArchiveFileName = Path.GetFileName ( FileName ).Replace ( ".log", FI.LastWriteTime.ToString ( "yyyyMMdd" ) + ".log" );

    string MachineFolder = Path.Combine ( this.ArchiveFolder, MachineName );

    if ( !Directory.Exists ( MachineFolder ) )

    {

    Directory.CreateDirectory ( MachineFolder );

    }

    FI.MoveTo ( Path.Combine ( MachineFolder, ArchiveFileName ) );

    }

    }

    }

    }

    }

    The important thing to remember about Windows Services is that they MUST return quickly from the Start command.

    Hope this helps.

    Ed

  • Phillip - Texas

    SSC-Addicted

    Points: 415

    Thank you Ed.

  • EdSwiedler

    SSC Enthusiast

    Points: 190

    Sorry, I haven't been paying a lot of attention.

    The first thing I notice is that you have all of your code in the OnStart event handler. This won't work as the SCM is expecting a quick response. The best way is to create a thread to handle the work and return immediately.

    I had to completely re-write the service I use. I no longer use Service Broker (probably due to a lack of experience, I had some issues). I also had to create a separate thread for each process being logged. I then had memory issues, so I used a series of queues and queue managers, which solved the problem.

    If you would like to get a zip of what I ended up doing, please, let me know.

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply