Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Logging with SQL Server Expand / Collapse
Author
Message
Posted Wednesday, January 6, 2010 12:07 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 6:36 AM
Points: 19, Visits: 86
Comments posted to this topic are about the item Logging with SQL Server
Post #842563
Posted Wednesday, January 6, 2010 3:48 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 6:57 AM
Points: 982, Visits: 752
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...
Post #842654
Posted Wednesday, January 6, 2010 6:02 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 6:57 AM
Points: 982, Visits: 752
... 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.

Post #842709
Posted Wednesday, January 6, 2010 7:16 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, April 18, 2014 8:01 AM
Points: 258, Visits: 701
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








Post #842775
Posted Wednesday, January 6, 2010 8:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 8, 2013 11:28 AM
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.
Post #842827
Posted Wednesday, January 6, 2010 9:26 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 30, 2013 11:18 AM
Points: 29, Visits: 162
Author has provided the full SQL Script for creating the stored procedure. Thanks
Post #842934
Posted Wednesday, January 6, 2010 9:33 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 6:36 AM
Points: 19, Visits: 86
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
Post #842943
Posted Wednesday, January 6, 2010 9:35 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 6:36 AM
Points: 19, Visits: 86
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.
Post #842947
Posted Wednesday, January 6, 2010 11:33 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 4:09 PM
Points: 21,252, Visits: 14,960
Thanks for the article



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #843064
Posted Wednesday, January 6, 2010 1:15 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 30, 2013 11:18 AM
Points: 29, 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()
{
}
}
}

Post #843159
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse