I have a problem with locking and not timing out in one of my Oracle databases. This session locks occur on certain transactions that come from Oracle Forms users. For instance, when some user requests to make a change on a locked object the transaction waits for the locked object to be released, but it waits indefinetely until killed by alter session. I've tried the same in my testing database and it does kill the locked session after 60s, so I thought it has to be a configuration issue. I've checked the v$parameters DISTRIBUTED_LOCK_TIMEOUT and it is set to 60s in both databases. Does anyone know where is the "locking session/transaction timeout" set? Any help is greatly appreciated.

I have a Delete Statement which fails with Below Exception com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction It waits for 50 Seconds for Lock and gives Up, I wanted to know which all SQL Statements can lock a table. LOCK WAIT 6017 lock struct(s), heap size 376, 137376 row lock(s), undo log entries 22011 MySQL thread id 8242, OS thread handle 0x2b019e749700, query id 13811010 10.103.89.37 administrator updating DELETE from CONFIGSTOREQASTAGINGREL.EVENT_DETAILS where UPDATE_DATE < 20191013 AND COMPONENT_NAME = 'health' ------- TRX HAS BEEN WAITING 25 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 194 page no 9414 n bits 17 index GEN_CLUST_INDEX of table CONFIGSTOREQASTAGINGREL.EVENT_DETAILS trx id 38176269 lock_mode X waiting Record lock, heap no 17 PHYSICAL RECORD: n_fields 14; compact format; info bits 0 0: len=6; bufptr=0x2b01d5f29030; hex= 0000001ef5fb; asc ;; 1: len=6; bufptr=0x2b01d5f29036; hex= 0000024674c5; asc Ft ;; 2: len=7; bufptr=0x2b01d5f2903c; hex= af000004900110; asc ;; 3: len=30; bufptr=0x2b01d5f29043; hex= 333532613463313736333863613932393935336262653631646135306137; asc 352a4c17638ca929953bbe61da50a7; (total 32 bytes); 4: len=30; bufptr=0x2b01d5f29063; hex= 316663346166393133353164633731393062363833393134386335326535; asc 1fc4af91351dc7190b6839148c52e5; (total 32 bytes); 5: len=6; bufptr=0x2b01d5f29083; hex= 6865616c7468; asc health;; 6: len=30; bufptr=0x2b01d5f29089; hex= 36436c74773144627644656948474472756e774d4f362020202020202020; asc 6Cltw1DbvDeiHGDrunwMO6 ; (total 32 bytes); 7: len=4; bufptr=0x2b01d5f290a9; hex= 81341726; asc 4 &;; 8: len=4; bufptr=0x2b01d5f290ad; hex= 800003aa; asc ;; 9: len=16; bufptr=0x2b01d5f290b1; hex= 4f494d2d534552564943452020202020; asc OIM-SERVICE ;; 10: len=4; bufptr=0x2b01d5f290c1; hex= 80000000; asc ;; 11: len=0; bufptr=0x2b01d5f290c5; hex= ; asc ;; 12: len=30; bufptr=0x2b01d5f290c5; hex= 436f6e6e656374697669747920746f20536563757265204167656e742069; asc Connectivity to Secure Agent i; (total 168 bytes); 13: len=4; bufptr=0x2b01d5f2916d; hex= 5da441f8; asc ] A ;; I wanted to know what is Record Lock and when does that gets locked.I would very thankful if someone explains what is the meaning of above statements i got from SHOW INNODB ENGINE STATUS

