March 13, 2020 at 6:09 am
I need following details:
editing in Table or Via any other application.
I know that all the transactions are saved in .Ldf file.
Using Sql query/other tools i was able to fetch some details but not all the above for all transactions.
Is there any way to get all the above information for all transactions.
March 13, 2020 at 9:11 am
the only way I can see you doing that is setting up a profiler trace that saves to a table.
MVDBA
March 13, 2020 at 10:32 am
the only way I can see you doing that is setting up a profiler trace that saves to a table.
Ha! Gotta post this. I saw you recommend Profiler and just about popped until I saw which version of SQL Server we were referring to.
Step 1: Admit you have a problem.
Hi, my name is Grant and I have a problem with Profiler.
But in all seriousness, that's what I'd do in this situation too. Set up trace to capture the calls and connections. There's no other effective way to get this done.
"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
March 13, 2020 at 10:45 am
MVDBA (Mike Vessey) wrote:the only way I can see you doing that is setting up a profiler trace that saves to a table.
Ha! Gotta post this. I saw you recommend Profiler and just about popped until I saw which version of SQL Server we were referring to.
Step 1: Admit you have a problem.
Hi, my name is Grant and I have a problem with Profiler.
But in all seriousness, that's what I'd do in this situation too. Set up trace to capture the calls and connections. There's no other effective way to get this done.
lol that made me laugh, for once I beat one of the sql greats 🙂 I will admit
I am mike, I have a problem with profiler...grant will fix it
MVDBA
March 13, 2020 at 3:11 pm
Heh... Hi! My name is Jeff. Grant has a problem with SQL Profiler and I can't fix Grant! 😀 😀 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
March 13, 2020 at 6:43 pm
Heh... Hi! My name is Jeff. Grant has a problem with SQL Profiler and I can't fix Grant! 😀 😀 😀
HA!
And why you want to?
"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
March 13, 2020 at 9:36 pm
Going back to the original question, and the origin of the query. What you can track in SQL Server, with Trace or whatever is the application name, which is something the application sets itself in the connection string. Which for a simple C# program by default will be ".Net SqlClient Provider" (or somesuch).
And, yes, a Trace will be the best option. Not Profiler, but a server-side trace. (Which you can set up with Profiler.) And, I am tempted to say, on any version of SQL Server. Extended Events is too much hassle, in my opinion.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
March 13, 2020 at 10:28 pm
Jeff Moden wrote:Heh... Hi! My name is Jeff. Grant has a problem with SQL Profiler and I can't fix Grant! 😀 😀 😀
HA!
And why you want to?
I'll give you a hint:
Hi! My name is Jeff. Erland has a problem with Extended Events and so do I, not to mention the overhead of XML. Erland doesn't need fixing. 😀 😀 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
March 16, 2020 at 9:25 am
do NOT poke the mighty Grant on the subject of Extended Events. I learned the hard way.
He beat me like a naughty stepchild (methaphorically)
but on that version you have no choice
MVDBA
March 16, 2020 at 11:30 am
do NOT poke the mighty Grant on the subject of Extended Events. I learned the hard way.
He beat me like a naughty stepchild (methaphorically)
but on that version you have no choice
Nah, I've stopped all rants on the topic. Besides, we're talking 2008/R2 here, and the only right answer is to use Trace events.
By the way, most of the time, you don't need to access the XML to look at Extended Events. The Live Data Viewer works extremely well and is much more functional than Profiler ever was. Also, you can easily avoid the XML by using DBATools to consume the data from Extended Events. In fact, DBATools has a whole slew of Extended Events functionality that makes it easier to work with. So, you can get all the power and functionality of Extended Events (and see my blog for posts on things that it can do that Trace cannot) and make it easy to use.
Honestly, the only reason to use Trace any more is because you're working on 2008R2 or less, as we are here.
"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
March 16, 2020 at 11:49 am
there is another option and I hate even suggesting it... you could put a trigger on the tables that you want to monitor
I know that you can use SUSER_NAME() in your trigger, but I don't know how to track the application name (from the top of my head), maybe I can figure it out.
and Grant, keep on ranting
MVDBA
March 16, 2020 at 11:50 am
Using Sql trace i was able to get the Username but not the Query origin.
March 16, 2020 at 11:58 am
Using Sql trace i was able to get the Username but not the Query origin.
You mean the application that made the call? Unfortunately, those are optional values in a connection, so you may not see them. You should be able to get the calling machine though. That will at least narrow things down.
"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
March 16, 2020 at 12:01 pm
have you used the "select all columns" option in profiler? - you should have the application name (if it was supplied in the connection string or left as blank)
also if you can find the username then you can find which PC they are logged in on (google commandshell query)- then either ask them over the phone, or use tools to find the processes they were running at the time
MVDBA
March 16, 2020 at 12:12 pm
Unfortunately , if you are going through a web server it is nigh on impossible to get the level of data you want without coding in some sort of logging for yourself
everything in profiler will show up as "webserver01" (or some such)
If it's not then you can cross reference that data you have with
SELECT * FROM master.sys.dm_exec_connections
this has the client net address
Just a few of the tools you can use
MVDBA
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply