|
|
|
Grasshopper
      
Group: General Forum Members
Last Login: Tuesday, March 12, 2013 9:16 AM
Points: 18,
Visits: 71
|
|
|
|
|
|
SSC Eights!
      
Group: General Forum Members
Last Login: Tuesday, May 21, 2013 4:41 AM
Points: 909,
Visits: 596
|
|
| 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...
|
|
|
|
|
SSC Eights!
      
Group: General Forum Members
Last Login: Tuesday, May 21, 2013 4:41 AM
Points: 909,
Visits: 596
|
|
... 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.
|
|
|
|
|
SSC Veteran
      
Group: General Forum Members
Last Login: Thursday, May 16, 2013 5:46 AM
Points: 257,
Visits: 671
|
|
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
|
|
|
|
|
Forum Newbie
      
Group: General Forum Members
Last Login: Wednesday, January 06, 2010 3:51 PM
Points: 1,
Visits: 3
|
|
| 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.
|
|
|
|
|
SSC Rookie
      
Group: General Forum Members
Last Login: 2 days ago @ 4:28 PM
Points: 28,
Visits: 156
|
|
| Author has provided the full SQL Script for creating the stored procedure. Thanks
|
|
|
|
|
Grasshopper
      
Group: General Forum Members
Last Login: Tuesday, March 12, 2013 9:16 AM
Points: 18,
Visits: 71
|
|
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
|
|
|
|
|
Grasshopper
      
Group: General Forum Members
Last Login: Tuesday, March 12, 2013 9:16 AM
Points: 18,
Visits: 71
|
|
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.
|
|
|
|
|
SSCoach
         
Group: General Forum Members
Last Login: Today @ 9:29 AM
Points: 18,742,
Visits: 12,335
|
|
|
|
|
|
SSC Rookie
      
Group: General Forum Members
Last Login: 2 days ago @ 4:28 PM
Points: 28,
Visits: 156
|
|
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() { } } }
|
|
|
|