备份失败并出现错误SQL Server在数据库ID 2中的文件[D：\TempDB \ tempdb.mdf]上遇到1次超过15秒的I / O请求。 [英] Backup failing with error SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [D:\TempDB\tempdb.mdf] in database id 2.
问题描述
嗨！
我遇到了2节点AG SQL 2016 Azure Always On配置问题。
它故障转移手动确定没有问题。但是每天晚上运行完整备份（备份到URL存储容器）时，它会备份所有较小的数据库，但当它到达最后一个数据库（1.4TB）时，备份作业将失败，并显示错误消息：
由于会话处于kill状态，无法继续执行。
当我同时查看SQL日志时请参阅以下内容：
SQL Server遇到1次I / O请求，在文件上完成时间超过15秒[D：\ TempDB \ tempdb .mdf]在数据库ID 2中。 操作系统文件句柄为0x0000000000000DA0。 最新的长I / O的偏移量为：0x00000017f60000
AG组尝试故障转移但失败。备份作业错误但实例保持在线状态。 D：驱动器是一个223GB的快速驱动器 容量和173GB免费。唯一的东西就是Tempdb文件。
有什么想法在这里发生吗？
谢谢，
Zoe
消息看起来与我无关。 I / O消息说teampdb所在的驱动器是"慢"的。以及一些无法处理I / O请求的请求。虽然备份发生在某个存储在其他地方的容器上。问题
这里你应该看到为什么备份到1.4 TB的URL不起作用。你能查一下
支持URL时的
限制。检查你是否遇到任何这些限制
限制
不支持备份到高级存储。
SQL Server将页面blob支持的最大备份大小限制为1 TB。使用块blob支持的最大备份大小限制为大约200 MB（50,000块* 4MB MAXTRANSFERSIZE）。阻止blob支持条带化以支持
大得多的备份大小。
< p style ="">您可以使用TSQL，SMO，PowerShell cmdlet，SQL Server Management Studio备份或还原向导发出备份或还原语句。
不支持创建逻辑设备名称。因此，不支持使用sp_dumpdevice或通过SQL Server Management Studio将URL添加为备份设备。
不支持附加到现有备份blob。只能使用 WITH FORMAT 选项覆盖现有blob的备份。但是，在使用文件快照备份时（使用 WITH
FILE_SNAPSHOT 参数）， WITH FORMAT 参数，以避免遗留使用原始文件快照备份创建的孤立文件快照。
仅使用块blob并使用共享访问签名（SAS）令牌而不是使用共享访问签名（SAS）令牌来支持在单个备份操作中备份到多个blob SQL Credential的存储帐户密钥。
指定 ; BLOCKSIZE 不支持页面blob。
指定 MAXTRANSFERSIZE 不支持页面blob。
指定备份集选项 - RETAINDAYS EXPIREDATE 不受支持。
SQL Server对备份设备名称的最大限制为259个字符。对于用于指定备份设备名称的必需元素，BACKUP TO URL使用36个字符URL - 'https：//.blob.core.windows.net//.bak'，为帐户，容器，
和blob名称留下223个字符。
Hi!
I am having a problem with a 2 node AG SQL 2016 Azure Always On configuration.
It fails over manually ok with no issues. But every night when the full backup runs (backups to a URL Storage Container) it backs up all the smaller databases, but when it reaches the final one (which is 1.4TB) the backup job fails with the error message:
Cannot continue the execution because the session is in the kill state.
When I look in the SQL logs for the same time I see the following:
SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [D:\TempDB\tempdb.mdf] in database id 2. The OS file handle is 0x0000000000000DA0. The offset of the latest long I/O is: 0x00000017f60000
The AG group tries to fail over but fails. The backup job errors but the instance stays online. The D: drive is a fast drive with 223GB capacity and 173GB free. The only thing on there is the Tempdb files.
Any idea whats happening here?
Thanks,
Zoe
解决方案Those messages looks unrelated to me. The I/O message says that drive on which teampdb resides is "slow" and for some requests not able to cope with I/O requests. While backup is happening on some container which stored somewhere else. The problem here is you should see why backup to URL for 1.4 TB is not working. Could you check limitation when backing to URL. Check if you are hitting any of those limitations
Limitations
Backup to premium storage is not supported.
SQL Server limits the maximum backup size supported using a page blob to 1 TB. The maximum backup size supported using block blobs is limited to approximately 200 MB (50,000 blocks * 4MB MAXTRANSFERSIZE). Block blobs support striping to support substantially larger backup sizes.
You can issue backup or restore statements by using TSQL, SMO, PowerShell cmdlets, SQL Server Management Studio Backup or Restore wizard.
Creating a logical device name is not supported. So adding URL as a backup device using sp_dumpdevice or through SQL Server Management Studio is not supported.
Appending to existing backup blobs is not supported. Backups to an existing blob can only be overwritten by using the WITH FORMAT option. However, when using file-snapshot backups (using the WITH FILE_SNAPSHOT argument), the WITH FORMATargument is not permitted to avoid leaving orphaned file-snapshots that were created with the original file-snapshot backup.
Backup to multiple blobs in a single backup operation is only supported using block blobs and using a Shared Access Signature (SAS) token rather than the storage account key for the SQL Credential.
Specifying BLOCKSIZE is not supported for page blobs.
Specifying MAXTRANSFERSIZE is not supported page blobs.
Specifying backupset options - RETAINDAYS and EXPIREDATE are not supported.
SQL Server has a maximum limit of 259 characters for a backup device name. The BACKUP TO URL consumes 36 characters for the required elements used to specify the URL - 'https://.blob.core.windows.net//.bak', leaving 223 characters for account, container, and blob names put together.
这篇关于备份失败并出现错误SQL Server在数据库ID 2中的文件[D：\TempDB \ tempdb.mdf]上遇到1次超过15秒的I / O请求。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
