ldf file, how much do they keep, how can i access

[davejam]

hi all,

its rather important if possible to try and get a transaction log from our server, i think the transactions for each database sit in the ldf files (a guess) and i need to look back and see if there was a large data export from the database (or specific tables) by either admin or a specific user.

you may guess why i need this!!!

how much info does an ldf file save (as in content), the ldf file in question is pretty large so i don't think they have a maintenance plan going on, also whats the best way to access this for view (if its where i need to be looking!!!)

if its not where else (if anywhere) could i find logging information, if they've set the server up for full logging anyway!!!

Any pointers would be greatfully received

thankyou

daveJam

*two wrongs don't make a right..... but three lefts do!!!!*

 

[ptheriault]

You will need a third party tool to read the transaction log. I like lumigent log explorer best.
Are you backin up your log? If your database is in simple recovery then you will never find what you are looking for. Once the database checkpoints you loose any commited transactions from your log. If you have been backing up your log then open the backup of the Transaction log with Log explorer. One of the nice things about log explorer is the ability to filter by events.
One last thought, depending on how the data was accessed it may not be in your log.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[SQLBill]

Also, you might have to purchase Lumigent. I don't believe the trial version will work with user database, only the default 'learning' databases. That said....here's the disclaimer - I've never used the product, but I've seen lots of comments about that on this site.

-SQLBill

Posting advice: FAQ481-4875

 

[SQLDenis]

>>I don't believe the trial version will work with user database, only the default 'learning' databases.

that's why you can do this
sp_rename 'MyDatabase','pubs','Database'

;-)
then rename it back

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[billwatson]

try running

DBCC log(my_database,3)

ought to display the transaction log

 

[davejam]

it is a long shot.... and the findings may give us nothing extra, just trying to find a large export transaction and the owner....

this should have come from external software which will have individual login (so maybe we'll get that) all depends on how it was accessed and how the software logs into the db!!!

i will try your suggestions as soon as i've got a brew on!!!

daveJam

*two wrongs don't make a right..... but three lefts do!!!!*