Tag Info

New answers tagged

0

We have a similar situation and we had to create a larger script to resolve deletion in a proper manner. Note: Another issue you will run into with large delete operations (one or many transactions), is that your transaction logs will grow really fast during the runtime of the delete. So, be prepared for that in terms of disc space. It may be required ...


4

Order of table scan is never guaranteed. Order in which rows are locked is not guaranteed either. In addition, SQL Server has lock escalation, so you can't really say what the engine decides to lock, row, page, or table itself. Thus, deadlock may happen even if values of @id are different in concurrent sessions, but rows of interest happened to reside on ...


0

I've been experiencing the same thing. Worse yet my Snapshot gets selected as a deadlock victim and although the Snapshot and the Publisher think the Snapshot completed successfully, the Distributor never picks up the Snapshot files and does not play them into the subscriber. As a result when transactions affecting the new articles arrive the distribution ...


0

Following @Remus Rusanu's excellent answer, I have done the reader's task to connect the event to a stored procedure. In my case the sp will write the xml of the blocking event to a table, but you are free to do whatever you want at that position. So, follow Remus' code and create the queue, the service and the notification with a simple copy/paste from ...


7

is it possible to view the locks, along with the type, acquired during the execution of a query? Yes, for determining locks, You can use beta_lockinfo by Erland Sommarskog beta_lockinfo is a stored procedure that provides information about processes and the locks they hold as well their active transactions. beta_lockinfo is designed to gather as ...


2

Here is how I look at locks by process/table/lock type: SELECT HostName, "OS UserName", Login, spid, "Database", TableID, "Table Name_________", IndID, -- [Index Name], "Lock Type", "Lock Mode", Status, -- Resource, Count(*) AS "Lock Count" FROM ( SELECT Convert(VarChar(30), ...


0

Yes you can view the locks and its type during the query execution via Adam mechanics's SP_whoisactive click here to view Moreover if you want to create a block report with you can do with help of trace as explained here


1

You can view the locks for a session using sp_lock or sys.dm_tran_locks. In both ways you can filter by the session. You can also use Extended Events to do that.



Top 50 recent answers are included