0

I am running SQL from my host machine rather than via the OOTB mssql service. I did this by customizing the database query strings in my docker compose override file:

cd:
  environment:
    Sitecore_ConnectionStrings_Web: Data Source=xxxxxxx;Initial Catalog=xxxxxxx-web-db;User ID=xxxxxxx;Password=xxxxxxx
    Sitecore_ConnectionStrings_Core: Data Source=xxxxxxx;Initial Catalog=xxxxxxx-core-db;User ID=xxxxxxx;Password=xxxxxxx

The problem is that my cd service often requires multiple calls of docker compose up -d and docker compose down and a good amount of waiting around before it starts working.

The first time I run docker compose up -d, I will often see:

PS C:\projects\myproject> docker compose up -d 
[+] Running 12/12
 ✔ Network myproject_default         Created     0.5s 
 ✔ Container myproject-solr-1        Healthy     43.3s 
 ✔ Container myproject-redis-1       Started     4.6s 
 ✔ Container myproject-mssql-1       Healthy     38.9s 
 ✔ Container myproject-solution-1    Started     5.4s 
 ✔ Container myproject-mssql-init-1  Healthy     65.3s 
 ✔ Container myproject-solr-init-1   Started     47.4s 
 ✔ Container myproject-id-1          Healthy     109.1s 
 ✘ Container myproject-cd-1          Error       315.1s 
 ✔ Container myproject-cm-1          Started     71.2s 
 ✔ Container myproject-hrz-1         Healthy     104.0s 
 ✔ Container myproject-traefik-1     Created     0.2s
dependency failed to start: container myproject-cd-1 is unhealthy

Looking at the Docker logs, I see:

2023-09-20 14:57:42 myproject-cd-1 | [2023-09-20T20:57:42.000Z][LOGMONITOR] ERROR: Error in log file monitor. Failed to query file information. File: \\?\c:\inetpub\wwwroot\App_Data\logs\log.20230914.152738.txt. Error: 50

When I inspect that log file I see no notable WARN or ERROR entries. All I see is the site shutting down:

**********************************************************************
**********************************************************************

 668 15:28:23 INFO  **************************************************
 668 15:28:23 WARN  Sitecore shutting down
 668 15:28:23 WARN  Shutdown message: Initialization Error
HostingEnvironment initiated shutdown

I open terminal on the cd service and run a health check:

curl http://127.0.0.1/healthz/ready

[InvalidOperationException: Timeout expired.  The timeout period elapsed prior to obtaining a connection from the pool.  This may h
ave occurred because all pooled connections were in use and max pool size was reached.]
   System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbCon
nectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) +1318
   System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connec
tionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) +307
   System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry) +198
   System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry) +422
   System.Data.SqlClient.SqlConnection.Open() +199
   Sitecore.Data.DataProviders.Sql.DataProviderCommand..ctor(IDbCommand command, DataProviderTransaction transaction, Boolean openC
onnection) +113
   Sitecore.Data.DataProviders.Sql.<>c__DisplayClass26_0.<CreateCommand>b__0() +48
   Sitecore.Data.DataProviders.NullRetryer.Execute(Func`1 action, Action recover) +293
   Sitecore.Data.DataProviders.Sql.<>c__DisplayClass29_0.<CreateReader>b__0() +30
   Sitecore.Data.DataProviders.NullRetryer.Execute(Func`1 action, Action recover) +293
   Sitecore.Data.DataProviders.Sql.SqlDataApi.CreateReader(String sql, Object[] parameters) +281
   Sitecore.Data.DataProviders.Sql.<>c__DisplayClass92_0.<SelectIDs>b__0(Int32 batchNumber) +594
   System.Threading.Tasks.<>c__DisplayClass17_0`1.<ForWorker>b__1() +1396
   System.Threading.Tasks.Task.InnerInvokeWithArg(Task childTask) +32
   System.Threading.Tasks.<>c__DisplayClass176_0.<ExecuteSelfReplicating>b__0(Object <p0>) +278

[AggregateException: One or more errors occurred.]
   System.Threading.Tasks.Task.ThrowIfExceptional(Boolean includeTaskCanceledExceptions) +4674501
   System.Threading.Tasks.Task.Wait(Int32 millisecondsTimeout, CancellationToken cancellationToken) +14248496
   System.Threading.Tasks.Parallel.ForWorker(Int32 fromInclusive, Int32 toExclusive, ParallelOptions parallelOptions, Action`1 body
