Retrieve operation in db

Question

Post reply

Retrieve operation in db

gandalf_03_75

SSC Rookie

Points: 35
More actions
September 10, 2009 at 8:17 am

#210468

Hello
it's possible to retrieve the last operation committed in db and teh ip address of client that execute this operation?
thanks in advance

Viewing 9 posts - 1 through 8 (of 8 total)

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

Paul White SSC Guru Points: 150467 More actions · Answer 1

Assuming that the client connected to the SQL Server over TCP/IP then normally , yes.

It depends on what you mean by 'last operation committed' though.

Perhaps you could expand on the requirement a bit?

Are you, for example, interested in just data changes to tables, DDL changes, or...?

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Atul DBA Hall of Fame Points: 3610 More actions · Answer 2

A limited information may be retrived from the Transaction Log file if your database is in Full recovery mode. Otherwise it is almost impossible to get the info.

Silverfox SSC-Insane Points: 23150 More actions · Answer 3

depending on what information you are after, you could query the default trace file, there is another recent post that describes how to do that

--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]

Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]

gandalf_03_75 SSC Rookie Points: 35 More actions · Answer 4

Thanks for your answers. The problem is to understand which user has launched a stored that was cleaning the db.

Thanks

Paul White SSC Guru Points: 150467 More actions · Answer 5

SELECT client_net_address

FROM sys.dm_exec_connections

WHERE session_id = @@SPID

AND net_transport = N'TCP'

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Silverfox SSC-Insane Points: 23150 More actions · Answer 6

Paul White (9/11/2009)
SELECT client_net_address FROM sys.dm_exec_connections WHERE session_id = @@SPID AND net_transport = N'TCP'

nice, but only works for spids that are still active. still nice to know though.

--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]

Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]

Paul White SSC Guru Points: 150467 More actions · Answer 7

Silverfox (9/11/2009)
nice, but only works for spids that are still active. still nice to know though.

The idea is to include code based on that snippet into the procedure of interest, and log the data somewhere.

I left some thinking and work for the questioner (see my signature) 🙂

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

gandalf_03_75 SSC Rookie Points: 35 More actions · Answer 8

Great idea now puts the result of the query log table and the game is done. Too bad they can not know who cleaned the db yesterday.

Thank you for your willingness

😎