0

We are having WSFC(Windows Server Failover Cluster) configuration for one of database server and from last few days, backup of one of the database is failing. Sometime backup itself fails whereas on other times restore verify from disk fails.

I am using Ola Hallengren's maintenance script and we have checkdb weekly running for all the database and no issue is reported in that, one time I executed them manually - checkdb as well as backup finished successfully. Did fail-over one time and that day it worked fine however from next day onward its throwing same error. Differential backup just works fine.

Restore verify error is as below:

Command: RESTORE VERIFYONLY FROM DISK = N'Drive_name\Server_name\DB_Name\FULL\Server_Name_DB_Name_FULL_20191021_003022.bak' WITH CHECKSUM
Msg 3203, Level 16, State 1, Server Server_Name, Line 1
Read on "Drive_name\Server_name\DB_Name\FULL\Server_Name_DB_Name_FULL_20191021_003022.bak" failed: 170(The requested resource is in use.)
Msg 3013, Level 16, State 1, Server KHCBSPSQL, Line 1
VERIFY DATABASE is terminating abnormally.
Outcome: Failed
Duration: 00:05:13
Date and time: 2019-10-21 00:46:23

Backup error is as below:

Command: BACKUP DATABASE [DB_Name] TO DISK = N'Drive_name\Server_name\DB_Name\FULL\Server_Name_DB_Name_FULL_20191016_003016.bak' WITH CHECKSUM, COMPRESSION
Msg 3203, Level 16, State 1, Server Server_Name, Line 1
Read on "Drive_Name\MSSQL\DATA\DB_Name.mdf" failed: 170(The requested resource is in use.)
Msg 3202, Level 16, State 1, Server Server_Name, Line 1
Write on "Drive_name\Server_name\DB_Name\FULL\Server_Name_DB_Name_FULL_20191016_003016.bak" failed: 170(The requested resource is in use.)
Msg 3013, Level 16, State 1, Server Server_Name, Line 1
BACKUP DATABASE is terminating abnormally.
Outcome: Failed
Duration: 00:09:19
Date and time: 2019-10-16 00:39:35

I am suspecting that these files are used by some anti-virus running on server or some scanner is holding these files however I am not sure why only this specific database is failing. There are more than 20 databases and their backup runs smoothly, also to note that this is the largest one and approximately 100GB. Any help is much appreciated.

Version is as below:

Server: Windows Server 2012 Standard

SQL Server: Microsoft SQL Server 2012 (SP4-GDR) (KB4057116) - 11.0.7462.6 (X64) Jan 5 2018 22:11:56 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.2 (Build 9200: ) (Hypervisor)

UPDATE: Copied this backup to test server and restored the same on a new database and restoration completed successfully without any issue.

Executed the backup manually without checksum and backup completed without any error however when it runs with checksum, throws same error as provided above.

Sysadmin has excluded mdf, ldf, ndf and bak type files from live scanning.

Improve this question
6
  • 3
    Can you replicate the issue? You could use sysinternals process explorer to see what application has an open handle on the file
    – Randi Vertongen
    Commented Oct 21, 2019 at 6:57
  • @RandiVertongen Thank you for reply. I am not an sysadmin on the server and this is a production server, can run sysinternals only when sysadmins give me permission to do so. I want to rule-out any issue from DB side before going to that step.
    – Learning_DBAdmin
    Commented Oct 21, 2019 at 7:05
  • Alright, the commands are failing after 5/9 minutes which seems that something is interupting the backup / restore process. Are you backing up to a disk on the vm itself? A restore headeronly or a restore filelistonly works right?
    – Randi Vertongen
    Commented Oct 21, 2019 at 8:44
  • In the normal scenario(when it wasn't failing), restore verify was completing in 7 minutes and backup was completing in 11 mins for this database. I am taking backup at a disk on the vm which is part of cluster and shared disk. I am copying this backup to test now and will see if restoration completes successfully or not.
    – Learning_DBAdmin
    Commented Oct 21, 2019 at 8:49
  • Is it a shared disk in a Always On FCI setup? Have you tried backing up to a network share instead?
    – Randi Vertongen
    Commented Oct 21, 2019 at 8:56

0

Reset to default

Your Answer

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