A blocking SQL command prevents other reading or writing operations while it is being executed.

learn more… | top users | synonyms

4
votes
1answer
43 views

Blocking Extended events

I have very basic table CREATE TABLE [dbo].[Table_1]( [Test] [int] NULL) INSERT INTO [dbo].[Table_1]([Test]) Select 1 sp_configure'block process threshold', 1 --> 1 sec go To create ...
5
votes
0answers
66 views

How to solve blocking on tempdb

I am having tempdb contention (I guess, at least) on one of the servers (SQL Server 2008 R2). Both the blocker and the waiters (10 of them) has PAGELATCH_EX wait type on database tempdb and all ...
1
vote
0answers
32 views

How to avoid Head Blocker query doing nothing?

We are experiencing periodic slowdowns of our SQL Server 2014. Today I identified what appears to be the cause, in the SQL Activity Monitor. Process 61 has: Task State - blank Command - blank Wait ...
0
votes
1answer
59 views

Query hangs in preparing state and blocks others in waiting for table flush

In a webapp I'm working on, there's an UPDATE that's slowing / blocking out several other queries: UPDATE products SET visible = 1 WHERE merchant_id = in_merchant_id AND source_code IN ( ...
1
vote
1answer
27 views

Lock priority for scripts run in maintenance windows

We run maintenance windows weekly and monthly. Some of our maintenance windows are online and the last thing we want is our scripts to block live queries from our applications. Is there a way to set ...
2
votes
1answer
24 views

Refreshing a table using CREATE TABLE, DROP and Rename

A read-only database (database1) is updated via log shipping every minute, then a procedure queries multiple tables in it and refreshes a large table on database2. For this process I run an ...
3
votes
1answer
91 views

PAGELATCH_EX occures in update query with UPDLOCK, READPAST

