SQL Server Performance Forum – Threads Archive

Log information

Hello All, I’m pretty new to SQL Server and was wondering, If somebody could answer to my following question. Before I list the question. Here’s my environment details. I’ve a user database which is runing under Full model recovery and all of the txn logs are getting backup every 30 minutes to a drive. Now my question is, Lets say somebody ran a update command against my database and Is there a way, I know who ran it? what was the user id? what host name it ran from? etc etc??? In Oracle we have a logminer which does this. But in SQL Server. I dont know, if this option is available or not. If yes, could somebody please list all the steps to find out these. Thanks in advance.
DBA.

to the best of my knowledge you need a 3rd party tool to do this kind of thing in SQL Server…i think Log PI and Log Explorer are two such tools. This pagehttp://www.sql-server-performance.com/log_explorer_spotlight.asp
has info on log explorer Ben ‘I reject your reality and substitute my own’ – Adam Savage

New tool is released by red-gate to recover the data by using the log. it gives you details of "who did what, when"
read more from here http://www.sql-server-performance.com/da_sql_rescue_spotlight.asp

Thank you guys for replying to my question. I did take a look at the 2 products and both of them looks totally cool. thanks again. Now In addition to the question I asked. I’ve one more question. Hope i’m not annoying.. What happens, If I change my database recovery model to "Simple" from "Full recovery"
– run the update command and change back the recovery to "Full". What are the expected results from this change? Thanks in advance….
DBA

Still the update will be logged and meanwhile if the Tlog has reached to the checkpoint then the log will be truncated without writing to the disk. In this case ensure to maintain full backup in case to retrieve the work finished. Refer to the books online for complete understanding on RECOVERY MODELS in SQL Server 2000. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Read the
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_bkprst_4l83.asp to get an idea of recovery models in SQL Server
In Addition, there are plenty of articles if you google.

You can use Profiler to run a trace to collect any information about specific types of transaction.
Of course if your logging UPDATEs on a OLTP database you’re going to have storage problems.

]]>