Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

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 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

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
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' );
'','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

asked 12 Dec '20, 10:53

Breck%20Carter's gravatar image

Breck Carter
accept rate: 20%

edited 12 Dec '20, 16:38

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"

(12 Dec '20, 16:37) Breck Carter
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 12 Dec '20, 10:53

question was seen: 916 times

last updated: 12 Dec '20, 16:38

Related questions