We have a queue organized with a table on MS SQL Server 2014: CREATE TABLE [queue].[Messages]( [Id] [bigint] IDENTITY(1,1) NOT NULL, [Status] [tinyint] NOT NULL, [Data] [nvarchar](max) ...
6
votes
1answer
232 views

How can I view how long a query has spent waiting for locks, without a profiler?

I'm trying to diagnose a query that is intermittently taking a long time. I suspect it may be blocked trying to acquire a lock. I don't have permissions to use a profiler in the environment ...
2
votes
0answers
90 views

SELECT blocking SELECT?

I have huge outputs from pg_stat_activity and the result from the second "combination of blocked and blocking activity" report from https://wiki.postgresql.org/wiki/Lock_Monitoring (2nd version), with ...
2
votes
2answers
61 views

What could be preventing updates and inserts to a table without anything visibly blocking the process?

The following has happened on more than one occasion. Updates or inserts to a certain table (I'm not 100% sure but there seem to be a couple of tables it always happens to) fail due to a timeout. If ...
7
votes
1answer
502 views

How do I prevent a massive SELECT from blocking other statements?

Our SQL Azure database contains a massive SELECT statement that runs something like once per day. The heavy SELECT statement contains no locking hints. Recently we observed some stalls in production ...
0
votes
0answers
30 views

Blocking SPID has Zero Disk IO and Zero CPU Time

Running sp_who2 and following the BlkBy trail up to the root cause of blocking returns a SPID which has CPUTime and DiskIO values of 0; yet it's blocking 4 other SPIDs. I'm confused by how this is ...
1
vote
1answer
138 views

SSIS Blocking itself with IsolationLevel read Uncommited and TransactionOption supported

My Project components have IsolationLevel = ReadUncommitted and TransactionOption = supported. My query is a select on table Y verifying if not exists in table X and the result is written to table X. ...
5
votes
1answer
301 views

Why does UPDLOCK cause SELECTs to hang (lock)?

I've got a select in SQL SERVER that locks the whole table. Here's the setup script (make sure you don't overwrite anything) USE [master] GO IF EXISTS(SELECT 1 FROM sys.databases d WHERE d.name = ...
2
votes
0answers
188 views

SQL Reporting Services (SSRS) - Blocked process on ReportServer, SP CheckSessionLock [duplicate]

We frequently see blocking on our SSRS box which houses both front-end Report Manager ReportingService and the SQL database engine with ReportServer & ReportServerTempDB Catalog databases ...
1
vote
1answer
111 views

How is an idle SQL Server connection causing blocking?

I have a busy, transactional SQL Server (2012) which is having intermittent problems with blocking (every couple of days or so). I've been using extended events to capture blocked-process reports, but ...
1
vote
2answers
72 views

Spotlight for SQL Server LockType explanation required

I have just started using Spotlight as I'm a software developer, but I am forced to identify database issues causing blocking (Spotlight shows me AWAITING_COMMAND at the points and all other processes ...
2
votes
2answers
129 views

Blocked Process Report -two updates causing a long block

I have a .NET application which is getting SQL update timeouts and I believe that some sort of blocking/locking is taking place which is causing this. I have ran a blocking process report on the ...
2
votes
2answers
243 views

Blocked Process Report SQL Server 31 hour duration

I have an application (.NET EF 6 ) which is getting timeouts when submitting/reading data from the database. Whilst trying to diagnose the problem I used Blocked Process Reports from SQL server ...
2
votes
0answers
27 views

Locks while stream reading and deleting/inserting involving the same tables?

I have a question regarding a potential block/lock situation: I am reading data using a select command, which is then modified externally and uploaded to a temporary table. A stored procedure will ...
18
votes
5answers
788 views

In Sql Server, is there a way to check if a selected group of rows are locked or not?

We are attempting to update/delete a large number of records in a multi-billion row table. Since this is a popular table, there is a lot of activity in different sections of this table. Any large ...
0
votes
1answer
75 views

SQL Blocking causing timeouts

We have a long running transaction (with nested trans), it runs for about 2min. In this time it does a heap of insert, update and selects. At the same time users still need to be able to use the ...
4
votes
2answers
991 views

SSRS Blocking Processes

We have a busy SSRS Reporting server (SQL Server 2008 R2) on which we see frequent blocking between the below processes: [dbo].[CheckSessionLock] [dbo].[WriteLockSession] We are aware that some ...
0
votes
0answers
191 views

Postgresql: Update or insert stuck

I'm running update-or-insert query on a table of approximatelly 3mln rows, 4 columns. Query is part of every day process, and for same days, the query stucks forever. It's just blocked. It's ...
1
vote
1answer
476 views

SQL Server 2012 merge replication blocks other insert/updates during sync

I have a merge replication setup between two SQL Server 2012 databases via Pull method. There are several merge replications affecting different table sets (3 sets 2-5 tables each, actually). There ...
1
vote
0answers
72 views

Trying to add a NULLable column to a table with over 90 million rows takes a very long time

I have been trying to add a NULLable column to a table with over 90 million rows. ALTER TABLE <TableName> ADD <FieldName> INT NULL; It is taking a very long time (been running for over ...
2
votes
3answers
118 views

Why is sql server waiting for acknowledgment?

I have an application that often request a big set of rows [about 20K] from a sql view, in the mean time other applications can't update the table from where the data comes from, the update query's ...
1
vote
0answers
20 views

CLR UDT SCH-M blocking

I’m hoping someone can provide some guidance on a problem I’m having with schema locking on C# CLR UDT’s. I’ve been on the Internet for an extended period of time researching my problem without ...
0
votes
0answers
93 views

Sleeping queries blocking rebuild index and update statistics job in Always on

At one of your client sides we have configured Always on with synchronous mode.Also we have schedule rebuild index and update statistics job which runs in night every alternate day. the issue is ...
1
vote
1answer
237 views

What happens if two mySQL queries both try to lock the same row at the same time?

So this isn't exactly my strong suit, but here's my basic understanding of the situation: If two threads of the same application both try to execute the same query at the same time, mySQL will ...
7
votes
1answer
148 views

When does SQL Server acquire locks?

The list of isolation levels in SQL Server found here states that write locks acquired within a transaction are retained until the end of the transaction. However it doesn't mention anything about ...
1
vote
1answer
74 views

My connection to a Mysql DB has been blocked after spamming queries, for how long?

I am creating a web application that depends on querying a legacy DB without an API. It's MySql. Off course the lack of an API eventually got me in trouble. A feedback loop while testing the app ...
1
vote
0answers
371 views

Renaming a column with sp_rename hangs SQL Server 2012

I was attempting to remove a column from a table within SQL Server 2012 for one of our developers. As a precaution, before dropping the column, we rename them to make sure that everything continues ...
0
votes
1answer
150 views

How to determine which statement is blocking an update in SQL Server

We have an UPDATE statement running in a large J2EE application that is getting blocked by some other statement. The update is using the primary key, so it should affect only one row. The isolation ...
2
votes
1answer
110 views

Resource cost of a blocked query

I'm currently reviewing a case where an SSRS server is experiencing frequent blocking and deadlocking. The reports are not business-critical, and they are frequently killed to alleviate the blocking. ...
0
votes
0answers
515 views

LCK_M_SCH_M Blocking

I have 2 stored procedures (one vendor, one third party) that are constantly (monthly) stepping on each other. They both (for some reason) acquire a schema lock when all they are doing is updating ...
0
votes
0answers
82 views

How can I obtain concurrent deletes for this table?

We have a program that is currently doing something to the effect of: SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN TRANSACTION UPDATE [Source].[dbo].[SourceTable] SET [ProcessedOn] = ...
0
votes
0answers
90 views

PostgreSQL: How can I identify the connections which is blocking ALTER TABLE?

I would like to add some fields to a table in all testing and production DBs, all of which are stored in PostgreSQL 9.3 on Amazon RDS. ALTER TABLE users DROP COLUMN IF EXISTS device_language; ALTER ...
1
vote
1answer
187 views

TempDB Query being Blocked

I have been monitoring Blocking issues on our SQL Server database. Very often the below query is running on tempdb and is blocked with LCK_M_S What is this query used for and why does it run ...
4
votes
1answer
414 views

Why is SQL Server's online indexing causing blocking?

My understanding is that online indexing acquires locks at the beginning (Preparation) and end (Final) phases. However, we see a lot of DML queries blocked during the Build phase. A sampling of the ...
0
votes
1answer
46 views

Getting null block_st.text when querying for blocks

We are using the blocking query from this article which queries sys.dm_tran_locks. http://simplesqlserver.com/2013/05/21/blocking-capturing-and-monitoring/ The issue we've ran into is that often ...
1
vote
1answer
4k views

why a session becomes a head blocker

I am troubleshooting a problem that seems to be random The problem is sometimes an application (dynamics SL) is freezing When the application is freezing , I notice there is a blocked by in SQL ...
0
votes
1answer
1k views

Alter table add column query blocking a table or taking so much time

I'm trying to add a new column to an existing table but it takes so much time or the query is blocking the table, I'm really confused about what's happening exactly. ALTER TABLE MYTABLE1 ADD ...
4
votes
1answer
489 views

How can a blocking_session_id be negative?

I have a case where a query I'm running is taking a long time. When I checked sys.dm_exec_requests the blocking_session_id is a negative value, -2 to be specific. The wait is a LCK_M_X and the ...
3
votes
1answer
265 views

Why am I getting blocked when I call OBJECT_NAME(object_id, database_id) from sys.dm_tran_locks?

I'm working on a query to tell what objects have locks from a given session and I'm running into some blocking issues. Here is my basic query: SELECT request_session_id AS session_id, ...
14
votes
1answer
598 views

Excessive compile blocking on sp_procedure_params_90_rowset

A resurgence of this questionon MSDN: Blocked-process-report: what is this waitresource "OBJECT: 32767:124607697:0 [COMPILE]" I have caught these statements in Profiler. They all have durations over ...
4
votes
2answers
1k views

Why did my UPDATE statement not complete?

Running SQL Server 2008; Mgmt Studio 2008. I am working with a MS SQL job that we have created for our MS CRM 4.0 implementation. For anyone familiar with CRM, this job purges the aSyncOperationBase ...