Make MS SQL Server 2014 Log Every Query
-
We have a very large Microsoft SQL Server 2014 box and for application forensics, we'd like to have a way to log every query that is attempted and store it so that we can look through it. We know this will have high overhead. Trying to find a reasonable way to do this. We know that if we update to 2016 that you can do this natively, but in 2014 we aren't sure how best to accomplish this.
Thoughts?
-
Updating isn't an option?
-
@dustinb3403 said in Make MS SQL Server 2014 Log Every Query:
Updating isn't an option?
Soon, but not in a "today" context, no.
-
We are thinking SQL Server Profiler might do the trick. Going to look into that first.
-
-
@scottalanmiller Check out Extended Events. It can log everything that happens.
Fastest way to learn more is probably to search youtube for sql server extended events.Extended events are suppose to be the replacement for sql traces/profiling.
-
@pete-s said in Make MS SQL Server 2014 Log Every Query:
@scottalanmiller Check out Extended Events. It can log everything that happens.
Fastest way to learn more is probably to search youtube for sql server extended events.Extended events are suppose to be the replacement for sql traces/profiling.
Excellent, thanks.
-
@pete-s said in Make MS SQL Server 2014 Log Every Query:
Extended Events
@Pete-S happen to know where the default location of the event files are saved?
I created a new session and I believe it is properly showing the queries ran, but if I try to change the place where the file is logged to is doesn't start, but if I leave the default set I can't find the file!!
-
@romo said in Make MS SQL Server 2014 Log Every Query:
@pete-s said in Make MS SQL Server 2014 Log Every Query:
Extended Events
@Pete-S happen to know where the default location of the event files are saved?
I created a new session and I believe it is properly showing the queries ran, but if I try to change the place where the file is logged to is doesn't start, but if I leave the default set I can't find the file!!
I'm not sure the default is actually a file at all, it may be memory buffers.
However it sound like you have a user rights issue. Make sure SQL server is allowed to write to the file where you put it. -
@pete-s said in Make MS SQL Server 2014 Log Every Query:
@romo said in Make MS SQL Server 2014 Log Every Query:
@pete-s said in Make MS SQL Server 2014 Log Every Query:
Extended Events
@Pete-S happen to know where the default location of the event files are saved?
I created a new session and I believe it is properly showing the queries ran, but if I try to change the place where the file is logged to is doesn't start, but if I leave the default set I can't find the file!!
I'm not sure the default is actually a file at all, it may be memory buffers.
However it sound like you have a user rights issue. Make sure SQL server is allowed to write to the file where you put it.Also remember The SQL instance is usually not running with a user context. Generally a system service.
