Access FE connection to SQL server failed

[RonPaii]

I was having a periodic connection problem between an Access 365 FE and SQL server BE. The ODBC connections would lock requiring restarting the SQL server service for the database. The log indicated "I/O is frozen on database #####" followed by a backup. then "I/O was resumed on database #####". The SQL server is on a VM using VSS (Volume Shadow Copy Service). Apparently the pause will cause Access ODBC connection to timeout, locking the connection. The freeze is 1 - 2 seconds.

I implement 2 fixes.
1) On the FE I set the ODBC Time out on query's to 60, only required on adhoc because the saved queues defaulted to this value.
2) Added a SQL Agent Job to kill stale Access sessions.

leave out AND database_id = DB_ID('#####') for all databases on the server.

DECLARE @sid INT;
DECLARE @sql NVARCHAR(50);

DECLARE stale CURSOR FOR
SELECT session_id
FROM sys.dm_exec_sessions
WHERE program_name = 'Microsoft Office'
  AND status = 'sleeping'
  AND database_id = DB_ID('#####')
  AND DATEDIFF(minute, last_request_end_time, GETDATE()) > 10;

OPEN stale;
FETCH NEXT FROM stale INTO @sid;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = N'KILL ' + CAST(@sid AS NVARCHAR(10));
    PRINT @sql;
    EXEC (@sql);

    FETCH NEXT FROM stale INTO @sid;
END

CLOSE stale;
DEALLOCATE stale;