September 2, 2008 at 11:05 pm
hi,
i want to monitor list of queries fired on any table ...a kind og log
i know this could be done with the help of trigger ...but dont know HOW
Please help me
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
September 3, 2008 at 2:41 am
You can only use a trigger for Insert\Update\Delete queries not Select and I don't think you can get the query text then either.
You can use SQL Server Profiler to set up a trace that traces Stored Procedure and T-SQL events which will get you all SQL Statements run against a server. You can also filter this by database.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 4, 2008 at 12:21 pm
Here is a method I have used. It should be noted that each stored procedure to be logged will have to be modified to include:
EXEC Dbo.PerformanceStats_Update @@Procid
Use of this logging will place a load on your server which may be unacceptable, so use with caution. With that in mind you might not want to log those SPs which you know are heavily used.
You can stop logging by simply changing the name of the log table (PerformanceStats)
Also note that these scripts use sysobjects and these may not be supported in future releases of SQL server. Hence a user may wish to change these to match the new sys.objects available in SQL2005
/****** Object: Table [dbo].[PerformanceStats] Script Date: 8/25/2006 11:25:48 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PerformanceStats]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[PerformanceStats]
/****** Object: Table [dbo].[PerformanceStats] Script Date: 8/25/2006 11:25:48 AM ******/
CREATE TABLE [dbo].[PerformanceStats] (
[RecNo] [int] IDENTITY (1, 1) NOT NULL ,
[ProcName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ProcStart] [datetime] NOT NULL ,
[ProcFinish] [datetime] NOT NULL ,
[UserName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[MachineName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ApplicationName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Counter] [bigint] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PerformanceStats] WITH NOCHECK ADD
CONSTRAINT [PK_PerformanceStats] PRIMARY KEY CLUSTERED
(
[RecNo]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PerformanceStats] ADD
CONSTRAINT [DF_PerformanceStats_ProcStart] DEFAULT (getdate()) FOR [ProcStart],
CONSTRAINT [DF_PerformanceStats_ProcFinish] DEFAULT (getdate()) FOR [ProcFinish],
CONSTRAINT [DF_PerformanceStats_UserName] DEFAULT (rtrim(suser_sname())) FOR [UserName],
CONSTRAINT [DF_PerformanceStats_MachineName] DEFAULT (rtrim(host_name())) FOR [MachineName],
CONSTRAINT [DF_PerformanceStats_ApplicationName] DEFAULT (app_name()) FOR [ApplicationName],
CONSTRAINT [DF_PerformanceStats_Counter] DEFAULT (0) FOR [Counter],
CONSTRAINT [IX_PerformanceStats_1] UNIQUE NONCLUSTERED
(
[ProcName],
[ApplicationName]
) ON [PRIMARY]
GO
CREATE INDEX [IX_PerformanceStats] ON [dbo].[PerformanceStats]([RecNo]) ON [PRIMARY]
exec sp_addextendedproperty N'MS_Description', null, N'user', N'dbo', N'table', N'PerformanceStats', N'column', N'RecNo'
exec sp_addextendedproperty N'MS_Description', N'The stored procedure being executec', N'user', N'dbo', N'table', N'PerformanceStats', N'column', N'ProcName'
exec sp_addextendedproperty N'MS_Description', N'Set only when first row entered', N'user', N'dbo', N'table', N'PerformanceStats', N'column', N'ProcStart'
exec sp_addextendedproperty N'MS_Description', N'Updated each time proc is executed', N'user', N'dbo', N'table', N'PerformanceStats', N'column', N'ProcFinish'
exec sp_addextendedproperty N'MS_Description', N'How many times executed between ProcStart and ProcFinish times', N'user', N'dbo', N'table', N'PerformanceStats', N'column', N'Counter'
GO
CREATE PROCEDURE dbo.PerformanceStats_Update
@Procid INT
AS
DECLARE @Counter Int
DECLARE @NewCount Int
SET @NewCount = 1
IF EXISTS (select xtype from dbo.sysobjects WHERE Id = OBJECT_ID('PerformanceStats'))
Begin
SELECT @Counter = (SELECT counter
FROM PerformanceStats
WHERE ApplicationName = RTRIM(App_Name()) AND ProcName = RTRIM(object_name(@procid)))
If ISNUMERIC(@counter) = 0
BEGIN
INSERT INTO PerformanceStats (ProcName, Counter)
VALUES (RTRIM(object_name(@procid)), @NewCount)
END
ELSE
BEGIN
SET @NewCount = @Counter + 1
UPDATE PerformanceStats
Set Counter = @NewCount, ProcFinish = GetDate()
WHERE ApplicationName = RTRIM(App_name()) AND ProcName = RTRIM(object_name(@procid))
END
END
GO
INSERT INTO PerformanceStats ([UserName],[ProcName])
SELECT 'Initialization', sob.name
FROM sysobjects so
LEFT OUTER JOIN (sysobjects sob left outer join sysdepends on sob.id = sysdepends.id)
on sysdepends.depid = so.id
WHERE so.xtype = 'u' AND sob.xtype = 'p'AND NOT so.name = 'dtproperties'
GROUP BY sob.name
ORDER BY sob.name
GO
Each existing stored procedure must be modified to include:
EXEC Dbo.PerformanceStats_Update @@Procid
September 4, 2008 at 9:50 pm
Thanks .i 'll try this ...
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply