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

learn more… | top users | synonyms

3
votes
1answer
40 views

Unknown System SPID blocking user SPIDs

We are performing a migration of 3rd party data and it is hanging. When we investigate we see SPID 38 (a system/background SPID) is the blocking user SPIDs trying to do the migration. Unfortunately, ...
2
votes
1answer
65 views

Hold exclusive lock on a whole DB

As I explained in Set a filegroup to readonly, I wanna set some tables readonly, to have better performance. These tables are only written by an ETL that runs daily, then they are only read. ...
2
votes
2answers
61 views

Database “frozen” on ALTER TABLE

Our production environment just froze* this morning for a while when altering a table, adding a column actually. Offending SQL:ALTER TABLE cliente ADD COLUMN topicos character varying(20)[]; * ...
1
vote
2answers
61 views

SOS_SCHEDULER_YIELD - Prevent a Proc from giving up the Processor Time

I've got a process which about once a month at times unrelated (or as far as we can tell) goes into SOS_SCHEDULER_YIELD State. This process blocks our main data entry pipeline and brings the entire ...
1
vote
1answer
10 views

What happens during RCSI while tempdb is being populated?

Our database/application utilizes Read Committed Snapshot Isolation so that readers are not blocked by writers, and writers are not blocked by readers. Of course that means when a row/page/table lock ...
0
votes
1answer
28 views

Multiple Replication Type Publications on Single DB

I have 2x critical DB's which until recently; I was reporting on as well as using in production. DB1 is a Vendor DB and DB2 an in-house DB used in conjunction with DB1 for custom tables and views (as ...
3
votes
1answer
70 views

Unpredicatable single insert performance on SQL Server table

I have inherited a system that sometimes exhibits extremely poor single record insert performance. The table is something like 8 million rows, so is relatively large but not exceptionally so. The ...
4
votes
1answer
66 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 ...
6
votes
1answer
101 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
52 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
142 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
35 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
25 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
101 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
245 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
114 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
62 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
526 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
34 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
183 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
633 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
177 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 ...
3
votes
1answer
175 views

how to get the information about sleeping processes that are causing blocking?

I have a procedure that allows me to see what is currently running in my sql server. This procedure does not use sys.sysprocess because it has been deprecated. However, I am struggling to find ...
2
votes
2answers
230 views

how to find the T-SQL of a sleeping session that is blocking some other process?

I have a stored procedure called sp_radhe that I put on my servers and it has been helping me to "see" what is happening internally. here is the code of this stored procedure: USE [master] GO SET ...
1
vote
2answers
82 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 ...
5
votes
2answers
172 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
270 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
28 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 ...
2
votes
2answers
148 views

'sp_releaseschemalock' causing me a blocking issue?

why 'sp_releaseschemalock' causing me a blocking issue? what proccess that must execute sp_releaseschemalock or what function of sp_releaseschemalock? I have searched various forums or blogs about ...
18
votes
5answers
1k 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
79 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
2k 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
241 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
609 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
80 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
124 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
26 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
103 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
350 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
185 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
108 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
405 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
179 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
115 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
638 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
92 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
97 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
220 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
487 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 ...