October 22, 2002 at 6:06 pm
Is there a stored procedure or script that anyone knows about which can take a snapshot of the current activity screen in EM? I want to see exactly what processes are running when a trigger is fired for a particular delete statement.
Matt.
October 22, 2002 at 6:26 pm
Your best bet would be to use SQL Profiler to run a trace that will be active when the delete trigger fires. Books Online will explain how to use Profiler. Profiler will allow you to see really whatever you desire in terms of database activity.
Sincerely,
Mark Cudmore, MCP
Sincerely,
Mark Cudmore, MCDBA
October 22, 2002 at 6:47 pm
Try sp_who or sp_who2. In adittion, you can use sp_lock
October 22, 2002 at 7:04 pm
Or if you are a brave soul you can query against sysprocesses, though sp_who is what Microsoft will recommend since sp_who should return the same results, regardless if Microsoft makes changes to the sysprocesses table.
Mark is right, though, if you want to know exactly what statements are being executed, there's no better tool than SQL Profiler.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
October 22, 2002 at 7:41 pm
thanks for the response guys.
I agree that profiler is the way to go most of the time. Unfortunately in this scenario I'm trying to capture a bulk delete statement that has happened 4 times in the last 12 months and causes large data loss. I'm actually using Log explorer (Lumigent) retrospectively on our transaction logs to try and identify where things are going wrong. I'm trying to determine if it's actually a user perpetrating this delete statement or whether it is a coding issue.
The really weird thing is that you can't actually delete the records we are losing through the application?? We are wondering whether it is a maintenance procedure being called by the application every few months to clear these specific records.
Anyway, the intent now is to create a trigger which makes use of sysprocesses to identify the current activity at the time of the delete statement (at least to get SOME kind of idea what's going on, the developers SWEAR it's not the app causing it!).
Sound OK to you guys?
Matt.
October 22, 2002 at 9:28 pm
This may help a bit. Warning, do not set count limit to low!!
-- Event/Evidence Table
CREATE TABLE [dbo].[CaptureEvent] (
[EventType] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Parameters] [int] NULL ,
[EventInfo] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]
GO
-- Any table / example uses table Test
CREATE TRIGGER Test_Delete ON [dbo].[Test]
FOR DELETE AS
-- Check for BIG delete!
-- More than million records
If (Select count(*) from Deleted)>1000000
Begin
Declare @MySpid Varchar(200)
Set @MySpid='DBCC inputbuffer('+Cast(@@Spid as varchar(12))+') With NO_INFOMSGS '
-- Get offending command
Insert into CaptureEvent
Exec (@MySpid)
-- Loginname
Insert into CaptureEvent
Select N'LoginName',0,Loginame
From Master..Sysprocesses
Where Spid=@@Spid
-- Hostname
Insert into CaptureEvent
Select N'HostName',0,HostName
From Master..Sysprocesses
Where Spid=@@Spid
-- When
Insert into CaptureEvent
Select N'When',0,Convert(nVarchar(50),GetDate(),113)
-- How many records got deleted!
Insert into CaptureEvent
Select N'Records Involved',0,Count(*)
From Deleted
End
GO
-- Insert some data and delete
-- Change limit to >0 for the test
Insert test values(1,'a')
GO
Delete from test where a=1
GO
Select * from CaptureEvent
GO
October 22, 2002 at 11:30 pm
Cheers for the script, we'd actually already got the new table and trigger created by the time you'd posted!! Works like a dream.
I appreciate the help.
Matt.
October 23, 2002 at 4:27 am
Instead of using sysporcesses, setup an on DELETE trigger that runs
select system_user, host_name(), app_name()
which will give you the actual login name of the user that fired delete, the machine they originated from, and the application name (such as SQL QA, SQL EM, SQL Agent or App name supplied by another app). This is what you are wanting to pinpoint.
In fact you can get really swift with system_user and the other two to prevent all deletes accept in certan circumstances. I have several tables that have a trigger like this.
CREATE TRIGGER tr_delspec_tblx
ON DELETE
AS
if system_user != 'Antares686'
begin
RAISERROR ('You are not authorized to delete data from this table.', 11, -1)
return
end
However if the data were ever truncated (which doesn't sound like you are having happen) no trigger will be able to help.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply