How to view transaction logs?

Question

Hi

I am not sure if the transaction log is what I need or what.

My first problem is that I have a stored procedure that inserts some rows. I was just checking out elmah and I see that some sql exceptions happens. They all are the same error(a PK constraint was violated).

Other than that elmah is not telling me much more. So I don't know what row caused this primary key constraint(I am guessing the same row was for some reason being added twice).

So I am not sure if the the transaction log would tell me what happened and what data was trying to be inserted. I can't recreate this error it always works for me.

My second problem is for some reason when my page loads up I have a row from that database that I don't think exists anymore(I have a hidden column with the PK in it.) When I try to find this primary key it does not exist in the database.

I am using ms sql 2005.

Thanks

Answer 1

If you can repeat the error I would suggest using SQL Server profiler so you can see exactly what is going on.

If you are using asp.net to load the page are you using any output caching or data caching that might be retaining the row that no longer exists in the db?

Answer 2

I don't think transaction log will help you.

SQL 2 modes on how to insert data with uniqueness violation. There is a setting : IGNORE_DUP_KEY. By default it is OFF. IF you turn it ON, SQL will ignire duplicate rows and your INSERT statement will succeed.

You can read about it here: http://msdn.microsoft.com/en-us/library/ms175132.aspx

BTW, to view transaction log, you can use this command:

SELECT * FROM  fn_dblog(null, null)

Answer 3

You can inspect the log with the (undocumented) function fn_dblog(), but it won't tell you anything in the case of duplicate key violation because the violation happens before the row is inserted, so no log record is generated. Is true though that you'll get other operations at the time of error and from those you can, possibly, recreate the actions that lead to the error condition. Note that if the database is in SIMPLE recovery model then the log gets reused and you likely lost track of anything that happened.

Have a look at this article How do checkpoints work and what gets logged for an example of fn_dblog() usage. Although is on a different topic, it shows how the function works.