sql logs

• vikas.02gupta

  Newbie

  Points: 4

  More actions

  October 27, 2009 at 6:54 am

  #134711

  hi.......

  i hav a table in sql database...i want keep track of the table when user perform any operation like insert ,update n delete on that table.how can i do this with the help of sql logs.... plz reply

• NickDBA

  Hall of Fame

  Points: 3792

  More actions

  October 27, 2009 at 7:09 am

  #1071175

  You can't examine the logs directly in SQL Server, you'd need a third party tool for that. Maybe you should consider a trigger on you table to record such activites.

• Grant Fritchey

  SSC Guru

  Points: 398909

  More actions

  October 27, 2009 at 8:37 am

  #1071250

  You might want to try setting up a server-side trace. That will allow you to capture all the calls against the database. Another option is to look at SQL Server 2008 and the Change Data Capture utility.

  "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
  - Theodore Roosevelt

  Author of:
  SQL Server Execution Plans
  SQL Server Query Performance Tuning

• mcvilbar

  Say Hey Kid

  Points: 711

  More actions

  October 27, 2009 at 9:56 am

  #1071310

  I think SQL 2008 can answer to your needs. I'm against with triggers, it will degrade the performance of a database.

• Lewis Dowson

  SSC Eights!

  Points: 988

  More actions

  October 27, 2009 at 10:08 am

  #1071317

  Hi,

  I've used triggers to perform this type of task.

  Performance has not really been an issue in my case.

  I guess you need to estimate how often you expect the trigger to fire before you implement a trigger strategy.

  Here is a sample trigger that you could use against the whole database.... you'll need to create the DDLEventLog table first, then create the trigger. As I mentioned this is for the whole database but you can do the same on table object too.

  CREATE TABLE [dbo].[DDLEventLog](

  [DDLEventLogID] [int] IDENTITY(1,1) NOT NULL,

  [AuditDate] [datetime] NULL,

  [EventType] [varchar](15) NULL,

  [ServerName] [varchar](100) NULL,

  [DatabaseName] [varchar](50) NULL,

  [DBUserName] [varchar](100) NULL,

  [ObjectType] [varchar](25) NULL,

  [ObjectName] [varchar](50) NULL,

  [CommandText] [nvarchar](2000) NULL

  ) ON [PRIMARY]

  GO

  CREATE TRIGGER [TRG_DDL_EventLog]

  ON DATABASE

  FOR DDL_DATABASE_LEVEL_EVENTS

  AS

  BEGIN

  DECLARE @xmlEventData XML

  SET @xmlEventData = EVENTDATA()

  INSERT INTO DDLEventLog

  (

  AuditDate,

  EventType,

  ServerName,

  DatabaseName,

  DBUserName,

  ObjectType,

  ObjectName,

  CommandText

  )

  SELECT

  REPLACE(CONVERT(VARCHAR(50), @xmlEventData.query('data(/EVENT_INSTANCE/PostTime)')), 'T', ' '),

  CONVERT(VARCHAR(15), @xmlEventData.query('data(/EVENT_INSTANCE/EventType)')),

  CONVERT(VARCHAR(25), @xmlEventData.query('data(/EVENT_INSTANCE/ServerName)')),

  CONVERT(VARCHAR(25), @xmlEventData.query('data(/EVENT_INSTANCE/DatabaseName)')),

  SUSER_NAME(),

  CONVERT(VARCHAR(25), @xmlEventData.query('data(/EVENT_INSTANCE/ObjectType)')),

  CONVERT(VARCHAR(25), @xmlEventData.query('data(/EVENT_INSTANCE/ObjectName)')),

  CONVERT(VARCHAR(MAX), @xmlEventData.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)'))

  END