Why does antivirus stops I/O and SQL sees it as a full backup, during AV check

Question

Why does antivirus stops I/O and SQL sees it as a full backup, during AV check

peter2501

SSChasing Mays

Points: 601
More actions
September 28, 2022 at 11:58 am

#4092903

Hello,
I have noticed that some AV's do freeze disk I/O to the databases and in the SQL logs it says it is running a full backup.
Does that impact the structure of my manual setup database backups, for Full -- Differentials -- Log backups
example of backups where the first backup is done via third party, but the other backups are just short disruption, but still displayed as Full backup.
backup_start_date backup_finish_date totaltime backup_type Size_in_MB physical_device_name
2022-09-27 21:03:55.000 2022-09-27 21:46:06.000 00:42:11 Full 647840.81 20597e31-e705-420f-8ff8-a87da2ad30b9
2022-09-28 00:18:55.000 2022-09-28 00:18:57.000 00:00:02 Full 647837.41 {26FF78CC-1A6E-4EED-B50F-D29C7B6BA5BF}6
2022-09-28 04:19:03.000 2022-09-28 04:19:05.000 00:00:02 Full 647837.59 {9152C1F2-68C4-4901-B9D8-08AABCC6869E}6
2022-09-28 04:40:21.000 2022-09-28 04:40:23.000 00:00:02 Full 647837.58 {9E0390FC-E644-472A-B439-705B49C6EBE1}6
2022-09-28 08:19:10.000 2022-09-28 08:19:12.000 00:00:02 Full 647837.84 {DBB92D5C-1C8F-47B3-A995-9BC2F114F3C1}6

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

durai nagarajan SSCoach Points: 15778 More actions · Answer 1

I dont know about AV working structure.

IS AV excluded to scan mdf, ndf, ldf , bak and trn files or respective backup files along with program file\ Microsoft SQL server and data folders etc.

Is error happening post the second line settings done?.

Regards
Durai Nagarajan

peter2501 SSChasing Mays Points: 601 More actions · Answer 2

Well, it is not really an error, it just freezes the disks to take a snapshot, or a shadow copy or something.

The list I showed has the first line having a normal backup, which takes 42 minutes.

And the others have just a 1 or 2 seconds full backup. which indicates the freese of the I/O so the snapshot can be taken.

I know it is just a brief disruption in the database access, but it gives me the feeling a backup is taken, placed somewhere, and SQL sees it.

But then again, it does not look like a real backup, as I don't know where the backup files are. And basically I don't want this to happen anyways.

The disks are excluded from scanning, which does not help either.

And, looking at the virtual location. The 42 minute backup job has a slightly different virtual name as the short ones, which have ( ) and a number at the end.

Peter

sterling3721 SSChasing Mays Points: 658 More actions · Answer 3

It's managed by your 3rd party SQL backup software, so, the backup software knows where it is, how to restore it when needed. It's almost the same for all 3rd party SQL Backup software vendors.

>>>but it gives me the feeling a backup is taken, placed somewhere, and SQL sees it. it does not look like a real backup, as I don't know where the backup files are.

If you rely on SQL native backup, you will know exactly where it is and restore anywhere you want.

>>>I don't want this to happen anyways.

peter2501 SSChasing Mays Points: 601 More actions · Answer 4

Hi,

That is the point. There is no external third party tool doing that backup.

Here another log of the backups done only via SQLwhere you can see that row 3 and 4 are having the diff and full backups via SQL.

totaltime backup_type Size_in_MB physical_device_name

00:00:01 Full 91330.56 {8A681751-ED1B-4575-992D-8F61E0DC2243}6

00:00:01 Full 91330.56 {E35E5ECF-E6A6-4897-8110-24F0F7AAB2EF}6

00:00:01 Differential 4.32 J:\Backups\1-daily\TST\TST_backup_2022_09

00:04:34 Full 91333.32 J:\Backups\2-weekly\TST\TST_backup_2022_09

00:00:01 Full 91330.57 {2F73A631-6730-4149-96F9-F57EB46C205D}6

00:00:01 Full 91330.56 {B1C72F15-1B1A-4809-9BA5-0FDF2FB96D5F}6

We noticed this VSS writer doing the DISK i/O freeze, but when we disabled the AV. the VSS writer also stops doing the freeze.

Therefor we believe it is the AV doing the freeze and "ghost" backup of the databases

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

Which Anti-Virus are you using, please?

And when you disabled the AV, did the odd entries for backups disappear, as well, or are they still appearing?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

peter2501 SSChasing Mays Points: 601 More actions · Answer 6

Hello,

We use Sentinel AV software. And yes, when disabled the entries for the FULL backups are gone as well.

Peter

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 7

peter2501 wrote:

Hello,
We use Sentinel AV software. And yes, when disabled the entries for the FULL backups are gone as well.
Peter

Thanks, Peter. Lordy... that's a bit crazy. Apologies for asking and stating the obvious, but I've never seen an AV do something like that before.

If there's nothing about this in the documentation for Sentinel, I'd contact the company and ask them your good question (and it IS a really good question). I would hope that it wouldn't affect anything to do with the backups you good folks setup but you know the old joke about "Hope in one hand and ....".

I would also ask them where their backup files "live" so that you can monitor that for disk usage. And, finally, ask them what these backups are useful and does the AV delete them according to some setting to preserve disk space.

Again, though... check the documentation first. Such information should be there, but who knows? If it's not, then contact them with a thoughtful list of questions, especially the one about why it "Stops I/O" and if there's any way around that. If the answer to that last question is "No", I'd get a different AV.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sterling3721 SSChasing Mays Points: 658 More actions · Answer 8

If this is confusing to you, it's probably confusing to your coworker, especially future coworker. If you cannot figure out how to avoid generating those fake backup rows, maybe it's the time to switch to a different AV vendor. My employer uses Sophos and I didn't see similar records.

peter2501 SSChasing Mays Points: 601 More actions · Answer 9

Thanks all so far, I still do not have any feedback from our AV provider,

But if I have an answer I will share it here as well.

Peter

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 10

peter2501 wrote:

Thanks all so far, I still do not have any feedback from our AV provider,
But if I have an answer I will share it here as well.
Peter

Aye. Thanks for the feedback, Peter. That sounds like another great reason to switch to another AV provider.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)