We had to re-create a service application and this involved removing the old database. However, we begin to notice login failed errors coming up on the SQL database server instance of our SharePoint farm. The errors were appearing in the SQL Agent logs and it was flooding the logs with a new entry every minute.
SQLServer Error: 18456, Login failed for ‘user’. [SQLState 28000] SQL Agent
SQLServer Error: 4060, Cannot open database “database” requested by the login. The login failed[ SQLState 42000]
It turns out there is a SQL Server Agent Job for that particular database to ‘Delete Expired Sessions’.
You can locate the job by logging in to your database server using SQL Management Studio.
Expand the SQL Server Agent group -> Jobs
The job name should be called “database_Job_DeletedExpiredSessions”, replacing the word database with the name of the offending database.
Right click on the job and click on ‘Disable’, monitor and see if it resolves the problem. If it does, you can delete the job.