We have recently migrated our cassandra DB from one DC to another DC, and with this migration we upgraded the cassandra version too from 4.0.9 to 4.1.4. However, after the migration we are experiencing frequent timeouts with the nodes in the cluster. whenever the issue occurs the logs fill with the below errors and node is going into un responsive state. In the logs we found the below errors: - Operation timed out - received only 0 responses." while executing SELECT permissions FROM system_auth.role_permissions - 7000-SMALL_MESSAGES-49749d9d dropping message of type READ_REQ whose timeout expired before reaching the network - 7000-SMALL_MESSAGES-49749d9d dropping message of type MUTATION_REQ whose timeout expired before reaching the network But when we check the cassandra service status, it is showing active. Whenever this issue occurs, as a work around we are restarting the effected node and the issue is getting resolved. The mentioned timeout is occurring very randomly among all the nodes and at random timings. below is the DB config: Single cluster with 7 nodes total RAM - 128 GB allocated heap 64 GB CPU - 16core Please help us solving this issue.

This one is bizarre because the database itself is never under any significant load...logs show CPU usage averaging around 5-10%, Memory usage is low, 10-20%, there's only ever 20 or so connections max per day usually, there's no errors in the logs, no IO blocking or anything obvious that could account for it. Moreover, this is not an issue where it is failing on a big expensive query or ETL or something, it's random and it occurs during the initial connection phase from the client. We have a job that runs nightly at 2AM, it alternates days working vs failing. So Mon-Wed-Fri this week it ran fine but Sun-Tue-Thu it failed. And it fails on the very first query in the job where it is simply doing a SELECT * FROM Company; (where the Company table has about 500 rows), every 2nd day it times out, every other day it works fine. There is a nightly backup setup in RDS but that runs at 6AM so it's not related to that I also periodically get timeouts myself while testing/debugging during the day, its random and if I just wait a minute and try again there's no issue. Its a really insidious bug that I can't make heads or tails of. Any suggestions or ideas what might be going on? This is MySql 8.0.35 on AWS RDS on an db.t4g.medium instance (2 vCPUs, 4GB RAM, Network up to 2085 Mbps)

We are facing an issue in production environment using PostgreSQL v14, we have a function which is selecting datas from a table then aggregate these data and then insert these aggregated data in another table. At the end of the function we are updating a table with the time of execution of this function (thus we know that the function went to the end of the process). We are executing this function or from dbeaver or from a java client The problem : When having a large set of data to aggregate, it can take more than 1 hours, we noticed that if that take more than 1 hours, the query seems to disappear from the pg_stat_activity and we dont get any return on the client side (dbeaver or java) and nothing is commited . If the set of data is small (taking less than one hour), then we see the query becoming "idle" and we can see that our table has been updated. We speaked with the database administrator and he said that there is no timeout configured on the database and that we should check the network (all these activites are done under a private network of our client using a VPN). If anyone already had a similar issue and can help it would be very helpful :). Thanks.

Following error comes while executing an SQL query. Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. This happens after 30 seconds, even though I have set the Command Timeout property to 300 seconds. Here’s what I’ve already verified: The connection is working fine. I've checked the remote query timeout, and it's set to 0 (unlimited). There are no packet drops or network issues. The query is optimized, properly indexed, and runs in seconds directly on SQL Server. There are no deadlocks, blocking processes, or transactions affecting the command. Connection pooling is not in use, and SQL logs show no issues. Any suggestions or insights on what could be causing this issue would be greatly appreciated!

LOCK_ESCALATION= AUTO. In one of our Data warehousing product we use partitioned tables. They are partitioned based on an integer column. We have implemented partitioning here so we can easily switch data across tables. Environment Details:

SQL Version: SQL Server 2016
Table: FACT
Approx Size: 100-500 GB
No of Partitions: 100
Partition Column: RunID (int)

