OS is Windows Server 2003 32 bit. SQL Server is SQL Server 2008 SP3 32 bit
Today I finished configuring database mirroring and log shipping for 3 databases on this SQL instance and started receiving timeouts (connection and query timeouts from our applications) and log backup errors (log backup was performed by log shipping executable). Never had this kind of issues before when database mirroring was not active and log backups were performed via our custom SSIS package instead of using log shipping (also previously log backup was setup only for 1 database every 15 minutes and now it is for 3 databases every 5 minutes).
1) Connection timeout from one of our applications:
Event Text: System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open() at App.Component1.EventBroadcaster.ConnectToSql(SqlConnection connect, Boolean bulk) at App.Component1.Broadcaster.ReBroadCastLocate(Int32 LocateId, BroadCastType bcType_)
2) Query timeout from another of our applications:
Event Text: System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. at App1.RTFS.DBProcess.exeNonQuery(SqlCommand cmd_) at App1.RTFS.RTDB.submitOrder(NewOrderParams& params_) at App1.RTFS.NewOrderMessageProcess.processSingle() at App1.RTFS.NewOrderMessageProcess.processSubmit() at App1.RTFS.NewOrderMessageProcess.process() at App1.RTFS.Application.processMessageRequest(IMessageProcess msgProcess, SessionID sessionID)
3) Log backup failure (all these are created at the same time in Event log)
BACKUP failed to complete the command BACKUP LOG DB0. Check the backup application log for detailed messages.
There is insufficient system memory in resource pool 'default' to run this query.
There is insufficient system memory in resource pool 'internal' to run this query.
These errors are not that frequent 5-6 times a day
I think error messages related to failed log backup (
"There is insufficient system memory in resource pool") indicate possible bottleneck. From what I found possible solution is to allocate more memory for SQL Server by using AWE. However, this server only has 4GB of physical RAM and SQL Server is already using 1.8GB (as shown by task manager).
How can I better troubleshoot these issues? Is there a way to see detailed memory situation for SQL Server and trace exactly what queries/connections are timing out?