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: 3684

  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: 397721

  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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

• mcvilbar

  Say Hey Kid

  Points: 701

  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: 928

  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