SQL Agent blocked in “Stopping” state

· Uncategorized
Authors

Some strange behaviour experienced on a newly to be installed server (Win2008R2)

– installed SQL2008R2; connected using local and remote SSMS: everything works fine
– installed oracle client software Oracle Client 11g R2 version 12.2.0.1.0 no errors
– Test connection to an oracle host using an .udl file: everything works fine
– try to setup a linked server to the same oracle host using script:

IF NOT EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N’ora1′)
BEGIN
EXEC master.dbo.sp_addlinkedserver @server = N’ora1′ , @provider=N’OraOLEDB.Oracle’ , @datasrc=N’ora1′
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N’ora1′ , @useself=N’False’ , @locallogin=NULL , @rmtuser=N’#####’ , @rmtpassword=’#####’
END
GO
EXEC master.dbo.sp_MSset_oledb_prop N’OraOLEDB.Oracle’, N’AllowInProcess’, 1
GO

From now on, things went wrong:
– Restart of SQL services using configuration manager is extremely slow (+/- 10min); and even don’t occur without giving an error message; SQL Agent has status Stopping for almost 1 hour now.
– connecting with SSMS seems to hang on expanding the folders in the object explorer, the Disconnect button doesn’t show; but I can right-click the instance name, open a query window and run queries; the @@servername returns correct info.
– connecting using SQLCMD works fine
– SSMS uses almost no CPU or memory resources

Conclusion; because there was no time to investigate this issue in dept, we needed to reboot the OS. Problem solved.

To be continued

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: