0

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).

More details:

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)

Event 3041
BACKUP failed to complete the command BACKUP LOG DB0. Check the backup application log for detailed messages.

Event 701
There is insufficient system memory in resource pool 'default' to run this query.

Event 701
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?

Improve this question
8
  • 1
    How large are the databases here? Also, can you look at perfmon and see what your Memory/Available MBs counter is at?
    – Mike Fal
    Commented Mar 15, 2013 at 16:37
  • Databases are not that large - one is 300 MB, another is 500 MB and another is 1.2 GB. Memory/Available MB counter i 1048 Min, 1081 Max
    – Joe Schmoe
    Commented Mar 15, 2013 at 16:46
  • For mirroring, are you using "High safety with automatic failover" with a witness machine? If so, the secondary machine may not be able to keep up with the primary and may be causing your timeout issues. What are the details of the mirror machine?
    – Hannah Vernon ♦
    Commented Mar 15, 2013 at 20:13
  • Mirroring is set to "High Safety without automatic failover". There is no witness. Insert/update activity on the primary is really low, so I don't think mirror not being able to keep up is an issue.
    – Joe Schmoe
    Commented Mar 15, 2013 at 20:30
  • High Safety means transactions must commit on the mirror before the transaction commits on the primary. What kind of setup do you have on the mirror machine? If you 'ALTER DATABASE xyz SET PARTNER SUSPEND" is everything suddenly quicker?
    – Hannah Vernon ♦
    Commented Mar 16, 2013 at 3:27

1 Answer 1

Reset to default
1

If your server has only 4GB of RAM, AWE isn't going to help you much. AWE is designed for when you have greater than 4GB of RAM you need to use.

Timeouts are harder to define. The timeout period itself is likely defined in your application, so it's not inherently SQL Server that's causing the timeouts. However, what you're running into are queries that take to long to complete. To troubleshoot this, I would first start by reviewing the wait stats to identify specifically what is causing things to drag in your SQL Server. Some waits to look for:

  • BACKUPTHREAD means your queries are waiting on backup operations to complete. Other backup waits could indicate problems.
  • Memory pressure could be any number of waits, but look for things like RESOURCE_SEMAPHORE or IO waits. Also, check the trend on your Page Life Expectancy in Perfmon (SQL Server: Buffer Manager -> Page Life Expectancy). If this continually trends low, your pages are cycling out of memory to quickly.

Another thing you can do is check how long your log shipping backups are taking to complete:

SELECT 
    database_name
    ,backup_finish_date
    ,datediff(ss,backup_start_date,backup_finish_date) SecondsToBackup
FROM
    msdb.dbo.backupset
WHERE
    type='l'
ORDER BY 
    backup_finish_date desc;

As for resolution, if your log shipping is happening all at the same time, you might want to try and stagger that out. If it isn't necessary to log ship every 5 minutes, I'd try and go with your 15 minutes and stagger each database off by 5 minutes so that they're running separately.

The other thing is to consider a hardware upgrade. The box your describing sounds fairly out of date, considering there are laptops you can purchase now that have more horse power than what you're describing. If you can move the server to a virtual machine, you'll be able to immediately upgrade to better resources, which could resolve these issues for you.

Improve this answer
4
  • Mike, Your reply us appreciated. Here are answers and more questions: 1) Will enabling AWE at least help some with 4 GB of RAM? I can give SQL 2.5 GB max memory for instance. 2) I ran query for wait stats you linked to. In the first set of results (since server was started) this is what it looks like for BACKUPTHREAD : sum_wait_time_ms=5796846 pct_wait_time=0.1 sum_waiting_tasks=236073 avg_wait_time_ms=24.6 3) SQL Server: Buffer Manager -> Page Life Expectancy is about 54,000 (avg) - is that considered low?
    – Joe Schmoe
    Commented Mar 15, 2013 at 20:16
  • 4) 99% log backups take 0 sec to complete, the rest are 1-3 sec. 5) Staggering log backups, making them less frequent and upgrading hardware is being considered (yes, that server is probably 6-8 years old)
    – Joe Schmoe
    Commented Mar 15, 2013 at 20:21
  • Sounds like your PLE is fine. There's a lot of discussion about the "right" value, but mostly you want to monitor the trend. As for your waits, look at what your highest wait is by pct_wait_time. You can see full documentation on the wait types here. It could take a lot of investigation to determine exactly what the problem is, all of this is just a start.
    – Mike Fal
    Commented Mar 15, 2013 at 20:24
  • Highest wait by pct_wait_time is DBMIRROR_SEND (82.7%, 5022 ms) but could it be skewed because I kept mirroring suspended for the last several hours for one of the databases? Second highest is PAGEIOLATCH_SH (11.0%, avg 94.2 ms)
    – Joe Schmoe
    Commented Mar 15, 2013 at 20:35

Your Answer

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

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