Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
m.esteghamat
Starting Member
47 Posts |
 Posted - 2013-05-21 : 08:38:34
|
| Hi I am facing with a problem on Delete data in a table . I looked at "Sql server Logs" But unfortunatoly I did not find any good information . I Need to know how , from what station runs what query . Is this posible?Please help me . Thank you |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-05-21 : 10:24:35
|
| You can use profiler or trace - see tutorials here: http://www.mssqltips.com/sqlservertutorial/272/profiler-and-server-side-traces/However, that is for going forward; i.e., you set up the trace and then come back later to observe what has happened.If the events you are interested in have already happened, I don't know of a way to find who deleted them unless you had some kind of triggers or audit enabled. |
 |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2013-05-21 : 14:55:20
|
| I'd create a trigger for this to log the row(s) deleted, along with SYSTEM_USER, host_name(), APP_NAME(), user_name(), getdate() |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-05-21 : 15:52:55
|
| Now that russell point it out, I have to agree. In your case where you are trying to trouble-shoot a single table, a trigger is easier and better. |
|
|
|
m.esteghamat
Starting Member
47 Posts |
Posted - 2013-05-25 : 00:29:59
|
| Thank You For your HelpThis is good idea for important tables and datas. But This use only for some tables, And if I want to control All Tables, this methods is difficault. And I have Another Idea please say about it to me:There is an sp : who_2 I Can save output of this sp in specific times.But what times? every times that output is change. such as create new spid, or run new query (cmd) or ... . in this way I have all events that have did on my database. although I write a Good query that do act such as who_2. But in this method i Have main problem : how I understand a change is occured?query :select sd.name,sp.spid,sp.hostname,sp.cmd,sp.login_time,sp.loginame from sysprocesses sp inner join sysdatabases sd on sd.dbid=sp.dbidwhere sd.name like '%dbname%' |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-05-26 : 08:08:16
|
quote:
Originally posted by m.esteghamat
Thank You For your HelpThis is good idea for important tables and datas. But This use only for some tables, And if I want to control All Tables, this methods is difficault. And I have Another Idea please say about it to me:There is an sp : who_2 I Can save output of this sp in specific times.But what times? every times that output is change. such as create new spid, or run new query (cmd) or ... . in this way I have all events that have did on my database. although I write a Good query that do act such as who_2. But in this method i Have main problem : how I understand a change is occured?query :select sd.name,sp.spid,sp.hostname,sp.cmd,sp.login_time,sp.loginame from sysprocesses sp inner join sysdatabases sd on sd.dbid=sp.dbidwhere sd.name like '%dbname%'
I don't think sp_who2 is a good idea. The reason being that, a delete operation may take only a fraction of a milli-second, and you cannot run sp_who2 that frequently.If you want to audit all the tables, you can of course, create triggers on all tables, but that would be cumbersome and I would advise against that. Instead, consider one of the approaches that I suggested in my reply on 05/21/2013 at 10:24:35 |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
|
|
m.esteghamat
Starting Member
47 Posts |
Posted - 2013-05-27 : 03:36:35
|
| About "Change Data Capture" Or CDC a new feature from sqlserver 2008 : 1- It causes All Changed Data (inserted,deleted or Updated) store to a table 'cdc.dbo_tablename_ct' and some another control data. this is good. But this method has very serious Bound : After you enabled cdc on a table, You can not Alter it's design (change field name or ... ) and show this message : you cant not...table is replicated . Dose this problem has solution?2- I Need to save important Control on a table. In first Step : who (what login) connect and second : From what station (hostname) And last with what command change data? But cdc only save changed data. Are these any configs for cdc to save these control datas?(exactly such as sp_who2's output. short and useful. I dont need lose data.I Have them into my full and trn. backup for any time that I need.) |
|
|
|
|
|
|
|
|
More Information Needed On Sqlserver Logs