Reading Transaction Logs

Question

Reading Transaction Logs

Ustun Kaya

SSC Enthusiast

Points: 106
More actions
December 3, 2007 at 12:50 pm

#181054

Hello,
I want to find a way to see what transactions are happening in the database, like, who logged in, edited what, timestamps of the actions. This would be great especially monitoring - tracking security problems or unauthorized access.
I am not sure transaction logs (.ldf files) should be the sources I should take as reference for this purpose. I know that transaction logs keep track of transactions for restoration - recovery purposes, and they are not human readable. I saw that there are 3rd party programs that do this job (reading them), but I just want to make sure whether I'd get satisfying results even if I were able to read them.
I hope I'm on the right way of thinking, appreciate all help,
Thanks

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736455 More actions · Answer 1

You cannot easily do this after the fact. Reading transaction logs will give you most of this, and there are numerous third party products to help here. They give you some information.

If you want to track this, you need to use Profiler or set up server side traces (preferred) to keep track of this information. It will generate a lot of data, so be sure you have adequate disk space in which to hold the information.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

The tran logs can be read, but they are not easy to understand and they don't contain information about people logging in and they don't keep info about who did what operation.

I would recommend that you use a server side trace for this (sp_trace_* stored procs)

btw, if you want to read the tran log (and do this on a dev server, not your prod box) you can use the function fn_dblog.

SELECT * from fn_dblog(null, null)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Steve Jones - SSC Editor SSC Guru Points: 736455 More actions · Answer 3

You can also download Log Rescue from Red Gate (I work for them). It's free because they're no longer selling it (hard to make money from this product), but it will read your logs.

Adam Haines SSC-Insane Points: 23217 More actions · Answer 4

Steve,

Doesn't Log Rescue only work for SQL 2000?

-Adam

My blog: http://jahaines.blogspot.com

Steve Jones - SSC Editor SSC Guru Points: 736455 More actions · Answer 5

Yep, I think it is only 2000. That's what's on the site. It's also 32bit only.

Apparently there isn't much $$ in these products because people only want them when they need something, not on a regular basis.

Looks like the log readers are $1000 per instance. Not really an economical choice.

Adam Haines SSC-Insane Points: 23217 More actions · Answer 6

Yeah, the price for 3rd party readers is precipitous. I wanted to play/test a log reader app and could never find one that met my budgeted price of FREE.

My blog: http://jahaines.blogspot.com

Steve Jones - SSC Editor SSC Guru Points: 736455 More actions · Answer 7

I pinged some people to see if there are any other solutions. I know I didn't renew our Lumigent maintenance at JD Edwards because we never used it. This is a "screwdriver" type tool that you need to install on a workstation and then just read the logs you need, not on every instance.

Slick84 SSCertifiable Points: 5602 More actions · Answer 8

Okay, I know this is a topic from a long time ago, but I'd rather search and reply than create a new topic from scratch. So now to my question..

I was reading an article on Sql Server Performance .com about restoring from transaction logs (point in recovery) and after creating a scenario (creating DB, table, inserting data, taking backups of DB, logs) i dropped the table. In order to recover it using the transaction logs, I need to see the time stamp of when exactly I dropped the table.. and I'm stumped.. the author doesnt tell how to view the time stamp..

So anyone out there who can help me? if you guys need to see the article im talking about.. here is the link:

hxxp://www.sql-server-performance.com/articles/dba/Restore_Transaction_Logs_for_Point_in_Time_Recovery_p2.aspx

I changed the http to hxxp because im not sure if links are allowed.

Anyway, any help would be appreciated. Thanks guys...

Regards,

O

--
:hehe:

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Please rather create new threads for new questions.

There's no easy way to see the time stamp. The way you can do it is to roll the log forward second by second with the Standby options and see if the data's there. Of course, if you go too far, you'll have to start the restore from scratch.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Slick84 SSCertifiable Points: 5602 More actions · Answer 10

Will do the next time Gail...

So how else do I do point in time recovery from transaction logs if looking at the timestamps for a dropped table or anything else is so difficult?

--
:hehe: