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...

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,...

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...

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...

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...

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...

Yes, but still the locking issue which is very weird and cant find the reason for it.

Regards

Nader

Man this is amuch better solution , thank u very much.

Nader

thanks for ur help

thats what i did pls tell me if am wrong

for trigger

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER TRIGGER [TR_Audit_Trail_Insert_Accounting_Period] ON [Accounting_Period] FOR INSERT AS

SET NOCOUNT ON;

CREATE TABLE #ins([ID] [int]...

Here is the trigger code

Create TRIGGER [TR_Audit_Trail_Insert_TableName] ON TableName FOR INSERT AS

SET NOCOUNT ON;

SELECT * INTO #ins FROM INSERTED;

EXEC SP_Audit_Trail_Transaction_Insert 'TableName';

and this is the SP called from within the...

Thanks for the link.

But does that mean that SQL server will not be able to access indexes and temporary tables , till the lock caused by the creation of the...

The error is:

Lock request time out exceeded error:1222

Thanks

Thanks for your reply.

1. Locking tempdb (is whole db locked) meaning no one can access table indexes.

NO.Locking mechanism work on row level --> page level ---> then table level.no on...

Grant Fritchey (5/26/2010)

---

I would suggest using connection pooling. As much as possible, reusing connections is the way to go because it reduces the amount of memory that connections take up...

Thanks for ur reply