Bonjour,

Je cherche des pistes ou des axes d'analyse pour un problème de réplication qui plante aléatoirement.

Mon infrastructure est assez simple, j'ai un Server SQL 2012 sur une machine virtuelle dont certaines tables sont répliquées sur deux machines avec leur propre instance de SQL Server 2012.
Tout a bien fonctionné pendant des mois, mais depuis 3 semaine j'ai la réplication qui plante sur une des deux machines, toujours la même, toujours dans la deuxième moitie de la semaine.

Lors que je relance la tâche de synchronisation manuellement, la réplication se termine et j'ai bien toutes mes données.

Au niveau des messages d'erreurs, voilà ce que j'ai :
2016-03-17 00:19:58.692 Bulk copying data into table 'XL_JourFerie_Pdv'
2016-03-17 00:19:58.708 Bulk copied data into table 'XL_JourFerie_Pdv' (624 rows)
2016-03-17 00:19:58.724 Bulk copying data into table 'XL_JourFerie_Pdv'
2016-03-17 00:19:58.755 Bulk copied data into table 'XL_JourFerie_Pdv' (4005 rows)
2016-03-17 00:19:58.864 <stats state="2" fetch="0" wait="1178" cmds="1122" callstogetreplcmds="1"><sincelaststats elapsedtime="1178" fetch="0" wait="1178" cmds="1122" cmdspersec="0.000000"/><message>Raised events that occur when an agent's reader thread waits longer than the agent's -messageinterval time. (By default, the time is 60 seconds.) If you notice State 2 events that are recorded for an agent, this indicates that the agent is taking a long time to write changes to the destination.</message></stats>
2016-03-17 00:30:46.241 Delivering replicated transactions
2016-03-17 00:30:46.335 <stats state="2" fetch="0" wait="1825" cmds="1248" callstogetreplcmds="1"><sincelaststats elapsedtime="647" fetch="0" wait="647" cmds="126" cmdspersec="0.000000"/><message>Raised events that occur when an agent's reader thread waits longer than the agent's -messageinterval time. (By default, the time is 60 seconds.) If you notice State 2 events that are recorded for an agent, this indicates that the agent is taking a long time to write changes to the destination.</message></stats>
2016-03-17 00:30:46.335 Applied script 'MODE_1_12.dri'
2016-03-17 00:30:46.335 Applied script 'Pdv_16.dri'
2016-03-17 00:35:46.342 The process is running and is waiting for a response from the server.
2016-03-17 00:40:46.443 The process is running and is waiting for a response from the server.
2016-03-17 00:45:46.544 The process is running and is waiting for a response from the server.
2016-03-17 00:50:46.643 The process is running and is waiting for a response from the server.
2016-03-17 00:55:46.707 The process is running and is waiting for a response from the server.
2016-03-17 01:00:46.551 Query timeout expired
2016-03-17 01:00:46.957 Category:COMMAND
Source: Failed Command
Number:
Message: IF OBJECT_ID(N'[dbo].[FK_ARRMAG_IDCOULMOD]') IS NULL
BEGIN
ALTER TABLE [dbo].[ARRMAG] ADD CONSTRAINT [FK_ARRMAG_IDCOULMOD] FOREIGN KEY
([IDCOULMOD])
REFERENCES
[dbo].[COULMOD]([IDCOULMOD])
NOT FOR REPLICATION
ALTER TABLE [dbo].[ARRMAG] CHECK CONSTRAINT [FK_ARRMAG_IDCOULMOD]
END


2016-03-17 01:00:46.957 Category:NULL
Source: Microsoft SQL Server Native Client 11.0
Number: HYT00
Message: Query timeout expired

Au niveau des logs :
LogDate ProcessInfo Text
2016-03-17 00:00:19.250 spid29s This instance of SQL Server has been using a process ID of 2460 since 07.03.2016 14:00:29 (local) 07.03.2016 13:00:29 (UTC). This is an informational message only; no user action is required.
2016-03-17 01:00:02.530 spid56 AppDomain 12 (SSISDB.dbo[runtime].11) created.
2016-03-17 01:00:02.740 spid56 Unsafe assembly 'microsoft.sqlserver.integrationservices.server, version=11.0.0.0, culture=neutral, publickeytoken=89845dcd8080cc91, processorarchitecture=msil' loaded into appdomain 12 (SSISDB.dbo[runtime].11).
2016-03-17 01:00:02.990 spid56 Unsafe assembly 'microsoft.sqlserver.integrationservices.server, version=11.0.0.0, culture=neutral, publickeytoken=89845dcd8080cc91, processorarchitecture=msil' loaded into appdomain 12 (SSISDB.dbo[runtime].11).
2016-03-17 01:00:08.410 spid57 Configuration option 'min server memory (MB)' changed from 2000 to 22000. Run the RECONFIGURE statement to install.
2016-03-17 01:00:08.410 spid57 Configuration option 'max server memory (MB)' changed from 4000 to 22000. Run the RECONFIGURE statement to install.
2016-03-17 01:00:08.410 spid57 SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2016-03-17 01:00:08.410 spid57 SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2016-03-17 01:00:08.410 spid57 SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2016-03-17 02:24:57.240 spid27s AppDomain 12 (SSISDB.dbo[runtime].11) is marked for unload due to memory pressure.
2016-03-17 02:24:57.340 spid27s AppDomain 12 (SSISDB.dbo[runtime].11) unloaded.
2016-03-17 02:25:37.810 spid57 Error: 1204, Severity: 19, State: 4.
2016-03-17 02:25:37.810 spid57 The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions.
Le package SSIS qui pilote tout ça commence à 1h00.

Qqun a-t-il une idée ?



Edit :

J'ai aussi cette erreur là dans l'observateur d’evenement système :
Les paramètres d’autorisation spécifiques à l’application n’accordent pas d’autorisation Local Activation pour l’application serveur COM avec le CLSID
{FDC3723D-1588-4BA3-92D4-42C430735D7D}
et l’APPID
{83B33982-693D-4824-B42E-7196AE61BB05}
au SID NT SERVICE\SQLSERVERAGENT de l’utilisateur (S-1-5-80-344959196-2060754871-2302487193-2804545603-1466107430) depuis l’adresse LocalHost (utilisation de LRPC). Cette autorisation de sécurité peut être modifiée à l’aide de l’outil d’administration Services de composants.