how to view log files

Question

how to view log files

a9727

SSC Veteran

Points: 210
More actions
August 18, 2013 at 10:11 am

#297895

This gives me the location of the log file. How do I view it?
SELECT name, physical_name AS current_file_location
FROM sys.master_files
where name = 'MyDatabase'

Viewing 5 posts - 1 through 4 (of 4 total)

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

Jeff Moden SSC Guru Points: 1004191 More actions · Answer 1

If you mean how to view the content of what is normally the ".ldf" file, it normally takes some 3rd party software to view the logfile. There are some tricks that you'd have to Google but MS never intended to make the contents viewable and didn't make it easy to view.

With that thought in mind, why do you want to view the log file? What are you trying to find out from there?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

a9727 SSC Veteran Points: 210 More actions · Answer 2

a9727

SSC Veteran

Points: 210

August 18, 2013 at 11:23 am

#1642505

I'm trying to find out who deleted data.

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

Unless you're in full recovery model and haven't taken a log backup since the delete, the log records describing that change will likey have been removed from the log (marked inactive and overwritten).

If you are in full recovery, you can use the fn_dblog system function in SQL. The log cannot be opened in an application and read and it's very hard to read and understand even through SQL. It's not designed to be human-readable, it's not an audit trail.

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

Jeff Moden SSC Guru Points: 1004191 More actions · Answer 4

a9727 (8/18/2013)
I'm trying to find out who deleted data.

I agree with Gail. If you're in the FULL recovery mode and are doing backups correctly, there's a pretty good chance that the data you're looking for is no longer in the log file. If you're in the SIMPLE recovery mode, there's really no chance of the data being there at all.

If you're having problems with people deleting from a table, perhaps a trigger on the table would be your best bet. You can record ORIGINAL_LOGIN() (which won't actually help much if it's been done through most applications) or you can use an INSTEAD OF trigger to reject all deletion attempts.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)