none
如何取得SQL Server交易記錄檔的內容 RRS feed

  • 問題

  • :::請問各位SQL Server高手,小女子想要查看SQL Server交易記錄檔(.ldf)的
    內容,請問是否有相關的語法可以查看,或者有相關的資訊???
    小女子不想用到市面上的一些查看記錄檔的產品!!!

    另外一個問題,是否可以取消寫記錄到交易記錄檔的動作???
    2007年4月23日 上午 02:03

解答

  • 1. 要存取交易記錄檔,在官方的文件上好像沒有辦法。但在網路上有一個作法:

     

    Source: http://www.mssqlcity.com/Articles/KnowHow/ViewLog.htm

     

    You can use the following undocumented command in this case:

    SQL Server Undocumented Command: DBCC log
    DBCC log ( {dbid|dbname}, [, type={0|1|2|3|4}] )

     

    PARAMETERS:
    Dbid or dbname - Enter either the dbid or the name of the database in question.

    type - is the type of output:

    0 - minimum information (operation, context, transaction id)

    1 - more information (plus flags, tags, row length)

    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.

    by default type = 0

    To view the transaction log for the master database, you can use the following command:

    DBCC log (master)

    2. 我記得所有的交易都會被寫入 Transaction Log,除非你沒有使用交易處理 (BEGIN TRAN ... COMMIT ... ROLLBACK)。

    2007年4月23日 上午 08:53
    版主
  • 建議使用 DBCC LOG 來檢視交易記錄檔內容。


    DBCC LOG:
    This command is 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)

     

     

    參考資料:
      Some Useful Undocumented SQL Server 7.0 and 2000 DBCC Commands
       http://www.sql-server-performance.com/ac_sql_server_2000_undocumented_dbcc.asp
    -------------------------------------------
    林金花
    微軟技術支援中心

    2007年4月24日 上午 10:21

所有回覆

  • 1. 要存取交易記錄檔,在官方的文件上好像沒有辦法。但在網路上有一個作法:

     

    Source: http://www.mssqlcity.com/Articles/KnowHow/ViewLog.htm

     

    You can use the following undocumented command in this case:

    SQL Server Undocumented Command: DBCC log
    DBCC log ( {dbid|dbname}, [, type={0|1|2|3|4}] )

     

    PARAMETERS:
    Dbid or dbname - Enter either the dbid or the name of the database in question.

    type - is the type of output:

    0 - minimum information (operation, context, transaction id)

    1 - more information (plus flags, tags, row length)

    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.

    by default type = 0

    To view the transaction log for the master database, you can use the following command:

    DBCC log (master)

    2. 我記得所有的交易都會被寫入 Transaction Log,除非你沒有使用交易處理 (BEGIN TRAN ... COMMIT ... ROLLBACK)。

    2007年4月23日 上午 08:53
    版主
  • 建議使用 DBCC LOG 來檢視交易記錄檔內容。


    DBCC LOG:
    This command is 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)

     

     

    參考資料:
      Some Useful Undocumented SQL Server 7.0 and 2000 DBCC Commands
       http://www.sql-server-performance.com/ac_sql_server_2000_undocumented_dbcc.asp
    -------------------------------------------
    林金花
    微軟技術支援中心

    2007年4月24日 上午 10:21