, Action`2 bodyWithState, Func`4 bodyWithLocal, Func`1 localInit, Action`1 localFinally) +1446
   System.Threading.Tasks.Parallel.For(Int32 fromInclusive, Int32 toExclusive, Action`1 body) +113
   Sitecore.Data.DataProviders.Sql.SqlDataProvider.SelectIDs(ID[] filterIds, CallContext context) +275
   Sitecore.Data.DataProviders.Sql.SqlDataProvider.PrefetchNotExistedItems(IdCollection prefetchingItems, CallContext context) +102
   Sitecore.Data.DataProviders.CompositeDataProvider.PrefetchResourceItems() +688
   Sitecore.Data.DefaultDatabase.AddDataProvider(DataProvider provider) +49

[TargetInvocationException: Exception has been thrown by the target of an invocation.]
   System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor) +0
   System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments) +132
   System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo 
culture) +146
   Sitecore.Configuration.DefaultFactory.AssignProperties(Object obj, Object[] properties) +853
   Sitecore.Configuration.DefaultFactory.AssignProperties(XmlNode configNode, String[] parameters, Object obj, Boolean assert, Bool
ean deferred, IFactoryHelper helper) +641
   Sitecore.Configuration.DefaultFactory.CreateObject(XmlNode configNode, String[] parameters, Boolean assert, IFactoryHelper helpe
r) +326
   Sitecore.Configuration.DefaultFactory.CreateObject(XmlNode configNode, String[] parameters, Boolean assert) +72
   Sitecore.Configuration.DefaultFactory.CreateObject(String configPath, String[] parameters, Boolean assert) +703
   Sitecore.Configuration.DefaultFactory.GetDatabase(String name, Boolean assert) +157
   Sitecore.Configuration.DefaultFactory.GetDatabase(String name) +55
   Sitecore.Configuration.DefaultFactory.GetDatabases() +121
   Sitecore.Data.SqlServer.SqlServerLinkDataProviderFactory.CreateProvidersForAllDatabases() +161

[TargetInvocationException: Exception has been thrown by the target of an invocation.]
   System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor) +0
   System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments) +269
   System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo 
culture) +146
   Sitecore.Reflection.ReflectionUtil.CallStaticMethod(String typeName, String methodName, Object[] parameters) +165
   Sitecore.Configuration.DefaultFactory.CreateFromFactoryMethod(XmlNode configNode, String[] parameters, Boolean assert) +534     
   Sitecore.Configuration.DefaultFactory.CreateObject(XmlNode configNode, String[] parameters, Boolean assert, IFactoryHelper helpe
r) +91
   Sitecore.Configuration.DefaultFactory.CreateObject(XmlNode configNode, String[] parameters, Boolean assert) +72
   Sitecore.Configuration.DefaultFactory.GetConstructorParameters(XmlNode configNode, String[] parameters, Boolean assert) +161    
   Sitecore.Configuration.DefaultFactory.CreateFromTypeName(XmlNode configNode, String[] parameters, Boolean assert) +114
   Sitecore.Configuration.DefaultFactory.CreateObject(XmlNode configNode, String[] parameters, Boolean assert, IFactoryHelper helpe
r) +163
   Sitecore.Configuration.DefaultFactory.CreateObject(XmlNode configNode, String[] parameters, Boolean assert) +72
   Sitecore.Configuration.DefaultFactory.CreateObject(String configPath, String[] parameters, Boolean assert) +703
   Sitecore.Configuration.DefaultFactory.GetLinkDatabase() +36
   Sitecore.DefaultGlobals.Load() +294
   (Object , Object ) +55
   Sitecore.Pipelines.CorePipeline.Run(PipelineArgs args) +1268
   Sitecore.Pipelines.DefaultCorePipelineManager.Run(String pipelineName, PipelineArgs args, String pipelineDomain, Boolean failIfN
otExists) +236
   Sitecore.Pipelines.DefaultCorePipelineManager.Run(String pipelineName, PipelineArgs args, String pipelineDomain) +22
   Sitecore.Nexus.Web.HttpModule.Application_Start() +146
   Sitecore.Nexus.Web.HttpModule.Init(HttpApplication app) +918
   System.Web.HttpApplication.RegisterEventSubscriptionsWithIIS(IntPtr appContext, HttpContext context, MethodInfo[] handlers) +584
   System.Web.HttpApplication.InitSpecial(HttpApplicationState state, MethodInfo[] handlers, IntPtr appContext, HttpContext context
) +168
   System.Web.HttpApplicationFactory.GetSpecialApplicationInstance(IntPtr appContext, HttpContext context) +277
   System.Web.Hosting.PipelineRuntime.InitializeApplication(IntPtr appContext) +369

[HttpException (0x80004005): Exception has been thrown by the target of an invocation.]
   System.Web.HttpRuntime.FirstRequestInit(HttpContext context) +532
   System.Web.HttpRuntime.EnsureFirstRequestInit(HttpContext context) +111
   System.Web.HttpRuntime.ProcessRequestNotificationPrivate(IIS7WorkerRequest wr, HttpContext context) +724

<!--
[InvalidOperationException]: Timeout expired.  The timeout period elapsed prior to obtaining a connection from the pool.  This may 
have occurred because all pooled connections were in use and max pool size was reached.
   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, Db
ConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory con
nectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.Open()
   at Sitecore.Data.DataProviders.Sql.DataProviderCommand..ctor(IDbCommand command, DataProviderTransaction transaction, Boolean op
enConnection)
   at Sitecore.Data.DataProviders.Sql.SqlDataApi.<>c__DisplayClass26_0.<CreateCommand>b__0()
   at Sitecore.Data.DataProviders.NullRetryer.Execute[T](Func`1 action, Action recover)
   at Sitecore.Data.DataProviders.Sql.SqlDataApi.<>c__DisplayClass29_0.<CreateReader>b__0()
   at Sitecore.Data.DataProviders.NullRetryer.Execute[T](Func`1 action, Action recover)
   at Sitecore.Data.DataProviders.Sql.SqlDataApi.CreateReader(String sql, Object[] parameters)
   at Sitecore.Data.DataProviders.Sql.SqlDataProvider.<>c__DisplayClass92_0.<SelectIDs>b__0(Int32 batchNumber)
   at System.Threading.Tasks.Parallel.<>c__DisplayClass17_0`1.<ForWorker>b__1()
   at System.Threading.Tasks.Task.InnerInvokeWithArg(Task childTask)
   at System.Threading.Tasks.Task.<>c__DisplayClass176_0.<ExecuteSelfReplicating>b__0(Object <p0>)
[AggregateException]: One or more errors occurred.
   at System.Threading.Tasks.Task.ThrowIfExceptional(Boolean includeTaskCanceledExceptions)
   at System.Threading.Tasks.Task.Wait(Int32 millisecondsTimeout, CancellationToken cancellationToken)
   at System.Threading.Tasks.Parallel.ForWorker[TLocal](Int32 fromInclusive, Int32 toExclusive, ParallelOptions parallelOptions, Ac
tion`1 body, Action`2 bodyWithState, Func`4 bodyWithLocal, Func`1 localInit, Action`1 localFinally)
   at System.Threading.Tasks.Parallel.For(Int32 fromInclusive, Int32 toExclusive, Action`1 body)
   at Sitecore.Data.DataProviders.Sql.SqlDataProvider.SelectIDs(ID[] filterIds, CallContext context)
   at Sitecore.Data.DataProviders.Sql.SqlDataProvider.PrefetchNotExistedItems(IdCollection prefetchingItems, CallContext context)  
   at Sitecore.Data.DataProviders.CompositeDataProvider.PrefetchResourceItems()
   at Sitecore.Data.DefaultDatabase.AddDataProvider(DataProvider provider)
[TargetInvocationException]: Exception has been thrown by the target of an invocation.
   at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
   at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
   at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureIn
fo culture)
   at Sitecore.Configuration.DefaultFactory.AssignProperties(Object obj, Object[] properties)
   at Sitecore.Configuration.DefaultFactory.AssignProperties(XmlNode configNode, String[] parameters, Object obj, Boolean assert, B
oolean deferred, IFactoryHelper helper)
   at Sitecore.Configuration.DefaultFactory.CreateObject(XmlNode configNode, String[] parameters, Boolean assert, IFactoryHelper he
lper)
   at Sitecore.Configuration.DefaultFactory.CreateObject(XmlNode configNode, String[] parameters, Boolean assert)
   at Sitecore.Configuration.DefaultFactory.CreateObject(String configPath, String[] parameters, Boolean assert)
   at Sitecore.Configuration.DefaultFactory.GetDatabase(String name, Boolean assert)
   at Sitecore.Configuration.DefaultFactory.GetDatabase(String name)
   at Sitecore.Configuration.DefaultFactory.GetDatabases()
   at Sitecore.Data.SqlServer.SqlServerLinkDataProviderFactory.CreateProvidersForAllDatabases()
[TargetInvocationException]: Exception has been thrown by the target of an invocation.
   at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
   at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
   at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureIn
fo culture)
   at Sitecore.Reflection.ReflectionUtil.CallStaticMethod(String typeName, String methodName, Object[] parameters)
   at Sitecore.Configuration.DefaultFactory.CreateFromFactoryMethod(XmlNode configNode, String[] parameters, Boolean assert)       
   at Sitecore.Configuration.DefaultFactory.CreateObject(XmlNode configNode, String[] parameters, Boolean assert, IFactoryHelper he
lper)
   at Sitecore.Configuration.DefaultFactory.CreateObject(XmlNode configNode, String[] parameters, Boolean assert)
   at Sitecore.Configuration.DefaultFactory.GetConstructorParameters(XmlNode configNode, String[] parameters, Boolean assert)      
   at Sitecore.Configuration.DefaultFactory.CreateFromTypeName(XmlNode configNode, String[] parameters, Boolean assert)
   at Sitecore.Configuration.DefaultFactory.CreateObject(XmlNode configNode, String[] parameters, Boolean assert, IFactoryHelper he
lper)
   at Sitecore.Configuration.DefaultFactory.CreateObject(XmlNode configNode, String[] parameters, Boolean assert)
   at Sitecore.Configuration.DefaultFactory.CreateObject(String configPath, String[] parameters, Boolean assert)
   at Sitecore.Configuration.DefaultFactory.GetLinkDatabase()
   at Sitecore.DefaultGlobals.Load()
   at (Object , Object )
   at Sitecore.Pipelines.CorePipeline.Run(PipelineArgs args)
   at Sitecore.Pipelines.DefaultCorePipelineManager.Run(String pipelineName, PipelineArgs args, String pipelineDomain, Boolean fail
IfNotExists)
   at Sitecore.Pipelines.DefaultCorePipelineManager.Run(String pipelineName, PipelineArgs args, String pipelineDomain)
   at Sitecore.Nexus.Web.HttpModule.Application_Start()
   at Sitecore.Nexus.Web.HttpModule.Init(HttpApplication app)
   at System.Web.HttpApplication.RegisterEventSubscriptionsWithIIS(IntPtr appContext, HttpContext context, MethodInfo[] handlers)  
   at System.Web.HttpApplication.InitSpecial(HttpApplicationState state, MethodInfo[] handlers, IntPtr appContext, HttpContext cont
ext)
   at System.Web.HttpApplicationFactory.GetSpecialApplicationInstance(IntPtr appContext, HttpContext context)
   at System.Web.Hosting.PipelineRuntime.InitializeApplication(IntPtr appContext)
