-
Title
Lock request timeout error running a collection found in a SQL Server agent log -
Description
A SQL Server collection fails due to a LOCK REQUEST TIME OUT PERIOD EXCEEDED error.
Collection Status. DBSS_CLR_Assemblies Failed with the exception: Failed to execute query on instance [INSTANCENAME], user account [DOMAIN\USER]. Reason : [[Foglight][SQLServer JDBC Driver][SQLServer]Lock request time out period exceeded.
Increasing the collection timeout does not affect this error.
-
Cause
The lock timeout SQL Server default is -1 which means "no timeout", however the SQL Server agent resets this when the connection is dropped as per SET LOCK_TIMEOUT.
At the beginning of a connection, this setting has a value of -1. After it is changed, the new setting stays in effect for the remainder of the connection. Using an infinite isn’t a good to use because it hides problems that may not be related to the database piece...slow network, dropped connections, etc. Foglight would have no idea of these problems if you tell the computer to wait forever for the command to process.
Increasing the lock timeout could affect another connection by forcing them to wait longer for the lock to release.
EXAMPLEIn one example, there was blocking on master.sys.sysdbreg.clst from the .Net SQLClient Data Provider that took roughly 30 seconds for each task. On every connection establishment, the Lock Timeout is set to 10 seconds - the SQL Server agent executes: "SET LOCK_TIMEOUT 10000" (10 seconds). The create database statement locks the sysdbreg, which causes the issue with sysdatabases used by the query. The database agent collection often takes ten of more minutes to complete due to the frequent locking. -
Resolution
WORKAROUND *Specific to lock timeouts due to database creations*
Running a DBCC CheckDB and reindex or rebuild the indexes on the master DB more often may help the issue because of all the database creations.
STATUS
It is not advisable for the database agent to hold a database agent collection for 12 or 15 minutes. This is not tested and can have unknown consequences on the connections and memory usage by FglAM.
The log files detail that every few hours there is an incident of lock timeout; there is no failure of any critical collection and all data is eventually collected. The collections that do fail involve CLR assemlies and the schema object change tracking. These Lock timeout messages can be safely ignored.An enhancement SSFOG-52 was added to the SQL Server 5.7.5.35 and higher cartridges to include a hidden parameter called "monLockTimeout" that can be used to change the number for the LOCK_TIMEOUT parameter on each new connection that is opened.It might be best increasing it in small increments (for example from 10000 to 20000) to test the effects.The following script below can be run to increase the monitored lock timeout value. Please update the agentname value and replace HOSTNAME-INSTANCENAME to include the agent name being adjusted.def agentName = "HOSTNAME-INSTANCE";def monitoredLockTimeout = "60000"; // find the agent def agents = server.get("AgentService").findByName(agentName); if (agents.size() == 0 ) { return String.format("Failed to find agent: %s", agentName); } if (agents.size() > 1 ) { return String.format("More than 1 agents were found to: %s", agentName); } def agent = agents.get(0); // update agent ASP def configService = server.get("ConfigService"); def primaryAsp = configService.getAgentInstancePrimaryAsp(agent.getAgentNamespace(), agent.getTypeId(), agent.getId()); primaryAsp.setValueByString("monLockTimeout", monitoredLockTimeout); configService.saveConfig(primaryAsp); return String.format("Agent %s ASP successfully updated", agentName);