ADM Exception #1
Description
Hi,
I have been facing the issue to finish the "bounce Cleanup Job".
I have this error.
"An exception occurred during running the SQL command. Please check the log file for details. Short info: The DELETE statement conflicted with the REFERENCE constraint "FK_Pages_Visitors". The conflict occurred in database "Analytics", table "dbo.Pages", column 'VisitorId'.
The statement has been terminated.
Job ended: Analytics Database Manager (units processed: )"
Before I ran the job this time. I did the following.
- Installed the latest 2.1 version
- Ran Check Integrity SQL
- Ran fix integrity SQL
Environment: Sitecore 6.5, database size 160gb, windows 2003 sp2, IIS6
The job ran for over 5/6 hours and I got this error. Then I change all the SQL timeouts for 12 hours and then to 22 hours but I still have the same error.
The log error says the following:
ManagedPoolThread #17 14:02:44 INFO AUDIT (sitecore\abc): 'Clean bounce visitors' task started
ManagedPoolThread #10 14:10:19 ERROR Exception in geoip worker thread.
Exception: System.Data.SqlClient.SqlException
Message: Transaction (Process ID 128) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Source: .Net SqlClient Data Provider
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.HasMoreRows()
at System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout)
at System.Data.SqlClient.SqlDataReader.NextResult()
at System.Data.DataTable.Load(IDataReader reader, LoadOption loadOption, FillErrorEventHandler errorHandler)
at Sitecore.Analytics.Data.DataAccess.DataAdapters.Sql.SqlServer.SqlServerLookup.GetLookupData(GetLookupDataRequest request)
at Sitecore.Analytics.Lookups.GeoIpWorker.ProcessGeoIp()
at Sitecore.Analytics.Lookups.GeoIpWorker.b__3(Object a)
ManagedPoolThread #2 14:15:07 INFO Job started: Sitecore.Analytics.Tasks.SubscriptionTask
ManagedPoolThread #2 14:15:07 INFO SubscriptionTask started.
ManagedPoolThread #2 14:15:08 INFO SubscriptionTask done.
ManagedPoolThread #2 14:15:08 INFO Job ended: Sitecore.Analytics.Tasks.SubscriptionTask (units processed: )
ManagedPoolThread #9 14:30:08 INFO Job started: Sitecore.Analytics.Tasks.SubscriptionTask
ManagedPoolThread #9 14:30:08 INFO SubscriptionTask started.
ManagedPoolThread #9 22:33:31 INFO SubscriptionTask done.
ManagedPoolThread #9 22:33:31 INFO Job ended: Sitecore.Analytics.Tasks.SubscriptionTask (units processed: )
ManagedPoolThread #1 22:33:31 INFO Job started: Sitecore.Analytics.Tasks.UpdateReportsSummaryTask
ManagedPoolThread #1 22:33:31 INFO Job ended: Sitecore.Analytics.Tasks.UpdateReportsSummaryTask (units processed: )
ManagedPoolThread #12 22:33:41 INFO Job started: Sitecore.Tasks.CleanupEventQueue
ManagedPoolThread #12 22:33:41 INFO Job ended: Sitecore.Tasks.CleanupEventQueue (units processed: )
ManagedPoolThread #8 22:33:41 INFO Job started: Sitecore.Analytics.Tasks.EmailReportsTask
ManagedPoolThread #8 22:33:41 INFO EmailReportsTask started. Database: master
ManagedPoolThread #8 22:33:41 INFO Job ended: Sitecore.Analytics.Tasks.EmailReportsTask (units processed: )
ManagedPoolThread #15 22:39:36 ERROR Exception in geoip worker thread.
Exception: System.Exception
Message: Transaction (Process ID 84) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Source: Sitecore.Kernel
at Sitecore.Data.DataProviders.Sql.DataProviderCommand.ExecuteReader()
at Sitecore.Data.DataProviders.Sql.SqlDataApi.CreateReader(String sql, Object[] parameters)
at Sitecore.Analytics.Data.DataAccess.DataAdapters.SqlBase.ReadMany(DataSourceQuery query, Action`1 handler)
at Sitecore.Analytics.Data.DataAccess.DataAdapters.Sql.SqlLookup.GetVisitsWithEmptyLocation()
at Sitecore.Analytics.Lookups.GeoIpWorker.ProcessVisits()
at Sitecore.Analytics.Lookups.GeoIpWorker.b__3(Object a)
Nested Exception
Exception: System.Data.DataException
Message: Error executing SQL command: SE
LECT [v].[VisitId] , [v].[VisitorId] , [v].[VisitorVisitIndex] , [v].[VisitPageCount] , [v].[StartDateTime] , [v].[EndDateTime] , [v].[TrafficType] , [v].[Value] , [v].[AspNetSessionId] , [v].[ReferringSiteId] , [v].[KeywordsId] , [v].[BrowserId] , [v].[UserAgentId] , [v].[OsId] , [v].[ScreenId] , [v].[CampaignId] , [v].[RDNS] , [v].[MultiSite] , [v].[LocationId] , [v].[Ip] , [v].[BusinessName] , [v].[City] , [v].[PostalCode] , [v].[MetroCode] , [v].[AreaCode] , [v].[Region] , [v].[IspName] , [v].[Country] , [v].[Latitude] , [v].[Longitude] , [v].[TestSetId] , [v].[TestValues] , [v].[Referrer] , [v].[State] , [v].[StateChanged] , [v].[DeviceName] , [v].[Language] FROM [Visits] [v], [GeoIps] [i] WHERE [i].[Status] = @p1 AND [i].[Ip] = [v].[Ip] AND [v].[LocationId] = @p2 AND [v].[EndDateTime] < @p3
Nested Exception
Exception: System.Data.SqlClient.SqlException
Message: Transaction (Process ID 84) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Source: .Net SqlClient Data Provider
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
at Sitecore.Data.DataProviders.Sql.DataProviderCommand.ExecuteReader()
ManagedPoolThread #17 22:42:51 ERROR SQL error during the running of the task.
Exception: System.Data.SqlClient.SqlException
Message: The DELETE statement conflicted with the REFERENCE constraint "FK_Pages_Visitors". The conflict occurred in database "NYLCD65_Analytics", table "dbo.Pages", column 'VisitorId'.
The statement has been terminated.
Source: .Net SqlClient Data Provider
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Sitecore.AnalyticsDatabaseManager.Logic.AnalyticsDatabaseHelper.ExecuteNonQuery(String query)
at Sitecore.AnalyticsDatabaseManager.Logic.AnalyticsDatabaseHelper.RemoveUnusedGlobalSessions()
at Sitecore.AnalyticsDatabaseManager.Logic.AnalyticsDatabaseService.ApplySimpleCleanFilter(String query)
at Sitecore.AnalyticsDatabaseManager.Logic.AnalyticsDatabaseService.CleanBounceSessions()
at Sitecore.AnalyticsDatabaseManager.Jobs.CleanFilteredBounceSessionsRunner.RunnerLogic(Job job)
at Sitecore.AnalyticsDatabaseManager.Jobs.JobRunnerBase.Run()
ManagedPoolThread #17 22:42:51 INFO Job ended: Analytics Database Manager (units processed: )
ManagedPoolThread #0 22:48:32 INFO Job started: Sitecore.Analytics.Tasks.SubscriptionTask
ManagedPoolThread #0 22:48:32 INFO SubscriptionTask started.
ManagedPoolThread #0 22:48:32 INFO SubscriptionTask done.
ManagedPoolThread #0 22:48:32 INFO Job ended: Sitecore.Analytics.Tasks.SubscriptionTask (units processed: )
Heartbeat 23:00:01 INFO Health.PrivateBytes: 393,150,464
Heartbeat 23:00:01 INFO Health.CacheInstances: 99
Heartbeat 23:00:01 INFO Health.CacheTotalCount: 17,819
Heartbeat 23:00:01 INFO Health.CacheTotalSize: 44,616,606
Heartbeat 23:00:01 INFO Health.Counter(w3wp#8, Process\Private Bytes): 393,322,496
Heartbeat 23:00:02 INFO Health.Counter(w3wp#8, Process\Virtual Bytes): 3,143,163,904
ManagedPoolThread #14 23:00:02 INFO Job started: Sitecore.Tasks.CounterDumpAgent
Heartbeat 23:00:02 INFO Health.Counter(w3wp#8, Process\Page File Bytes): 392,798,208
Heartbeat 23:00:03 INFO Health.Counter(w3wp, .net CLR Memory# Bytes in all Heaps): 78,192,360
Heartbeat 23:00:03 INFO Health.Counter(w3wp, .net CLR Memory% Time in GC): 596,204,469
Heartbeat 23:00:03 INFO Health.Counter(w3wp, .net CLR Memory\Large Object Heap size): 55,020,792
Heartbeat 23:00:03 INFO Health.Counter(w3wp, .net CLR Loading\Bytes in Loader Heap): 12,079,104
Heartbeat 23:00:03 INFO Health.Counter(w3wp, .net CLR Loading\Current Assemblies): 130
ManagedPoolThread #14 23:00:03 INFO Job ended: Sitecore.Tasks.CounterDumpAgent (units processed: )
6148 23:03:05 INFO **************************************************
6148 23:03:05 WARN Sitecore shutting down
6148 23:03:05 WARN Shutdown message: HostingEnvironment initiated shutdown
ManagedPoolThread #3 23:03:33 INFO Job started: Sitecore.Analytics.Tasks.SubscriptionTask
ManagedPoolThread #3 23:03:33 INFO SubscriptionTask started.
ManagedPoolThread #3 23:03:33 INFO SubscriptionTask done.
ManagedPoolThread #3 23:03:33 INFO Job ended: Sitecore.Analytics.Tasks.SubscriptionTask (units processed: )
ManagedPoolThread #14 23:03:33 INFO Job started: Sitecore.Analytics.Tasks.UpdateReportsSummaryTask
ManagedPoolThread #14 23:03:33 INFO Job ended: Sitecore.Analytics.Tasks.UpdateReportsSummaryTask (units processed: )