[HttpException]: Exception has been thrown by the target of an invocation.
   at System.Web.HttpRuntime.FirstRequestInit(HttpContext context)
   at System.Web.HttpRuntime.EnsureFirstRequestInit(HttpContext context)
   at System.Web.HttpRuntime.ProcessRequestNotificationPrivate(IIS7WorkerRequest wr, HttpContext context)
-->

I have seen similar Q&As recommend increasing the PagePreview.MaxSessionsCount in the main site config. That doesn't help.

What can I do to resolve this?

Improve this question

1 Answer 1

Reset to default
0

Increasing the max pool size and connection timeout can help. Add Max Pool Size=200;Connection Timeout=500; to each custom SQL query string:

cd:
  environment:
    Sitecore_ConnectionStrings_Web: Data Source=xxxxxxx;Initial Catalog=xxxxxxx-web-db;User ID=xxxxxxx;Password=xxxxxxx;Max Pool Size=200;Connection Timeout=500;
    Sitecore_ConnectionStrings_Core: Data Source=xxxxxxx;Initial Catalog=xxxxxxx-core-db;User ID=xxxxxxx;Password=xxxxxxx;Max Pool Size=200;Connection Timeout=500;

This way, we double the max pool size (default is 100), and we massively increase the connection timeout, which is particularly useful when Sitecore is starting up (it usually takes around 200 seconds in my case) and/or working through large event / publish queues, which is typical if you are connecting to databases that you copied from upstream but didn't run cleanup commands on; in which case, running these cleanup commands will help:

TRUNCATE TABLE [dbo].[EventQueue]
TRUNCATE TABLE [dbo].[PublishQueue]
TRUNCATE TABLE [dbo].[Properties]

# On web db:
TRUNCATE TABLE [dbo].[History]

Otherwise, you can also try restarting SQL server on your host machine prior to docker compose up -d to force close any connections that may not have been properly disposed of (thus flooding the connection pool).

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.