October 5, 2010 at 2:07 am
Yes, but still the locking issue which is very weird and cant find the reason for it.
Regards
Nader
October 5, 2010 at 2:18 am
nadersam (10/5/2010)
Yes, but still the locking issue which is very weird and cant find the reason for it.Regards
Nader
its time to set profiler trace
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
October 5, 2010 at 2:26 am
Already done that, and i see the following lock when i try to open tempdb properties
also i checked the Activity Monitor tool and i see the following wait
Wait Type:LCK_M_S
Resource :Key : 2:458752
Thanks
Nader
October 5, 2010 at 3:29 am
nadersam (10/5/2010)
Already done that, and i see the following lock when i try to open tempdb propertiesalso i checked the Activity Monitor tool and i see the following wait
Wait Type:LCK_M_S
Resource :Key : 2:458752
Thanks
Nader
Did you check where the exactly locking is happening ?
In a profiler trace
Under the errors and warning eventclass select blocker process report
And Locks : Locks timeout > 0
and another try i think you should first keep data from information_schema.columns to table variable and then use in further query
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
October 5, 2010 at 3:49 am
i tried blocked process report unfortunately it didnt capture anything in profiler even after changing blocked process report in sp_configure to 1000.
i added the lock timeout event though
and it showed the following
Lock Timeout 1:348
Lock Timeout :(0000c5e73325)
which is not very user fiendly 🙂
Thanks
October 5, 2010 at 3:59 am
Bhuvnesh (10/5/2010)
nadersam (10/5/2010)[hr
and another try i think you should first keep data from information_schema.columns to table variable and then use in further query
Did you try this ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
October 5, 2010 at 4:03 am
Still didnt try that but i will for sure as it will minimize access to system table.
Just was wonderring why is that happening, do u think reason for this lock is the use of temporary table or use of system table?
Regards
Nader
October 5, 2010 at 4:41 pm
I've tried recreating your problem on my laptop but with not much success. The only thing I can think is that maybe the table Audit_Trail_Transaction is locked.
A couple of questions
1) What isolation level are you using?
2) How many rows do you have in Audit_Trail_Transaction, and what indexes are on the table?
For my testing I was doing
begin tran
insert into table_with_trigger
I then issued use the following sql to see what locks had been taken out.
select * from sys.dm_tran_locks
If you run the following on a second connection then it will lock.
select * from [Audit_Trail_Transaction]
October 6, 2010 at 4:03 pm
Hi David,
I am really sorry for my late reply but i was traveling away with limited internet access.
I attached the full scripts for recreating the scenario.
Pls note that there is a line wait for delay in SP so i can check what's going with regards to locking.
Just run the insert sql below from mangment studio and then try to go to tempdb and view properties , u should get lock request timed out.
insert into Accounting_Period (name,date_from,date_to,performed_by,performed_in,performed_at) values ('name1','2010-10-08','2010-10-09','by','in','1-1-2010')
Thanks appreciating you replies
Nader
October 6, 2010 at 9:15 pm
Nader ,
I will still stick to avoid the syscolumns/sysobjects/sysindexes kind if sys tables in your logic , they will force for unnecessary locking in TempDb.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
October 7, 2010 at 3:09 am
syscolumns,sysindexes etc. is not in my logic but i was afraid they get blocked by creation of temporary tables which will kill performance.
Anyways i am thinking of changing logic totally, i wanted to pass the inserted record in trigger as a table variable to stored procedure but unfortunately it seems not supported in SQL 2005 :crying:
Any ideas?
I thought about table valued functions but this will include an additional select to base table.
Thanks guys
Nader
October 7, 2010 at 3:24 am
nadersam (10/7/2010)
i wanted to pass the inserted record in trigger as a table variable to stored procedure but unfortunately it seems not supported in SQL 2005 :crying:Any ideas?
Post the code.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
October 7, 2010 at 3:39 am
I added all code needed before, all i want to do now is instead of creating a temporary table and fill it with inserted record, just pass the inserted record as a table ariable to the stored procedure.
Pls note that the passed table variable needs to be generic as this will be used accross all tables in database also i am using SQL 2005
Thanks
Nader
Audit Trail table
CREATE TABLE [Audit_Trail_Transaction](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[Table_Name] [varchar](128) NOT NULL,
[Record_ID] [bigint] NOT NULL,
[Field_Name] [varchar](128) NOT NULL,
[Operation] [char](1) NOT NULL,
[Performed_By] [varchar](400) NOT NULL,
[Performed_In] [varchar](200) NOT NULL,
[Performed_At] [datetime] NOT NULL,
[Old_Value] [varchar](5000) NULL,
[New_Value] [varchar](5000) NULL,
CONSTRAINT [PK_Adt_Trl_Trnsctn] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Stored Procedure
Create PROCEDURE [SP_Audit_Trail_Transaction_Insert] (@Table_Name VARCHAR(128)) AS
DECLARE @Field_Position AS INT;
DECLARE @Field_Name AS VARCHAR(128);
DECLARE @Field_Type AS VARCHAR(128);
DECLARE @Conversion AS VARCHAR(128);
SELECT @Field_Position = 0;
WHILE ( @Field_Position < (SELECT MAX(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS with (nolock) WHERE TABLE_NAME = @Table_Name) )
BEGIN
SELECT @Field_Position = MIN(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS with (nolock) WHERE TABLE_NAME = @Table_Name AND ORDINAL_POSITION > @Field_Position;
SELECT @Field_Name = COLUMN_NAME, @Field_Type = DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS with (nolock) WHERE TABLE_NAME = @Table_Name AND ORDINAL_POSITION = @Field_Position;
IF ( @Field_Name NOT IN (N'ID', N'Performed_By', N'Performed_In', N'Performed_At') )
BEGIN
waitfor delay '00:01'
IF ( @Field_Type IN (N'DATETIME') )
SET @Conversion = ', 121';
ELSE
SET @Conversion = '';
EXEC (N'INSERT INTO [Audit_Trail_Transaction] ([Table_Name], [Record_ID], [Field_Name], [Operation], [Performed_By], [Performed_In], [Performed_At], [Old_Value], [New_Value])
SELECT
''' + @Table_Name + ''',
i.[ID],
''' + @Field_Name + ''',
''I'',
i.[Performed_By],
i.[Performed_In],
i.[Performed_At],
NULL,
CONVERT(VARCHAR(5000), i.[' + @Field_Name + ']' + @Conversion + ')
FROM
#ins with (nolock) i;');
--WAITFOR DELAY '00:01'
END
END
Base Table
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Accounting_Period](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](100) NOT NULL,
[Date_From] [datetime] NOT NULL,
[Date_To] [datetime] NOT NULL,
[Performed_By] [varchar](400) NOT NULL,
[Performed_In] [varchar](200) NOT NULL,
[Performed_At] [datetime] NOT NULL,
CONSTRAINT [PK_Accntng_Prd] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [UN_Accntng_Prd_Dt_Frm] UNIQUE NONCLUSTERED
(
[Date_From] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [UN_Accntng_Prd_Dt_T] UNIQUE NONCLUSTERED
(
[Date_To] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [UN_Accntng_Prd_Nm] UNIQUE NONCLUSTERED
(
[Name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [TR_Audit_Trail_Insert_Accounting_Period] ON [Accounting_Period] FOR INSERT AS
SET NOCOUNT ON;
CREATE TABLE #ins(
[ID] [int] ,
[Name] [varchar](100) NOT NULL,
[Date_From] [datetime] NOT NULL,
[Date_To] [datetime] NOT NULL,
[Performed_By] [varchar](400) NOT NULL,
[Performed_In] [varchar](200) NOT NULL,
[Performed_At] [datetime] NOT NULL)
insert INTO #ins select * FROM INSERTED with (nolock);
EXEC SP_Audit_Trail_Transaction_Insert 'Accounting_Period';
GO
October 7, 2010 at 3:43 am
nadersam (10/6/2010)
Hi David,I am really sorry for my late reply but i was traveling away with limited internet access.
I attached the full scripts for recreating the scenario.
Pls note that there is a line wait for delay in SP so i can check what's going with regards to locking.
Just run the insert sql below from mangment studio and then try to go to tempdb and view properties , u should get lock request timed out.
insert into Accounting_Period (name,date_from,date_to,performed_by,performed_in,performed_at) values ('name1','2010-10-08','2010-10-09','by','in','1-1-2010')
Thanks appreciating you replies
Nader
Table-Valued Parameter is not supported in SQL Server 2005.
Please refer: http://msdn.microsoft.com/en-us/library/bb510489.aspx
Thanks
October 7, 2010 at 3:46 am
can u pls tell me whats wrong in this syntax, i am trying to use table valued functions instead
Thanks
Create function [GetTableData] (@TableName varchar(50),@RowID integer)
returns table
AS
Declare @MySQL varchar(100);
Set @Mysql= 'select * from ' + @TableName + ' where id = ' + @RowID
exec (@Mysql)
return
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply