SQL Server: tracer un job en erreur depuis un agent SQL SERVER distant.
Par Draggi le vendredi, novembre 16 2012, 13:42 - SQL - Lien permanent
Voici l'erreur remonté par le serveur MASTER:
11/09/2012 23:54:30,,Error,[298] SQLServer Error: 4060<c/> Cannot open database "MABASE" requested by the login. The login failed. [SQLSTATE 42000]
11/09/2012 23:54:30,,Error,[298] SQLServer Error: 18452<c/> Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. [SQLSTATE 28000]
11/09/2012 23:54:30,,Error,[298] SQLServer Error: 18456<c/> Login failed for user 'MONSERVEUR\mssql-serv-agent'. [SQLSTATE 28000]
Ouvrez le Profiler et lancer une trace sur les sessions en erreurs, il vous remonte alors l'id du job en erreur:
Audit Login FailedLogin failed. The login is from an untrusted domain and cannot be used with Windows authentication. [CLIENT: 000.000.000.000]SQLAgent - TSQL JobStep (Job 0xBBEF866B1CC05D4BB8D80AFAE9D4BE06 : Step 1 9820932012-11-14 19:54:31.043
Si on recherhe le JOB id on se rends compte qu'il se trouve sur le serveur SLAVE. Vous pouvez vérifier cela en exécutant la commande suivante sur les deux serveurs.
EXEC xp_sqlagent_enum_jobs 1, ''
create table #enum_job ( Job_ID uniqueidentifier, Last_Run_Date int, Last_Run_Time int, Next_Run_Date int, Next_Run_Time int, Next_Run_Schedule_ID int, Requested_To_Run int, Request_Source int, Request_Source_ID varchar(100), Running int, Current_Step int, Current_Retry_Attempt int, State int ) insert into #enum_job exec master.dbo.xp_sqlagent_enum_jobs 1,garbage select * from #enum_jobdrop table #enum_jobSELECT sj.name AS [JobName], [enabled], sj.category_id,sc.name AS [CategoryName]FROM msdb.dbo.sysjobs AS sjINNER JOIN msdb.dbo.syscategories AS scON sj.category_id = sc.category_idORDER BY sj.name;