InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 07:47:33 GMT20http://www.sqlservercentral.com/Forums/Topic1395009-1550-1.aspxThanks SQLACT & Anthony for the reply.Will check those and update you guysTue, 11 Dec 2012 05:19:08 GMTRatheesh.K.Nairhttp://www.sqlservercentral.com/Forums/Topic1395009-1550-1.aspxHi,Check sp_whoisactive procedure after running the above statements. It will tell you which session id is blocing the table. ORU can check the information sys.dm_tran_locks to see if there are any shared and exclusive locks on it. For more information goto Adam machanic blog. http://sqlblog.com/blogs/adam_machanic/archive/tags/sp_5F00_whoisactive/default.aspxTue, 11 Dec 2012 05:17:47 GMTsankar276http://www.sqlservercentral.com/Forums/Topic1395009-1550-1.aspxThe delete must have been waiting for the bulk insert to release a lock on some resource it had held open.If you have historic reporting, try looking at what locks where held for the bulk insert, or debug where both of the statements could execute at the same time for transactional concurency and things not committing transactions where they should etc.Tue, 11 Dec 2012 04:55:39 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1395009-1550-1.aspxAny chance of referential integrity between these 2 tables?Tue, 11 Dec 2012 04:52:29 GMTSQLSACThttp://www.sqlservercentral.com/Forums/Topic1395009-1550-1.aspxHi Experts,I came across a blocking issue in which the below two process are involved the second statement is blocked by first,but the strange part is they don't have anything in commoninsert bulk [dbo].[table1]([MeterReadID] uniqueidentifier,[MeterID] uniqueidentifier,[AssetID] uniqueidentifier,[EntryDate] datetime,[ReadDate] datetime,[Count] bigint,[UserName] nvarchar(50),[MeterSourceID] uniqueidentifier,[MeterExtendedSourceID] uniqueidentifier,[IsRollOver] bit,[StartRead] bigint,[Comment] nvarchar(50),[IsInvalid] bit,[IsCredit] bit,[Credits] bigint,[IsEstimate] bit,[IsSuspect] bit,[IsSuspectHigh] bit,[IsVirtual] bit,[IsRefill] bit,[RefillCount] bigint,[CreatedDate] datetime,[CreatedBy] uniqueidentifier,[ModifiedDate] datetime,[ModifiedBy] uniqueidentifier,[DWIsDeleted] bit,[DWStartDate] datetime,[DWEndDate] datetime,[IsInstall] bit,[IsFinal] bit)DECLARE @DuplicateRows bigint, @RowsUpdated bigint ; DELETE c FROM dbo.[table2] AS c INNER JOIN dbo.[table2_Deletes] AS a ON c.[MeterReadID] = a.[_MeterReadID] AND c.DWEndDate = a.[ModifiedDate] ; SET @DuplicateRows = @@ROWCOUNT ; UPDATE c SET c.DWEndDate = ISNULL(a.[ModifiedDate], GETDATE()) FROM dbo.[table2] AS c INNER JOIN dbo.[table2_Deletes] AS a ON c.[MeterReadID] = a.[_MeterReadID] WHERE c.DWEndDate IS NULL AND a.IsPruned = 0 ; SET @RowsUpdated = @@ROWCOUNT ; DROP TABLE dbo.[table2_Deletes] ; SELECT @DuplicateRows AS DuplicateRows, @RowsUpdated as RowsUpdated ;The delete statement is blocked by bulk insert. Can anyone please point out why this happened?Tue, 11 Dec 2012 03:50:05 GMTRatheesh.K.Nair