One of the classics as far as best practices is concerned is to exclude SQL Server files and folders on your antivirus programs running on the server. We all know this statements and often I see DBAs not adding any extra care to this recommendation. There are not many blogs that call them out explicitly.
Although antivirus software is a very important part of security planning, it is important to understand the effect it has on SQL Server files. After rebooting a server, if the antivirus software locks a SQL Server file before SQL Server can gain access to that file, potential issues ranging from SQL Server not being able access those particular files to possible database corruption may occur. In a number of cases I have seen SQL Server refusing to start sometimes or the CPU / IO seems to be stalling sometimes. As you can see there are a number of these random errors one might get because of not implementing a simple best practice. Therefore, it is recommended that the following files be excluded from all antivirus scans:
- SQL Server data files (typical extension is .mdf, .ndf, and .ldf)
- SQL Server backup files (typical extension is .bak and .trn)
- Full-text catalog files
- Trace files (typical extension is .trc)
- SQL Audit files
- SQL query files (typical extension is .sql)
- Directories holding Analysis Services data and partitions
- The directory that holds Analysis Services temporary files used during processing
- Analysis Services backup files
In addition to excluding SQL Server and Analysis Services files, it is recommended to exclude the following list of processes from antivirus scans:
- SQLServr .exe
- ReportingServicesService .exe
- MSMDSrv .exe
For environments where SQL Server is clustered, exclude the C:\Windows\Cluster directory and the Quorum drive.
Though this list is not exhaustive, I am sure there might be a few more that I might have missed. This was a humble shot at this topic and I am sure as seasoned DBA’s you might have a little more to include. Do let me know under the comments section and I am sure this can be a great source of reference for others searching on this topic too.
Additional resources here.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
14 Comments. Leave new
see Microsoft SQL Server KB:
How to choose antivirus software to run on computers that are running SQL Server
https://support.microsoft.com/en-us/help/309422/choosing-antivirus-software-for-computers-that-run-sql-server
Thanks for sharing that Daniel.
Awesome, that is cool collection. Thanks Daniel.
when you give the argument as integer or double decimal then month function of the SQL will count the days and give the month according to the days.
so if you do SELECT MONTH(60) then it will return 3 because days is 60 so 31 of January and 28 of feb Total 58 of 60 will be the 3rd month same if you give just SELECT MONTH(59.11) same result will be there 3 but if you give SELECT MONTH(10) then it will give 1st month since year’s 10th day is first month. now MONTH(18/200) is 0.09 of the it is the first month and same for the 200/18 is 11.11111111111111 then it is 11th day of year so it will return 1st month.
What if virus effect the files which are not protected :-)
We have received a request from customer that disbaled the antivirus from Database cluster server(Active\passive).
“The anti-virus can lock up the SQL Server in much the same manner as the snapshots.
Unfortunately we can see the issue even when disabling this. The only way to completely take this out of the picture is to temporarily uninstall it from the SQL Servers. I understand that this is production servers,
and this would be very temporary until the issue either happens again, or we are comfortable that it isn’t happening with this removed.”
Could you please Confirm if the antivirus software locks a SQL Server files.?
Should i suggest the customer to excluded from all antivirus scans as mentioned pinal dave.
I hope you will be able to provide the information.
@Pinal dave: Waiting for guidance\suggestions on this.Please
I appreciate that this article is a bit old, but I feel that I have to say…when you use other people’s work, it is good practice to cite where you got the info from. It saves from the accusation of plagerism being laid at you – ie, your list is obviously taken from…
https://support.microsoft.com/en-us/help/309422/choosing-antivirus-software-for-computers-that-run-sql-server
…or from someone else who has quoted this list.
Not the first time I’ve come across things you’ve written that can be found elsewhere with earlier timestamps.
Any experience or recommendations using Palo Alto TRAPS scanning SQL Servers?
don’t forget about *.etl files for extended events
Are there any updates for this exclusion list to include things like R services and other new features of SQL Server?
I experienced an issue today where TrendMicro Office Scan was preventing R services from starting.
maybe we should also exclude sqlagent.exe ?