We normally perform backups across the network to a file share on a different SAN. This works very well for all but 1 of our database servers. The problem machine is a new server running Windows Server 2012R2 as the OS, and SQL Server 2012 Standard Edition for the database instance. The network latency between the server and backup share is sub-MS.
The actual backup of the data and log file happen quickly (sub-second), however, it seems to get stuck at 100% completed (when viewed through sp_WhoIsActive) with a BACKUPTHREAD wait type. If I perform the same backup but to a local drive, it does not experience this problem.
Here is a sample backup command we are running for the MODEL (only 5MBs) database:
USE MASTER GO BACKUP DATABASE MODEL TO DISK = N'\\<FileShareServer>\SQL_Backup\MODEL.BAK' WITH NAME = N'MODEL-Full Database Backup' ,NOFORMAT, INIT, SKIP, NOREWIND, NOUNLOAD, STATS = 10 ,CHECKSUM, COPY_ONLY, COMPRESSION;
Here are the stats for the backup, with Trace Flag 3604, 3213 turned on:
Backup/Restore buffer configuration parameters Memory limit: 36855MB BufferCount: 7 Sets Of Buffers: 3 MaxTransferSize: 64 KB Min MaxTransferSize: 64 KB Total buffer space: 1 MB Tabular data device count: 1 Fulltext data device count: 0 Filestream device count: 0 TXF device count: 0 Filesystem i/o alignment: 512 Media Buffer count: 7 Media Buffer size: 64KB Encode Buffer count: 7 10 percent processed. 20 percent processed. 30 percent processed. 40 percent processed. 51 percent processed. 61 percent processed. 71 percent processed. 81 percent processed. 91 percent processed. Processed 312 pages for database 'MODEL', file 'modeldev' on file 1. 100 percent processed. Processed 2 pages for database 'MODEL', file 'modellog' on file 1. -- Up to here happens sub-second as expected BACKUP DATABASE successfully processed 314 pages in 252.372 seconds (0.009 MB/sec).
I've tried playing around with stripped backups as well as setting the BUFFERCOUNT and MAXTRANSFERSIZE to higher numbers, to no avail.
Thanks in advanced for your input!
Based on Aaron Bertrand's suggestion, I turned on trace flags 3605, 3004, and 3014 and got the following additional information (only pasting in the portion from where it gets to 100% and then sits waiting):
慂正灵慄慴慢敳退邐邐邐邐坖呁啁荈⃬튅襌⑴䱘粉怤ཁ쒔譌䣹솁̘ : Log files done Backup: Trailing config done Backup: MBC done BackupDatabase: Writing history records Writing backup history records BACKUP DATABASE successfully processed 314 pages in 251.106 seconds (0.009 MB/sec). BackupDatabase: Finished
Just to clarify the entries above, starting with, what I'm guessing are Chinese characters, only display once the backup completes. However, looking in the log file, up to the "Backup: MBC done" entry has the same timestamp as all the previous entries. "BackupDatabase: Writing history records" has a timestamp 4 minutes later.