View Transaction Log

[Brit]

SQL 7

I am a developer without much knowledge of SQL Admin. My problem: I beleive a stored procedure began a transaction but then rolled back. The web server did not receive notification of the rollback. The event logs for the web server and SQL do not show any errors. I am guessing that possibly the @@xactcount was out of sync or a timeout caused the a rollback but did not send an error to the client.

I know that SQL keeps a transaction log to be used for recovery. Can I visually look at this log and determine if a rollback did occur? If so how do I locate it and open it? Any help is appreciated.

 

[whool]

Brit,

There is no way of viewing the Transaction Log.

Perhaps you could re-run the qurey on a backup db and try and make use of the sp Debuger (via the Object browser in Query Analyser). I have not managed to work out how to step through an sp using this debuger but it should be possible.If you can get it working you should be able to tell if the transaction gets rolled back.

Good luck,
Yael

 

[tlbroadbent]

You can view the transaction log with DBCC LOG. However, it is difficult to interpret the info returned by the command.

Example: DBCC LOG ('master',4)

See

http://www.swynk.com/friends/achigrik/DBCC2000.asp

for more info about DBCC LOG.

You can also get a 3rd-party tool to read the logs. I use Lumigent's Log Explorer. You can download a trial version from

http://www.lumigent.com/.

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[fluteplr]

Actually there is if you have some $$.

Have a look a a product called Lumigent Log Explorer

http://www.lumigent.com/go/sqmg

I have not used it personally but it has received a couple of decent writeups.