2

We have a SQL Server 2012 Enterprise database running in Full Recovery Model with transaction log backups running every minute. A query was run last night which means we need to recover data from a certain point in time - so we restore transaction logs to that point in time, recover the data we need, all good.

What we want to know, is there a way that we can tell what login ran a query using the transaction log backups?

  • If transaction has committed and VLF that was part of transaction is re-utalized and overwritten you cannot tell it from transaction log who ran certain transaction. You should try default trace although its not a guarantee. What you exactly want to capture ? – Shanky Nov 13 '14 at 22:18
  • You have to use undocumented fn_dblog Begin Time, SUSER_SNAME([Transaction SID]) or fn_dump_dblog -- when reading from T-log backups. Be careful and try it on a NON PROD system as every time fn_dump_dblog is called, it creates a new hidden SQLOS scheduler and up to three threads, which will not go away (and will not be reused) until a server restart. – Kin Shah Nov 13 '14 at 22:22
  • That's all gone a bit over my head. We've restored the database to the point in time right before the change happened. It happened last night (15 hours ago) so it's well and truly committed. The data has been fixed, but what we're trying to work out is what login was used to run the query. – Greg Nov 13 '14 at 22:32
  • @Greg Check my reference link and it will show you a method to read your transaction log. Its not that straight forward to be able to read T-log and come on a conclusion - If you have not done it or are new to it. You are better off trying a 3rd party tool (like Apex SQL Log) if it is that critical for you to find out who did the mess. – Kin Shah Nov 13 '14 at 22:38
  • What query was run that you want to capture. Transaction log logs different names for different events so unless you tell what query you are looking for its not good advise to ask you run fn_dblog on entire log file – Shanky Nov 13 '14 at 22:59
4

Converting my comment as answer :

You have to use undocumented fn_dblog Begin Time, SUSER_SNAME([Transaction SID]) or fn_dump_dblog -- when reading from T-log backups. Be careful and try it on a NON PROD system as every time fn_dump_dblog is called, it creates a new hidden SQLOS scheduler and up to three threads, which will not go away (and will not be reused) until a server restart.

Also, its not that straight forward to be able to read T-log and come on a conclusion - If you have not done it or are new to it. You are better off trying a 3rd party tool (like Apex SQL Log) if it is that critical for you to find out who did the mess.

  • Apex SQL Log worked a treat – Greg Nov 14 '14 at 23:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.