Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Data Deletion!! Expand / Collapse
Author
Message
Posted Friday, March 28, 2014 3:54 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, July 17, 2014 6:40 AM
Points: 2,567, Visits: 3,799
Hi Experts,

Every midnight 1 row is getting deleted from a particular table. Is there anyway to trace how it happens?

I have checked whether any job is running at that time or any trigger causing the issue but nothing was there at that time.

please help.
Post #1555814
Posted Friday, March 28, 2014 4:50 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 4:18 AM
Points: 1,539, Visits: 8,127
If you're sure of the time, I'd run a server-side trace 5 minutes either side of the time and look in that.

BrainDonor
Linkedin
Blog Site
Post #1555827
Posted Friday, March 28, 2014 5:33 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 2:56 AM
Points: 2,603, Visits: 2,061
Check the option of Extended event.

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
Post #1555841
Posted Friday, March 28, 2014 6:08 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 4:02 PM
Points: 15,501, Visits: 27,887
free_mascot (3/28/2014)
Check the option of Extended event.


Sorry? What?

Do you mean set up an extended event session to capture sql batches and rpc calls? That's a good idea. I'm not sure what you mean by "option of Extended Event"

The main reason to use extended events over trace events is that they have a lower impact on the server. The problem with using them in pre-2012 is that there's no GUI for consuming the data forcing you to write queries against the XML output.


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1555856
Posted Friday, March 28, 2014 8:38 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 9:51 AM
Points: 1,194, Visits: 2,208
Ratheesh.K.Nair (3/28/2014)
Hi Experts,

Every midnight 1 row is getting deleted from a particular table. Is there anyway to trace how it happens?

I have checked whether any job is running at that time or any trigger causing the issue but nothing was there at that time.

please help.


Setup SQL Auditing on that table for deletes.

--
SQLBuddy
Post #1555958
Posted Sunday, March 30, 2014 10:58 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 2:56 AM
Points: 2,603, Visits: 2,061
Grant Fritchey (3/28/2014)
free_mascot (3/28/2014)
Check the option of Extended event.


Sorry? What?

Do you mean set up an extended event session to capture sql batches and rpc calls? That's a good idea. I'm not sure what you mean by "option of Extended Event"

The main reason to use extended events over trace events is that they have a lower impact on the server. The problem with using them in pre-2012 is that there's no GUI for consuming the data forcing you to write queries against the XML output.


Yes, I mean extended event session if user is comfortable with XML output.


---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
Post #1556326
Posted Thursday, April 3, 2014 7:46 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, July 17, 2014 6:40 AM
Points: 2,567, Visits: 3,799
Thanks Everyone for the Reply.


Free_mascot : Do you have any template for tracking this using extended events?
Post #1557978
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse