SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 More Information Needed On Sqlserver Logs
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

m.esteghamat
Starting Member

24 Posts

Posted - 05/21/2013 :  08:38:34  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3643 Posts

Posted - 05/21/2013 :  10:24:35  Show Profile  Reply with Quote
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.
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 05/21/2013 :  14:55:20  Show Profile  Visit russell's Homepage  Reply with Quote
I'd create a trigger for this to log the row(s) deleted, along with SYSTEM_USER, host_name(), APP_NAME(), user_name(), getdate()
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3643 Posts

Posted - 05/21/2013 :  15:52:55  Show Profile  Reply with Quote
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.
Go to Top of Page

m.esteghamat
Starting Member

24 Posts

Posted - 05/25/2013 :  00:29:59  Show Profile  Reply with Quote
Thank You For your Help
This 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.dbid
where sd.name like '%dbname%'

Edited by - m.esteghamat on 05/26/2013 03:19:48
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3643 Posts

Posted - 05/26/2013 :  08:08:16  Show Profile  Reply with Quote
quote:
Originally posted by m.esteghamat

Thank You For your Help
This 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.dbid
where 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
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 05/26/2013 :  10:10:46  Show Profile  Visit russell's Homepage  Reply with Quote
Can look at Change Data Capture or auditing software.
Go to Top of Page

m.esteghamat
Starting Member

24 Posts

Posted - 05/27/2013 :  03:36:35  Show Profile  Reply with Quote
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.)

Edited by - m.esteghamat on 05/27/2013 03:49:50
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000