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

Question

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

892717952

SSC Eights!

Points: 843
More actions
August 30, 2021 at 1:49 am

#3924889

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

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

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

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 1

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

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

892717952 SSC Eights! Points: 843 More actions · Answer 2

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 !

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 3

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

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004355 More actions · Answer 4

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)

Jeff Moden SSC Guru Points: 1004355 More actions · Answer 5