5

I'm searching for a way to read the SQL Server 2008 log file, not to show the information, but to read the meaning of the symbols and the structure of the LOG table. I'm using DBCC LOG('my_table', 3).

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Neuvill
  • 93
  • 2
  • 3
  • 9
  • You cannot directly read the SQL Server transaction log file - that's not really documented. Why do you need to read the log file, what do you expect to find in there?? – marc_s Mar 19 '12 at 08:45
  • just to know the users logs and the instructions that they made... – Neuvill Mar 19 '12 at 08:56
  • use this command `DBCC LOG('my_table', 3)` in sql server management studio and you'll see a table that contents logs, if you read it you'll see in a column called [comment] a list of operation made like update insert, and you'll see the date of the operation and many other informations. – Neuvill Mar 19 '12 at 09:02
  • Yes - but that information is not in the transaction logs in any textual form or anything. DBCC collects and interprets the raw data - you'll have to have the source code for DBCC to understand how to do this (and you can't have that - it's not an open source project, after all) – marc_s Mar 19 '12 at 09:04
  • that's wired, so how we use of the log file? i'm workin in local database and im the administrator and i can't see what hapends in the log file? because im gona make this DB in online, and i have to see what the users do... is there another way to do that? – Neuvill Mar 19 '12 at 09:12
  • You don't use the transaction log yourself - it's a SQL Server **internal** thing, really. Don't try to tamper with it, don't try to read it out - just let SQL Server do its work! – marc_s Mar 19 '12 at 09:13
  • okey... so what you suggest to have a back up of the logs and transaction? a transact table i think... that records all the transaction... – Neuvill Mar 19 '12 at 09:16
  • You need to learn about the transaction log and its purpose and use: [Managing Transaction Logs](http://www.simple-talk.com/sql/learn-sql-server/managing-transaction-logs-in-sql-server/), [Introduction to the SQL Server transaction log](http://www.sqlservercentral.com/articles/Design+and+Theory/63350/), [Purpose of the SQL Server Transaction log](http://www.databasedesign-resource.com/sql-server-transaction-log.html) – marc_s Mar 19 '12 at 09:21
  • The way of reading a log file in SQL Server by using fn_dblog() function is too long. Instead of this you can try [Free SQL LDF Viewer](http://www.mdfviewer.com/ldf/) – Jason Clark Apr 14 '16 at 04:46

3 Answers3

11

First of all, in order to be able to read any meaningful data your database needs to be in full recovery mode. Otherwise you probably won't find much there. There are two ways to do this. Using undocumented SQL functions and using third-party tools.

SQL Functions:

DBCC LOG and fn_dblog - more details here and here

Third-party tools:

Toad for SQL Server (actually does a lot more than reading logs) and ApexSQL Log (focuses only on reading transaction logs).

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
JdMR
  • 1,268
  • 14
  • 9
8

See my answer in this Stack Overflow post: How can I view SQL Server 2005 Transaction log file

Or

Use this command:

Select * from ::fn_dblog(null,null)

And for more information, see How Do You Decode A Simple Entry in the Transaction Log.

Community
  • 1
  • 1
Ardalan Shahgholi
  • 11,967
  • 21
  • 108
  • 144
3

From your comments, if you want to see the queries users issue:

Start a trace or use extended events to capture the sql text. See How to: Create a Trace (SQL Server Profiler).

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Andy Irving
  • 2,657
  • 1
  • 14
  • 11