Activity: Parallel ETL Processing
Applications Involved in ETL: SSIS package We have a SSIS Package which is written to process in parallel for 3 RunIDs (3 Partitions). These 3 runs are few minutes/hours apart from each other. We have faced a situation where we observe a time-out when below condition occurs. Below is the observation: Run 3 (Truncate seems to be blocked for an hour) Run 2 (Bulk Insert, this is timed-out) We Suspect either Run2 or Run1 is conflicting with Run3, eventually causing a time-out for Run2. | Run 3 | Run 2 | | -------- | -------- | | Truncate with partition | Bulk insert | | partion 3 | Partion 2 | To avoid this conflict, we are considering to set the Lock Escalation of this partitioned table to AUTO. So, the locks would remain at HoBT level. While we are processing concurrently for 3 different Partitions. https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql?view=sql-server-ver16#set--lock_escalation---auto--table--disable-- Before Implementing, we wanted an opinion to understand the risks associated by using this configuration for a particular table which is only Queried/Processed for one partition at a time. Could this change introduce deadlocks? Since the early adopters of this feature have experienced deadlocks: Paul Randal: https://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-2330-lock-escalation/ Brent Ozar: https://www.brentozar.com/archive/2017/11/partition-level-locks-confusing/

I'm trying to limit the amount of time my MariaDB client will wait when trying to open a connection to the server. All my searches instead find only server-side limitations for timing out stale/inactive connections, nothing client-side. /etc/ssh/ssh_config, for example, has: ConnectTimeout Specifies the timeout (in seconds) used when connecting to the SSH server, instead of using the default system TCP timeout. If I try to use mysqlshow for example: # time mysqlshow && echo success || echo fail mysqlshow: Can't connect to server on 'sql.example.edu' (60) real 1m15.012s user 0m0.005s sys 0m0.007s fail If the client can't connect, I'd like it to timeout sooner than 75 seconds. Is there a knob to control this? ### Update 1 --connect_timeout=N doesn't seem to change anything: # time mariadb-show --connect_timeout=10 mariadb-show: unknown variable 'connect_timeout=10' real 0m0.007s user 0m0.000s sys 0m0.008s # time mariadb --connect_timeout=10 ERROR 2002 (HY000): Can't connect to server on 'sql.example.edu' (60) real 1m15.017s user 0m0.002s sys 0m0.014s Thank you!

We recently migrated our database from SQL Server 2014 to SQL Server 2022 Enterprise Edition. Our compatibility level is set for 160. After migration we observed few stored procedures started timing out frequently. The main symptom we identified is frequent statistics updates happening on certain tables leading to timeouts. We disable Auto Update of statistics on few tables and the problem went away. I need your expert opinion on following: 1. Why we are facing frequent statistics updates in SQL Server 2022 and why this is not observed on all tables. Our database is huge with many big tables. 2. Disabling Auto Update of statistics and doing it manually is the right approach? 3. Should we change Auto Update of statistics to Async? Will it cause any issues in high transactional system? Majority of tables are partitioned. We have disabled Auto Update of statistics on few tables.

I've got a performance degradation without a specific stored procedure being timeout. there are some errors here it is listed from most to least: 1. Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. (97% of the errors) 2. Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached. 3. A connection was successfully established with the server, but then an error occurred during the login process. (provider: TCP Provider, error: 35 - An internal exception was caught) 4. The lock supplied is invalid. Either the lock expired, or the message has already been removed from the queue. For more information please see https://aka.ms/ServiceBusExceptions . Reference:0a6d4e92-5df9-461e-9f02-80a863f6f975, . For troubleshooting information, see https://aka.ms/azsdk/net/servicebus/exceptions/troubleshoot . My enviorment is : SQL azure PAAS (P11) There are no high DTU for this database (about 40%) at max. and there is few timeout for 1 stored procedure (from the query store) Most of the time when i have a timeout issue i have a specific problematic stored procedure and this is not the case. is it a application error (the max pool issue) i've read somewhere that in the connection pool you can use the MAX thread to keep a connection idle. how can i troubleshoot this?

