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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy