sql server job failed. Here is the log. what caused the issue and how to fix?

Question

sql server job failed. Here is the log. what caused the issue and how to fix?

bestrongself 1

SQL server agent job failed-- that job runs a stored procedure. And stored Procedure failed in the middle.

Here is the log

2020-11-24 03:00:27.87 spid58 2020-11-24 03:00:27.91 spid58 2020-11-24 03:00:27.91 spid58 2020-11-24 03:00:27.92 spid58 2020-11-24 03:00:27.92 spid58 2020-11-24 03:00:27.92 spid58 2020-11-24 03:00:27.92 spid58 2020-11-24 03:00:27.92 spid58 2020-11-24 03:00:27.92 spid58 2020-11-24 03:00:27.92 spid58 2020-11-24 03:00:27.92 spid58 2020-11-24 03:00:27.93 spid58 2020-11-24 03:00:27.93 spid58 2020-11-24 03:00:27.94 spid58 2020-11-24 03:00:27.94 spid58 2020-11-24 03:00:27.94 spid58 2020-11-24 03:00:27.94 spid58 2020-11-24 03:00:27.94 spid58 2020-11-24 03:00:27.94 spid58 2020-11-24 03:00:27.94 spid58 2020-11-24 03:00:27.94 spid58 2020-11-24 03:00:27.94 spid58 2020-11-24 03:00:27.94 spid58 2020-11-24 03:00:27.94 spid58 2020-11-24 03:00:27.94 spid58 2020-11-24 03:00:27.94 spid58 Setting database option SINGLE_USER to ON for database 'DatabaseA'.
Process ID 55 was killed by hostname ServerA, host process ID 13352.
Process ID 61 was killed by hostname ServerA, host process ID 13352.
Process ID 63 was killed by hostname ServerA, host process ID 13352.
Process ID 64 was killed by hostname ServerA, host process ID 13352.
Process ID 66 was killed by hostname ServerA, host process ID 13352.
Process ID 89 was killed by hostname ServerA, host process ID 13352.
Process ID 90 was killed by hostname ServerA, host process ID 13352.
Process ID 55 was killed by hostname ServerA, host process ID 13352.
Process ID 64 was killed by hostname ServerA, host process ID 13352.
Process ID 66 was killed by hostname ServerA, host process ID 13352.
Process ID 89 was killed by hostname ServerA, host process ID 13352.
Process ID 90 was killed by hostname ServerA, host process ID 13352.
Process ID 55 was killed by hostname ServerA, host process ID 13352.
Process ID 64 was killed by hostname ServerA, host process ID 13352.
Process ID 66 was killed by hostname ServerA, host process ID 13352.
Process ID 89 was killed by hostname ServerA, host process ID 13352.
Process ID 90 was killed by hostname ServerA, host process ID 13352.
Process ID 55 was killed by hostname ServerA, host process ID 13352.
Process ID 64 was killed by hostname ServerA, host process ID 13352.
Process ID 66 was killed by hostname ServerA, host process ID 13352.
Process ID 89 was killed by hostname ServerA, host process ID 13352.
Process ID 90 was killed by hostname ServerA, host process ID 13352.
Process ID 55 was killed by hostname ServerA, host process ID 13352.
Process ID 64 was killed by hostname ServerA, host process ID 13352.
Process ID 66 was killed by hostname ServerA, host process ID 13352.

2020-11-24 03:00:27.99 spid58 Setting database option SINGLE_USER to ON for database 'DatabaseA'.
2020-11-24 03:00:28.05 Logon Login succeeded for user 'domain\user1'. Connection made using Integrated authentication. [CLIENT: 192.168.1.10]
2020-11-24 03:00:28.77 Logon Login succeeded for user 'domain\user1$'. Connection made using Integrated authentication. [CLIENT: <local machine>]
2020-11-24 03:00:29.16 Logon Login succeeded for user 'domain\user1'. Connection made using Integrated authentication. [CLIENT: 192.168.1.10]
2020-11-24 03:00:30.13 Logon Error: 18456, Severity: 14, State: 38.
2020-11-24 03:00:30.13 Logon Login failed for user 'domain\user1'. Reason: Failed to open the explicitly specified database 'DatabaseA'. [CLIENT: 192.168.1.10]

Guoxiong 8,056 Reputation points

2020-11-24T21:17:43.07+00:00

Can you post the message from the View History of that job?
Cris Zhan-MSFT 6,566 Reputation points

2020-11-30T01:31:10.38+00:00

Hi @bestrongself ,
Is there any update on this case?

Guoxiong 8,056 Reputation points

2020-11-24T21:17:43.07+00:00

Can you post the message from the View History of that job?
Cris Zhan-MSFT 6,566 Reputation points

2020-11-30T01:31:10.38+00:00

Hi @bestrongself ,
Is there any update on this case?

Answer 1

Ronen Ariely 14,146 MVP

Good day

For some reason many people that come to the forums think that the people behind the screens have the ability to read minds and see their code. It might be an unknown fact, so don't tell it to anyone, but the truth is that we cannot read your mind in order to read your code and we cannot see your screen unless you provide screenshot 🙃

Please provide the details about the job and you SP and the relation to the server/database and users

in the mean time I recommend to go over Erland's suggestion in this thread regarding the same error message:

https://social.msdn.microsoft.com/Forums/SECURITY/en-US/998d38e9-5b1d-45a5-9acb-a4791db498df/logon-login-failed-for-user-xyz-reason-failed-to-open-the-explicitly-specified-database?forum=sqlsecurity

Erland Sommarskog 72,421 Reputation points MVP

2020-11-24T22:26:03.753+00:00

in the mean time I recommend to go over Erland's suggestion in this thread regarding the same error message:

I find it unlikely, that the error at the end has any relation to the stored procedure that bestrongself was running. If any of the messages above are related to the job failure, I would rather put my bets on of those "process killed" messages.

Apart from that, I can only echo what Ronen and Guoxiong said. You need to provide more details. The job history is a start. What you posted was parts of the SQL Server errorlog, and the failure of a job, normally does not result in any message in this log.
Shashank Singh 6,211 Reputation points

2020-11-25T09:00:03.837+00:00

If I could vote 100 times on this post I could have. This is the problem with users posting here. Unfortunately the rules are slack
Ronen Ariely 14,146 Reputation points MVP

2020-11-25T12:18:36.367+00:00

In many aspects "the rules" are what we (community leaders/supporters) choose

In other systems the un-written rules are very clear. For example, if the OP don't say thanks at the end of the thread then that the OP will NEVR get respond for his next question (there are no points there and no "close" option, but clear discussion forums). In such cases if the OP come back to ask again, then he will get a link to previous thread make sure he understand that when you get something for free the minimum you should do is the say thanks

If all the leaders (INCLUDING MICROSOFT EMPLOYEES) where following the same approach then we could build the rules for the forum, without any need in development or written rules.

It is up to us !

For example, if no one will pursue points and if there is a question without DDL+DML then no one will answer it trying to guess the DDL, then the "rule" that DDL+DML is a must was enforced and become a clear rule in the forum
Shashank Singh 6,211 Reputation points

2020-11-25T12:42:16.387+00:00

Slight disagreement here the rules have to come from MS the forum owner. A question with no proper explanation and lacking basic information should be closed ASAP, I have stopped responding to many OP's since they post some random question and reply 2-3 later some even dont reply. I always feel happy if there is good explanation in the question, I hate one liners.

sql server job failed. Here is the log. what caused the issue and how to fix?

1 answer

It is up to us !

Activity