We are having a couple of issues preventing us from going live with an on-prem SQL 2016 Std Ed to Azure SQL Managed Instance Business Critical tier re-platform, and I was wondering if anyone had similar issues and had advice for us. Our issues are: 1. The **#1 show-stopping issue is excessive query plan **initial compilation**** for queries in at least a dozen stored procedures and other queries (40 seconds to 174 seconds...I've even seen examples up to 725 seconds!). This is leading to frequent and random **application timeouts** since most are set to the default 30 seconds. To clarify, this is not excessive re-compilations, but a very high CompileTime in the query plan. The queries and info in https://erikdarling.com/are-long-compile-times-bringing-you-down/ have been very helpful to identify these queries (I've read https://littlekendra.com/2024/03/05/long-compilers-who-time-out-not-in-query-store/ as well). We do see some compilation timeouts as well: https://www.brentozar.com/blitzcache/compilation-timeout/ . We are attempting to tune these queries and reduce their complexity as we've been told that should reduce CompileTime. We have also tried upgrading the AZ MI tier to Business Critical, adding vCores, and tuning instance and database settings for months. We have been working a Sev B case with Microsoft for several weeks now and received some tips, but still no resolution. Level 3 Support recommended making sure the compilation timeout queries do not have forced plans, because plan forcing allows the SQL Optimizer to spend 3x the duration than normal to compile, which increases the likelihood of timeout. The only thing that was forcing plans is Automatic tuning which applies to MI for only the FORCE_LAST_GOOD_PLAN option per https://learn.microsoft.com/en-us/sql/relational-databases/automatic-tuning/automatic-tuning?view=sql-server-ver16 and https://learn.microsoft.com/en-us/azure/azure-sql/database/automatic-tuning-overview?view=azuresql . We turned off FORCE_LAST_GOOD_PLAN per their recommendation and that helped some of the high CompileTimes but not all of them. We are not forcing plans with the plan guide (USE PLAN N'') or manual forcing in the Query Store. They recommended tuning queries to reduce their complexity and running a DUMP if turning off FORCE_LAST_GOOD_PLAN doesn't work. I'm hoping to schedule a working session with their performance team to troubleshoot these issues. **Update:** During our call on 5/28 with Level 3 Support, we noticed that even some trivial queries like SELECT COUNT(*) FROM Table were using OptimizationLevel = FULL instead of TRIVIAL. Level 3 Support said there weren't any settings we could change to affect that, but would mention it to the Product Team. 2. Our **partition maintenance job** which does ALTER PARTITION SPLIT **runs a lot longer (days to weeks)** in MI BusCrit compared to the same code in SQL 2016 Std Ed. It **also chews up a lot of extra used space in the data (and log) file during processing** which doesn't happen in SQL 2016, like a database that is 52 GB grew to 588 GB! while the job was running. The bloating (and performance?) may be partially related to Accelerated Database Recovery (ADR) since that is in SQL 2019+ and AZ MI: https://dba.stackexchange.com/questions/265446/table-size-in-azure-sql-managed-instance-vs-on-premise-sql-server (see also https://learn.microsoft.com/en-us/sql/relational-databases/accelerated-database-recovery-concepts?view=sql-server-ver16) . We are reviewing the algorithm we inherited as it appears to not follow the best practice in https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-partition-function-transact-sql?view=sql-server-ver16 "Always keep empty partitions at both ends of the partition range. Keep the partitions at both ends to guarantee that the partition split and the partition merge don't incur any data movement. The partition split occurs at the beginning and the partition merge occurs at the end. Avoid splitting or merging populated partitions. Splitting or merging populated partitions can be inefficient. They can be inefficient because the split or merge may cause as much as four times more log generation, and may also cause severe locking." But we're puzzled why the same code runs so much longer and excessively bloats the data file in MI when it didn't in SQL 2016 Std Ed. Another recommendation we've considered is to move to Azure VMs with SQL, but this will add months to our re-platform launch to set up, test, and operationalize this environment as we don't have any Azure VMs now. We'll also have to operationalize patching, backups, etc. I appreciate your help! Mike

