August 18, 2013 at 10:11 am
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'
August 18, 2013 at 11:08 am
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
Change is inevitable... Change for the better is not.
August 18, 2013 at 11:23 am
I'm trying to find out who deleted data.
August 18, 2013 at 11:38 am
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
August 18, 2013 at 12:09 pm
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
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply