Is Windows 10 defrag safe?

[Breck_Carter]

As far as I can tell, the Windows 10 Version 1903 (build 10.0.18362) "Optimize Drives" utility is safely running an "open-file defragmentation" process on a running SQL Anywhere 17.0.9.4882 database.

The DB_PROPERTY ( 'DBFileFragments' ) value dropped from 195 to 27 during a period when (I believe) only Windows 10 "Optimize Drives" ran.

AFAIK the PCMatic defragment process didn't run during the same period, and the Condusiv Diskeeper utility was previously uninstalled... so it's gotta be Windows 🙂

The SQL Anywhere database in question is 12G in size, and it is steadily busy 24x7, growing up to 1G per day, inserts only, no deletes, no REBUILD or unload/reload.

I haven't found any recent authoritative documentation closer than this...

Defragmenting SQL Server database disk drives 2020-12-02 https://docs.microsoft.com/en-ca/troubleshoot/sql/admin/defragmenting-database-disk-drives

Open-file defragmentation raises several possible issues that closed-file defragmentation typically does not:

Open-file defragmenting affects performance. Defragmentation utilities may lock sections of the file, preventing SQL Server from completing a read or write operation. This may affect the concurrency of the server that's running SQL Server. Contact the manufacturer of the defragmentation tool to learn how files are locked and how this could affect SQL Server concurrency.

Open-file defragmenting can affect write caching and ordering. Open-file-based utilities require I/O path components; these components must not change the ordering or intended nature of the write operation. If the write-through or WAL protocol tenants are broken, database damage is likely to occur. The database and all associated files are considered to be a single entity. (This is covered in many Microsoft Knowledge Base articles, SQL Server Books Online, and various white papers.) All writes must retain the original write-ordering sequences and write-through capabilities.

Windows 10 Version 1903 (build 10.0.18362)
SQL Anywhere dbsrv17.exe and file version 17.0.9.4882
Windows "Optimize Drives" enabled, run weekly, last run C: 12/11/2020 8:03 PM OK (0% fragmented)
=====
SELECT @@VERSION, DB_PROPERTY ( 'DBFileFragments' ), PROPERTY ( 'StartTime' );
'17.0.9.4882','27','2020-12-07 11:11:43.183'
=====
Run   Purge Started           Run Time    Progress / Status       Rows        File  Free  Frags
7   Dec 12 2020 9:40:26 AM  39.7s   Purge done / Completed  0   -   12G     0%  27
  *** Dec 11 2020 8:03    PM  Windows "Optimize Drives" run
6   Dec 11 2020 9:30:56 AM  1m 17s  Purge done / Completed  0   -   11G     0%  195
  *** Dec 07                  SQL Anywhere restarted 
5   Nov 29 2020 7:55:28 AM  32.4s   Purge done / Completed  0   -   6.9G    0%  83
4   Nov 24 2020 6:00:41 AM  12.8s   Purge done / Completed  0   -   5.4G    1%  44
3   Nov 22 2020 9:08:06 AM  10.3s   Purge done / Completed  0   -   4.9G    1%  116
2   Nov 20 2020 9:09:16 AM  1m 3.9s     Purge done / Completed  0   -   4.2G    0%  4
1   Nov 10 2020 7:42:35 AM  12.3s   Purge done / Completed  0   -   978M    1%  156

[Breck_Carter]

FWIW the dbsrv17 -m option is in effect, meaning the log file is truncated with every checkpoint.

The dbsrv17 command line is this... "C:\\Program Files\\SQL Anywhere 17\\Bin64\\dbsrv17.exe" -c 25p -ch 50p -cr- -gk all -gl all -gn 220 -gna 0 -m -n foxhound5 -o foxhound5_debug.txt -oe foxhound5_debug_startup.txt -on 1M -sb 0 -ufd restart -x tcpip -xd -xs http(port=80;maxsize=0;to=600;kto=600) foxhound5.db -n f "-hn0,16464:504"