I have a system where many users connect to a database SQL Server through an application, everything works as it should, but sometimes, very rarely, an error occurs. > Message: A network or instance-specific error occurred while > establishing a connection to SQL Server.The server was not found or > was not accessible.Verify that the instance name is correct and that > SQL Server is configured to allow remote connections.(Provider: TCP > Provider, Error: 0 - Operation timed out.) The error is not widespread, it occurs randomly once or twice a month for one of any users, after the error occurs the user tries to connect to the database again and everything works, while all other sessions work, sessions are not disconnected for working users. I can’t catch and understand the reason for the error, please tell me who has had this happen. I thought that there were problems with the network, I configured tcping on ports 1433 and 1434 from client machines to SQL Server, but I did not find any losses, I read that people write about such problems if there is a lack of RAM, but my server is powerful and there seem to be no memory drawdowns .

I want to stop all the query that exceeds the given execution timeout value of an instance in SSMS. I tried to set the execution timeout in SSMS using multiple methods. Initially, I tried changing the query execution time through the tools option, as described on the website below. Link Subsequently, I tried a second method from the link. In the tools design settings, we can configure values for the execution timeout in seconds. Link2 However, these methods didn't work even after restarting SSMS. I have also tried to set the execution timeout under the connection properties in the options tab. Unfortunately, this method also failed. The below link says that this setting has some issue. Link3 I want to know how this Execution timeout Setting works. What are the other ways to set this execution timeout in SSMS? My aim is to stop all the long running queries that I will be executing in my SSMS. This setting must work for all the sessions of my SSMS.

Is it possible to specify pgbouncer reserve_pool_timeout in milliseconds, I have tried specifying it with '500 ms' but throwing an error and then tried specifying 0.5, this time no error but unable to find out it's really working or just disabled the timeout but reserve_pool are being utilised.

What happens when a client timeout occurs during the execution of a stored procedure that raises XACT_ABORT and wraps its body in TRY/CATCH blocks? I am looking at a bunch of procedures that generally can be converted to an MWE as follows: create or alter procedure ##ptx as begin try set xact_abort on waitfor delay '10:00' end try begin catch declare @xsx int = XACT_STATE() raiserror('XSX=%d', 16, 10, @xsx) with log, nowait end catch I created a test harness that logs into the server as SA, asynchronously calls such procedure, and times out the call after X seconds but earlier than the delay should run out. There are no new entries in the log, so it looks like the execution does not enter the CATCH block when a timeout occurs. Should it? If it should not, then what happens upon the client timeout?

I have SQL running on several servers and I want to create DB links between the main server "A" and several other "child" servers (each one with their own SQL instance). I've successfully created a DB link with all of these "child" servers but 1 (Lets call it server "B"). When I try to register a DB link to "B" server using the SSMS GUI I get back below error: Named Pipes Provider: Could not open a connection to SQL Server OLE DB provider "SQLNCLI11" for linked server "B" returned message "Login timeout expired". OLE DB provider "SQLNCLI11" for linked server "B" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.". However, I can open a connection with the SSMS on the "A" server to the "B" server using the exact same credentials and IP, even run queries without any issues. All of the server have the same DB layout, same VLAN. Things I've tried: - Disabled the firewall on the "B" server. - Use the sa account. - Created a different login with db_owner authority. But still I get that error message. Any ideas on why a "direct connection" from the "A" server SSMS works without any issues, but setting up a new Linked Server with the same IP and credentials have a Login timeout expired timeout?

I have created an SSIS package to run DBCC CHECKDB on all the databases on the server and send error messages by email. But for several of the databases, I get an exception running my script: > Timeout expired. > The timeout period elapsed prior to completion of the operation or the server is not responding. The only timeout I can find in the project/package is set to 0, which I presume means no timeout. DBCC CHECKDB runs from SSMS just fine.

