Hello experts,
I'm seeing a strange issue. Yesterday I noticed that I was getting this error when I tried to expand the databases on a dev server:
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
Lock request time out period exceeded. (Microsoft SQL Server, Error: 1222)
I ended up finding a post that suggesting restarting the server. I did that, and it seemed to work. But the issue came back today, and repeatedly restarting the server is clearly not a real solution.
I also noticed an open transaction gets stuck (as open) when I try to right-click and start SQL Server Agent. I found that based on another post I read that suggested using DBCC OPENTRAN() to check.
The SQL Server Agent service is listed as Running in Configuration Manager. But in SSMS, it has the red X and the status '(Agent XPs disabled)'.
When I try to enable Agent XPs using sp_configure - or even just check the current state of the setting - the whole open transaction and lock timeout happens again.
-- Hangs during this command
use master
go
exec sp_configure 'Show advanced options',1
Go
reconfigure with override
go
Does anyone know how I can troubleshoot this issue further? Thanks for any help.
EDIT:
I also see this error in the Application Event log:
SQLServerAgent could not be started (reason: Shared Memory Provider: No process is on the other end of the pipe. [SQLSTATE 08S01] (Error 233) Communication link failure [SQLSTATE 08S01] (Error 233) Communication link failure [SQLSTATE 08S01] (Error 16389)).
-- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
July 12, 2023 at 4:55 pm
I experienced the same few weeks ago using the latest SSMS 2019 version.
I had multiple SSMS versions on my machine, I tried the same with SSMS 2016 and it worked.
For the shared memory, Is it enabled in first place?
=======================================================================
This isn't a client problem it is a server side problem. It sounds like something is holding a long running schema lock.
Big SELECT INTO statements are a common cause of this.
do something to create a block and then query for blocking sessions to find out what it is.
July 12, 2023 at 5:41 pm
Thanks to you both! Yes btw, Shared Memory is enabled, as are Named Pipes and TCP/IP.
Another reboot worked this time. But I'll trace for blocking to see what the culprit is and/or to see if the dev server needs more resources to prevent or minimize blocking.
Thanks again.
-- webrunner
Edited to remove the part about system SPIDs, as I believe those cannot be killed with the KILL command. So probably was the system SPIDs (such as sp_configure) being blocked by some user process.
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
July 13, 2023 at 3:33 am
I am not sure if this will help from another article I saw on internet. Personally, I have seen this happened in large platforms. I will definitely check your infrastructure as well:
In certain server environments, the TCP Chimney offload process either fails or causes very slow performance. The failure/delays may be caused by: Old/incompatible network card (NIC) software driver. faulty/incompatible network card (NIC) hardware. I will add firewall issues to this note. Look for hard drive/memory errors in the event viewer.
Application side, Pinal Dave has a query for looking for missing indexes. This could be useful. I would increase the auto growth for the data and log files.
DBASupport
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply