January 6, 2010 at 12:07 am
Comments posted to this topic are about the item Logging with SQL Server
January 6, 2010 at 3:48 am
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...
January 6, 2010 at 6:02 am
... 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.
January 6, 2010 at 7:16 am
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
January 6, 2010 at 8:07 am
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.
January 6, 2010 at 9:26 am
Author has provided the full SQL Script for creating the stored procedure. Thanks
January 6, 2010 at 9:33 am
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
January 6, 2010 at 9:35 am
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.
January 6, 2010 at 11:33 am
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
January 6, 2010 at 1:15 pm
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()
{
}
}
}
January 6, 2010 at 1:42 pm
Looks like you're catching the most likely causes but ignoring them. Take out the try/catch blocks and look in the application log.
January 6, 2010 at 2:21 pm
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.
January 7, 2010 at 5:23 am
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
January 11, 2010 at 8:12 am
Thank you Ed.
December 17, 2010 at 5:28 pm
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 15 total)
You must be logged in to reply to this topic. Login to reply