Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2003
    Posts
    1

    Unanswered: DBCC Permissions

    Hi,

    I want to be able to run the following statement:

    DBCC log (tbl_Name)

    But I get this error:

    Server: Msg 2571, Level 14, State 1, Line 1
    User 'HQ\exd188' does not have permission to run DBCC log.

    What server permissions would I need to run this statement.

    TIA,
    Eric

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Here's what I found:

    <QUOTE>
    From: David
    Date: 08-08-03 01:15
    Subject: Re: How can I read the content of transaction log?

    --------------------------------------------------------------------------------
    Hi Wallace,

    Well, Peter is right, it is very difficult to get some piece of information
    out from the log.
    It depends on what you are looking for. Have a look at dbcc log command:

    dbcc log (dbid, object_id, pagenum, rownum, nrecords, type, printopt)

    - dbid: the id of your database in sysdatabases (or you can get it with
    db_id("my_db"))
    - object_id: if you know especially on which object the transactions you're
    looking for are related.
    - pagenum / rownum: (optional) you can add the page number (if you don't
    have rownum) for this object
    or the transaction ID (if you can supply a rownum)
    - nrecords: number of records to examine. Put a negative value to get the
    last n records.
    - type: the type of transaction you can find. It goes from -1 (all records)
    to 36. Each number from 1 to 36
    represents a xact. For example: 4 for XREC_INSERT, 5 for XREC_DELETE, 9
    for XREC_MODIFY, etc...
    You can find the list at http://www.kaleidatech.com/dbcc2.htm
    - printopt: the print option: 0 if you want only headers, 1 if you want
    header + data.

    For example, if I want the header of the 20 last records of every xact on my
    object_id 263671987 in my database 4:
    1>dbcc traceon(3604)
    2>go
    1>dbcc log(4,263671987,0,0,-20,-1,0);
    2>go

    If the log is empty, it will say:

    LOG SCAN DEFINITION:
    Database id : 4
    Backward scan: starting at end of log
    log records for object id 263671987
    maximum of 20 log records.

    LOG RECORDS:

    Total number of log records 0
    DBCC execution completed. If DBCC printed error messages, contact a user
    with System Administrator (SA) role.

    If it is not, you will find hexa information. Have the closest look at the
    timestamps. They will tell you when
    the transaction occured.

    You can have a look also at Thierry Antinolfi's DBA Devil website. He
    provides a C++ built-in LogExplorer.
    http://perso.wanadoo.fr/dbadevil/en/logexpl.html

    Chrz, °-]

    David.

    <Wallace> a écrit dans le message de news:
    3f31cd08.51ec.846930886@sybase.com
    [/QUOTE]

  3. #3
    Join Date
    Jan 2003
    Posts
    6
    Quote Originally Posted by rdjabarov
    Here's what I found:

    <QUOTE>
    From: David
    Date: 08-08-03 01:15
    Subject: Re: How can I read the content of transaction log?

    --------------------------------------------------------------------------------
    Hi Wallace,

    Well, Peter is right, it is very difficult to get some piece of information
    out from the log.
    It depends on what you are looking for. Have a look at dbcc log command:

    dbcc log (dbid, object_id, pagenum, rownum, nrecords, type, printopt)

    - dbid: the id of your database in sysdatabases (or you can get it with
    db_id("my_db"))
    - object_id: if you know especially on which object the transactions you're
    looking for are related.
    - pagenum / rownum: (optional) you can add the page number (if you don't
    have rownum) for this object
    or the transaction ID (if you can supply a rownum)
    - nrecords: number of records to examine. Put a negative value to get the
    last n records.
    - type: the type of transaction you can find. It goes from -1 (all records)
    to 36. Each number from 1 to 36
    represents a xact. For example: 4 for XREC_INSERT, 5 for XREC_DELETE, 9
    for XREC_MODIFY, etc...
    You can find the list at http://www.kaleidatech.com/dbcc2.htm
    - printopt: the print option: 0 if you want only headers, 1 if you want
    header + data.

    For example, if I want the header of the 20 last records of every xact on my
    object_id 263671987 in my database 4:
    1>dbcc traceon(3604)
    2>go
    1>dbcc log(4,263671987,0,0,-20,-1,0);
    2>go

    If the log is empty, it will say:

    LOG SCAN DEFINITION:
    Database id : 4
    Backward scan: starting at end of log
    log records for object id 263671987
    maximum of 20 log records.

    LOG RECORDS:

    Total number of log records 0
    DBCC execution completed. If DBCC printed error messages, contact a user
    with System Administrator (SA) role.

    If it is not, you will find hexa information. Have the closest look at the
    timestamps. They will tell you when
    the transaction occured.

    You can have a look also at Thierry Antinolfi's DBA Devil website. He
    provides a C++ built-in LogExplorer.
    http://perso.wanadoo.fr/dbadevil/en/logexpl.html

    Chrz, °-]

    David.

    <Wallace> a écrit dans le message de news:
    3f31cd08.51ec.846930886@sybase.com
    [/QUOTE]

    How does that answer or helps in answering the question??

  4. #4
    Join Date
    Jan 2003
    Posts
    6
    You need to add the user to the sysadmin role.

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Which only begs the question of why a non-sysadmin needs to read the log of a database. Even as a sysadmin, I have not found a need to read any of the transaction logs.

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Wow, 5-year-old thread...[note to self]watch what you say, it may come back and haunt you...even 5 years later...[/note to self]You got it!
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •