Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Logging with SQL Server


Logging with SQL Server

Author
Message
EdSwiedler
EdSwiedler
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 94
Comments posted to this topic are about the item Logging with SQL Server
david.wright-948385
david.wright-948385
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1073 Visits: 967
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
david.wright-948385
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1073 Visits: 967
... 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
cmille19
SSC Veteran
SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)

Group: General Forum Members
Points: 295 Visits: 724
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
rbuscemi
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 4
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
Phillip - Texas
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 162
Author has provided the full SQL Script for creating the stored procedure. Thanks
EdSwiedler
EdSwiedler
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 94
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
EdSwiedler
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 94
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
SQLRNNR
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22617 Visits: 18259
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

Phillip - Texas
Phillip - Texas
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 162
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()
{
}
}
}


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search