DBCC LOG

[Alin Capitanescu]

Any idea where to find some help about DBCC LOG.
I want to decode (or something) the data fields.

[Anith Sen]

DBCC LOG is an undocumented command used to view the transaction
log for the specified database.

Syntax:
DBCC log ({dbid|dbname}, [, type={-1|0|1|2|3|4}])

where:
dbid or dbname - Enter either the dbid or the name of the database
type - is the type of output, and includes these options:
0 - minimum information (operation, context, transaction id)
1 - more information (plus flags, tags, row length, description)
2 - very detailed information (plus object name, index name, page id, slot
id)
3 - full information about each operation
4 - full information about each operation plus hexadecimal dump of
the current transaction log's row.
-1 - full information about each operation plus hexadecimal dump of the
current transaction log's row, plus Checkpoint Begin, DB Version,
Max XDESID

by default, type = 0

To view the transaction log for the master database, run the following
command:

DBCC LOG (master)

Note that if you use DBCC TRACEON (3604), you can understand details by
displaying a trace of the output of DBCC LOG

In SQL 2000 you can also try

SELECT * FROM ::fn_dblog(1,NULL)

--
- Anith

[Dinesh.T.K.]

Alin,

DBCC LOG is a undocumented call.So do exercise caution before using it .
Not per se, but this command helps in viewing transaction log for the
specified database.

The syntax is
DBCC LOG ( {db_id/db_name},[type])

Usage
DBCC LOG ( pubs )

The first parameter is either the database id or database name.
'type' can have any of thse values and the output is modified accordingly :
-1 - All needed info.abt the transaction and in addition info. checkpoint
begin value etc
3 - All needed info.abt the transaction.
0 - minimum info. like the id of the transaction and the related context
etc.This is the default.
1 - some more info like the affected row length etc.
2 - shows the obhect info like name and id.
4 - same as -1 but less info.

FYI only, if you are in SQL2000, you can also refer a system function -
fn_dblog.Umachandar has more details in his page
http://www.umachandar.com/technical/SQL2000Scripts/UtilitySPs/Main7.htm

Dinesh
--
--
SQL Server FAQ at
www.tkdinesh.com

"Alin Capitanescu" <al...@gfs.ro> wrote in message
news:#ThPJGuQCHA.608@tkmsftngp11...

[Alin Capitanescu]

Thanks for your answer...
I have another question
How can you build an INSERT command using DBCC LOG?
Is it possible? If yes... how?

"Dinesh.T.K." <dine...@usa.net> wrote in message
news:ewxI0VuQCHA.4012@tkmsftngp11...

[Anith Sen]

Are you looking for something like:

CREATE TABLE #test
(
[Current LSN] VARCHAR(25),
[Operation] VARCHAR(25),
[Context] VARCHAR(25),
[Transaction ID]VARCHAR(15)
)
INSERT #test
EXEC('DBCC LOG (pubs)')

SELECT * FROM #test

--
- Anith