"ROLLBACK TRANSACTION" notification - SQL 2000 ?

• Joseph Fallon

  SSCommitted

  Points: 1669

  More actions

  August 21, 2008 at 4:36 am

  #123967

  Hi All,

  I'm wondering whether there's any way to get the Server to record that it's doing a "ROLLBACK TRANSACTION" on a SQL transaction or that a transaction has timed out ?

  Anything would do, writing to a log, send an email etc ...

  I've had a look in "Manage SQL Server Messages" in EM but couldn't find anything.

  I can't keep a trace going as I'm not sure when / if the problem will happen again.

  Any ideas ?

  Thanks for reading.

  --****************************

  CREATE TABLE [dbo].[RollTest] (

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

  [RowText] [char] (10) COLLATE Latin1_General_CI_AS NULL

  ) ON [PRIMARY]

  GO

  BEGIN TRANSACTION

  INSERT INTO RollTest (RowText) values ('1')

  INSERT INTO RollTest (RowText) values ('2')

  INSERT INTO RollTest (RowText) values ('3')

  -- I want this rollback logged if possible

  ROLLBACK TRANSACTION

• Jack Corbett

  SSC Guru

  Points: 184394

  More actions

  August 21, 2008 at 6:54 am

  #861204

  Why can't you run a server-side trace? The impact is minimal if you limit events and columns returned and put a filter on.

  You could also put a RaisError() with log after any rollback.

  Jack Corbett
  Consultant - Straight Path Solutions
  Check out these links on how to get faster and more accurate answers:
  Forum Etiquette: How to post data/code on a forum to get the best help
  Need an Answer? Actually, No ... You Need a Question

• Joseph Fallon

  SSCommitted

  Points: 1669

  More actions

  August 21, 2008 at 8:52 am

  #861329

  Thanks for the reply.

  Ideally I'd like something that would be just write out the Rollbacks, as opposed to having to scan through big files.

  In the meantime I'll set up a local profiler trace with

  SQL:StmtCompleted and

  SQL:BatchCompleted

  filtered for "ROLLBACK TRANSACTION" like you suggest.