SQL Server Performance

Viewing the SQL server log file

Discussion in 'SQL Server 2008 General Developer Questions' started by tarekj, Jun 26, 2011.

  1. tarekj Member

    - is there a way to browse and/or recover the database log file? nothing new in SQL server 2008?
    - if I should use 3rd party, what is the best advice?
  2. satya Moderator

    What is the purpose of view the transaction log file for a database?

    You can take help of undocumented statement such as: DBCC LOG(<databasename >, <type of output>)

    <type of output>
    0: Return only the minimum of information for each operation -- the operation, its context and the transaction ID. (Default)
    1: As 0, but also retrieve any flags and the log record length.
    2: As 1, but also retrieve the object name, index name, page ID and slot ID.
    3: Full informational dump of each operation.
    4: As 3 but includes a hex dump of the current transaction log row.

    Have a look at fn_dblog and in order to get full information you must depend on third party toll such as Log Explorer or Quest log reader or look at ApexSQL tool from http://www.apexsql.com/sql_tools_log.asp.
  3. tarekj Member

    the purpose of view the transaction log file for a database to me is to see what actually happened in the right sequence, for example:
    update sales set price = 100; then
    update sales set price = 150; then
    update sales set price = 100

    when you type:
    select price from sales;
    you get 100 but you do not know that somebody changed the value two times, you get only the final value.
  4. FrankKalis Moderator

    I don't think a log viewer is the right choice for such a scenario. What about Change Data Capture?
  5. satya Moderator

    True, as suggested by Frank you must deploy the programming methods using TSQL to obtain such information, it will rather allow you to alert if there si a certain issue with the desiered tables. Lets say if you have used third party tools then you should continue this doing all the times.
    have a look at SQL Server books online for CDC concept.
  6. tarekj Member

    1- does CDC expand the log file?
    2- can I truncate the CDC table any time?
  7. satya Moderator

    Yes it will log the transactions and as per BOL (see Frank's link) too:
    Both the capture and cleanup jobs are created by using default parameters. The capture job is started immediately. It runs continuously, processing a maximum of 1000 transactions per scan cycle with a wait of 5 seconds between cycles. The cleanup job runs daily at 2 A.M. It retains change table entries for 4320 minutes or 3 days, removing a maximum of 5000 entries with a single delete statement.
  8. tarekj Member

    1- By using CDC, I will be able to know exactly who made changes ?
    2- Am I able to change the cleanup schedule?.
  9. satya Moderator

    I believe you need to take your time to study and know about CDC to fulfil the requirement, have a look at SQL Server Books online for more information.

Share This Page