I am quite new to OPC server/client. I have a service application serves as an OPC client, and trying to connect to OPC HDA server to data reading. There is a list of tags which their value at a particular timestamp are to be read from OPC HDA server. My service will loop through the list of the tags. For each tag, it will do the following: - connect to OPC HDA server based on the URL link given - Read the tag attributes from HDA server - disconnect from OPC HDA server My code is as follows: Public Shared Function GetOPCTagInfo(m_Tstamp As Object, m_tagname As Object, TagItemid As Object, serverurl As Object) On Error Resume Next Dim d(6) As String 'an array to store tag attributes read from OPC server Dim KnownURL As URL = New URL KnownURL = New URL(serverurl) Dim server As Opc.Server = SampleClient.Factory.GetServerForURL(KnownURL) myserver = server myserver.Connect() If myserver.IsConnected Then d = newgetopcinfo(m_Tstamp, TagItemid, n) 'a function to read the item attributes from OPC server' myserver.Disconnect() Return d Else Return {"Fail to connect to server"} End If End Function Currently I have encountered the issue that, when my service tries to connect to the HDA server in order to read the data for the tags, it got hang. I suspect that when it tries to connect to HDA server (myserver.Connect() ), it does not receive any response from HDA server and end up in infinite waiting. Is there anyway to break this infinite waiting, so that at least it can go to next tag to repeat the process, or try to re-connect?

We have been experiencing (perceived) issues with the write performance of our MS SQL Server for some time. As I believe it might be on the client side, I am looking for evidence to refute the accusations against the SQL Server. Here is the situation: A NodeRed Flow collects data from a machine and writes it to an SQL Server in the end. If writing is not possible, the data is buffered in Redis until it can be written again. As mentioned, for some time now, the buffers have been filling up repeatedly, going from a normal workload of about 50 messages to over 1000 - 30000 messages. After several minutes (10 - 60), the buffers are emptied again. The only error message received is a timeout within 2 seconds. The writing node is more or less a black box for us. What we have done so far: When the problem occurred, we set up monitoring of the buffers. Additionally, at the time of increasing buffers, we could look at the SQL Server and found nothing unusual in the Resource Monitor of the Windows Server... CPU, Disk, and Network were at a normal operating level. Only when the buffers were emptied, did we see a change on the SQL Server. On the monitoring (Telegraf + Grafana) of the SQL Server, during that time, you can see a Write Latency of < 1 second on the database. A restart of the SQL Server was also performed. However, this did not affect the full buffers; they neither increased nor decreased. As the database manager, I am now seeking evidence that the SQL Server is not responsible for the timeout in the data flow. In addition to the option of comparing the monitoring, I would like to provide proof that during the time when the buffers are filling up, the client is not sending any requests to the SQL Server. Are there any possibilities to achieve this with the existing resources? Thank you and best regards, Martin

The authentication timeouts and slowness happen for both local SQL logins and Windows authentication using AD. Rebooting "masks" the problem for a while, but after 6-8 hours, we see them again. The whole time, I see high numbers of PREEMPTIVE_OS_AUTHENTICATIONOPS waits. It can't be strictly an AD or routing issue because authentication happens instantly during RDP login using a domain account, even after many hours or days of uptime. telnet to the relevant ports on the DC instantly return success, so the ports are open. I've gone through everything on this page, to no avail: https://samzsimplesql.wordpress.com/2015/09/01/troubleshooting-connectivity-issues-timeout-error-258-unable-to-complete-login-process-due-to-delay-in-prelogin-response-pre-login-handshake-failed-2/ - SQL Server: 2008 R2 EE 10.50.6560.0 - Windows: 2008 Ent - AD: 2012 R2 (Yes, I know those are ancient versions, but have no control over that.) What else can I look at? EDIT: There are about 1000 connections (it varies throughout the day). "All" (except for the few from me) logins are from the same domain account. The problem started last Monday. There are no errors in the SQL Server error log. This is the only (and there were a lot of them!) message in the DB server's event viewer: > The description for Event ID 17052 from source MSSQLSERVER cannot be > found. Either the component that raises this event is not installed on > your local computer or the installation is corrupted. You can install > or repair the component on the local computer. > > If the event originated on another computer, the display information > had to be saved with the event. > > The following information was included with the event: > > Severity: 16 Error:258, OS: 258 [Microsoft][SQL Server Native Client > 10.0]Shared Memory Provider: Timeout error .