SharePoint: deleting a SSP leaves a running SQL agent job

Hello,

On one of MS SQL Server database backend machines, I was getting a lot of windows event log entries stating that there was a problem for our SQL Server account with accessing one of the databases:

Login failed for user ‘DOMAIN\sqluser’. Reason: Failed to open the explicitly specified database. [CLIENT: x.x.x.x]

Investigating the corresponding SQL Server instance logs, further details of the issue followed:

[298] SQLServer Error: 18456, Login failed for user ‘DOMAIN\sqluser’. [SQLSTATE 28000]

[298] SQLServer Error: 4060, Cannot open database "SSP_XYZ" requested by the login. The login failed. [SQLSTATE 42000]

First idea was of course checking the permissions of the user within that database, but then there came the weird thing – a database with such name did not exist. Another approach was that maybe some old web application has been forgotten and still uses explicitly the DB name (e.g. in web.config). Not the cause either.

Finally, since the database name contained ‘SSP’, it had most probably something to do with a Shared Services Provider database. The current one we have has a different name, so the name occurring in the error logs referred to a non-existing SSP. We were able to find out that such SSP has been created and deleted a while ago. The corresponding database has been also removed from the SQL-Server, but one oddment remained there: a SQL Agent job for deleting expired sessions. The agent tried to connect to that DB every minute, and encountered the error mentioned above.

You can find the jobs either directly in the table ‘msdb.dbo.sysjobs’, or within the object explorer, under the “SQL Server Agent” node:

sql server agent jobs

Deleting or disabling the job responsible for connecting to the non-existing SSP’s database solves the problem.

Hope this helps,
Łukasz

~ by Łukasz on 7 October 2011.

2 Responses to “SharePoint: deleting a SSP leaves a running SQL agent job”

  1. Very helpfull, thanks

  2. Thanks…i am going to try this early in the morning with my dba, as I do not have access to do so🙂

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: