Friday, April 17, 2009

SharePoint and SQL Agent Jobs

When you create an SSP, SharePoint automatically creates a SQL Agent job that deletes expired user sessions. However, if you are rebuilding your SSP, like I had to do recently, SharePoint will not delete the jobs it created for your old SSP. The jobs are set to run once per minute, so if one is mis-configured your SQL logs will be full of errors:
...for an incorrect target database:
[298] SQLServer Error: 18456, Login failed for user 'DOMAIN\MOSSSQLService'. [SQLSTATE 28000]
-or-
[298] SQLServer Error: 4060, Cannot open database "SharedServices_Provider_SSPPrimary" requested by the login. The login failed. [SQLSTATE 42000]

...for an incorrect user account
[298] SQLServer Error: 15404, Could not obtain information about Windows NT group/user 'DOMAIN\MOSSWrongUser', error code 0x5. [SQLSTATE 42000] (ConnIsLoginSysAdmin)
The broken jobs probably point to databases that no longer exist, so make sure the job's target database is your (new) SSP database, and that the job executes as the [domain]\SQLService user you specified during the MOSS install. You can disable or delete any old jobs. If the job hasn't been running for a while, you have probably noticed that SQL gobbles up all the RAM it has access to. Yum.