how can we use SQL server's log to trace user's operation?

  • I set my sql server recovery mode as FULL,so all of the logs will be fully recorded. if so, how can I use the log to track if users' operation(not including select) on the database? thanks

  • You can't really track user behavior through the logs. Without specialized tools, you can't view the information in the logs. That's really not what the logs are for. It's all about tracking the transactions as part of transaction management, recovery, and point in time restore. If you care about user behavior, then I'd recommend using Extended Events to track what the users are doing.

    Also, if your databases are on full recovery, make sure you have scheduled log backups.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore Roosevelt
    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Grant Fritchey wrote:

    You can't really track user behavior through the logs. Without specialized tools, you can't view the information in the logs. That's really not what the logs are for. It's all about tracking the transactions as part of transaction management, recovery, and point in time restore. If you care about user behavior, then I'd recommend using Extended Events to track what the users are doing.

    Also, if your databases are on full recovery, make sure you have scheduled log backups.

    Yes, my databases are on full recovery, and I have scheduled log backups.

    if using extended events to track users are doing, which will need more disk space to store the extended events file, is there any better tool to trace user's operation based on database log? thanks !

     

  • Not really. If you want less detailed usage, and you're on 2016+, you can use Query Store. If you want detailed usage, there are two real choices, Extended Events and Trace. I do not recommend Trace because it uses a lot more resources and can't be filtered easily. So you'll be dealing with the exact same amount of data, plus added load on your systems. You can output the Extended Event session to a drive other than the ones on your database server. However, to see detailed user behavior, Trace & Extended Events are your tools. Period. Even 3rd party vendors like Redgate are just using those same tools. That's what's available.

    There is a way to query the log, but it's A) undocumented officially and B) going to be a GIANT pain to try to query for individual user behavior. However, if you want to crawl down that rabbit hole (and I really advise against it), read up on it here.

    I'll say it one more time, your best bet is Extended Events.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore Roosevelt
    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • 892717952 wrote:

    I set my sql server recovery mode as FULL,so all of the logs will be fully recorded. if so, how can I use the log to track if users' operation(not including select) on the database? thanks

    Did you take a FULL backup after that?  If not, you may not doing what you think you're doing.

    And did you setup a decent Full backup and Logfile backup pair of jobs?

    Never mind... I didn't scroll down.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Grant Fritchey wrote:

    You can't really track user behavior through the logs. Without specialized tools, you can't view the information in the logs. That's really not what the logs are for. It's all about tracking the transactions as part of transaction management, recovery, and point in time restore. If you care about user behavior, then I'd recommend using Extended Events to track what the users are doing.

    Also, if your databases are on full recovery, make sure you have scheduled log backups.

    Agreed.  I also state that if such a thing must be auditable or could be used in a court of law, NOTHING in SQL Server where someone with sysadmin privs could get to it will do.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply