# Query during backup takes too long, does not finish

**URL:** <https://forum.duplicati.com/t/query-during-backup-takes-too-long-does-not-finish/13416>\
**Category:** Support\
**Created:** [November 18, 2021, 2:16pm UTC](https://forum.duplicati.com/t/query-during-backup-takes-too-long-does-not-finish/13416 "2021-11-18T14:16:25Z")\
**Posts on this page:** 1\
**Showing post:** 12

<div class="post-metadata">

**Author:** ![ts678](https://forum.duplicati.com/letter_avatar_proxy/v4/letter/t/8491ac/32.png) [@ts678](https://forum.duplicati.com/u/ts678)\
**Post date:** [December 3, 2021, 1:52pm UTC](https://forum.duplicati.com/t/query-during-backup-takes-too-long-does-not-finish/13416/12 "2021-12-03T13:52:49Z")

</div>

> [@Joel\_Harris](#):
>
> I haven’t killed it yet. It is still running while I hope for a resolution.

AFAIK there’s no way to force it to finish. The main reason to keep it up is to look at it more (see below) but the main drawback of it being up is I think you won’t even be able to copy the [database](https://duplicati.readthedocs.io/en/latest/03-using-the-graphical-user-interface/#database-management) to preserve its data. You can try, using whatever tools you can get. Also see if you can copy any file with similar name and with different suffix, such as `-journal`. If you can’t copy, maybe you can at least get the timestamp from them. That would be a quick way to see if you’re making any changes, although the slow query seems like reads.

I’m going to ask if @rsevero has any ideas on what to do while it’s up. After it’s down, we can try to grab a database bug report to look at, but it might take some work to get one that actually shows the slowness. Fortunately, knowing the query can sometimes find it in source, and I think this one might be requested on demand, or avoidable for testing purposes so that second backup can finish so a bug report can be made.

> [@Joel\_Harris](#):
>
> I/O Read Bytes at 23785.9 GB.

I have had a query running for about 17 days on a DB bug report from [here](https://forum.duplicati.com/t/why-the-heck-cant-we-change-the-blocksize/13120/55) that’s reading about 1 GB every five seconds (which is far above the drive’s abilities, but it acts like Windows is supplying data from cache). Currently at 218953 GB which is a ballpark match for calculated value based on duration and rate estimate.

Watching the process (which is actually [DB Browser for SQLite](https://sqlitebrowser.org/) because this is a manual test of the query) using [Sysinternals Process Monitor](https://docs.microsoft.com/en-us/sysinternals/downloads/procmon) shows it doing a huge read rate from the DB. If I filter out the `ReadFile` operations there’s nothing else going on to the drive, which rules against the thought of [temporary file](https://www.sqlite.org/tempfiles.html) write. Storing small results in memory is hard to rule out, but the commit size isn’t exploding. Others were larger.

Reads are always 4 KB which is the SQLite default page size. The access pattern is sequential with gaps. I’ve been looking to see if there’s any way to tell exactly what it’s doing, but I’m not sure SQLite offers any…

Process Explorer thread view shows the obvious (for DB Browser). The busy thread has SQLite on stack. You could check yours to see if it has a similar view, although that’s suggested by your non-finishing query.

Although I couldn’t find any other reports of this query not finishing (despite knowing multi-TB backups and multi-million file backups sometimes work, according to forum reports), this query did show erratic speed:

[Time to complete backup job has trippled in the last week](https://forum.duplicati.com/t/time-to-complete-backup-job-has-trippled-in-the-last-week/12878)  
with a quick me-too (and some experiments with trying to see if a page size boost could speed things) at:  
[Backups suddenly taking much longer to complete](https://forum.duplicati.com/t/backups-suddenly-taking-much-longer-to-complete/12994)

There were a few other reports, which one can find with a query on parts of the query (I tried with Google).

One strange thing is I think this code (which I think is [here](https://github.com/duplicati/duplicati/blob/a4a02ff884d59a964a0a9d9cdc3945db0560c8e6/Duplicati/Library/Main/Database/LocalDatabase.cs#L797-L803)) was probably run at the end of the first backup. Second backup would run it at start, then add FilesetID=2 as its work, then get stuck at end (right?). Why?

I’m not an SQL (or SQLite) expert, but [The SQLite Query Optimizer Overview](https://www.sqlite.org/optoverview.html) explains how the method of getting to the answer can be hugely varied, and SQLite tries to pick a good one. Maybe sometimes it fails?

There are apparently combinations of data and query that can loop, but I don’t know if this can do it. Query:

```plaintext
SELECT COUNT(*)
FROM (
	SELECT DISTINCT "Path"
	FROM (
		SELECT "L"."Path"
			,"L"."Lastmodified"
			,"L"."Filelength"
			,"L"."Filehash"
			,"L"."Metahash"
			,"L"."Metalength"
			,"L"."BlocklistHash"
			,"L"."FirstBlockHash"
			,"L"."FirstBlockSize"
			,"L"."FirstMetaBlockHash"
			,"L"."FirstMetaBlockSize"
			,"M"."Hash" AS "MetaBlocklistHash"
		FROM (
			SELECT "J"."Path"
				,"J"."Lastmodified"
				,"J"."Filelength"
				,"J"."Filehash"
				,"J"."Metahash"
				,"J"."Metalength"
				,"K"."Hash" AS "BlocklistHash"
				,"J"."FirstBlockHash"
				,"J"."FirstBlockSize"
				,"J"."FirstMetaBlockHash"
				,"J"."FirstMetaBlockSize"
				,"J"."MetablocksetID"
			FROM (
				SELECT "A"."Path" AS "Path"
					,"D"."Lastmodified" AS "Lastmodified"
					,"B"."Length" AS "Filelength"
					,"B"."FullHash" AS "Filehash"
					,"E"."FullHash" AS "Metahash"
					,"E"."Length" AS "Metalength"
					,"A"."BlocksetID" AS "BlocksetID"
					,"F"."Hash" AS "FirstBlockHash"
					,"F"."Size" AS "FirstBlockSize"
					,"H"."Hash" AS "FirstMetaBlockHash"
					,"H"."Size" AS "FirstMetaBlockSize"
					,"C"."BlocksetID" AS "MetablocksetID"
				FROM "File" A
				LEFT JOIN "Blockset" B ON "A"."BlocksetID" = "B"."ID"
				LEFT JOIN "Metadataset" C ON "A"."MetadataID" = "C"."ID"
				LEFT JOIN "FilesetEntry" D ON "A"."ID" = "D"."FileID"
				LEFT JOIN "Blockset" E ON "E"."ID" = "C"."BlocksetID"
				LEFT JOIN "BlocksetEntry" G ON "B"."ID" = "G"."BlocksetID"
				LEFT JOIN "Block" F ON "G"."BlockID" = "F"."ID"
				LEFT JOIN "BlocksetEntry" I ON "E"."ID" = "I"."BlocksetID"
				LEFT JOIN "Block" H ON "I"."BlockID" = "H"."ID"
				WHERE "A"."BlocksetId" >= 0
					AND "D"."FilesetID" = 2
					AND (
						"I"."Index" = 0
						OR "I"."Index" IS NULL
						)
					AND (
						"G"."Index" = 0
						OR "G"."Index" IS NULL
						)
				) J
			LEFT OUTER JOIN "BlocklistHash" K ON "K"."BlocksetID" = "J"."BlocksetID"
			ORDER BY "J"."Path"
				,"K"."Index"
			) L
		LEFT OUTER JOIN "BlocklistHash" M ON "M"."BlocksetID" = "L"."MetablocksetID"
		)
	
	UNION
	
	SELECT DISTINCT "Path"
	FROM (
		SELECT "G"."BlocksetID"
			,"G"."ID"
			,"G"."Path"
			,"G"."Length"
			,"G"."FullHash"
			,"G"."Lastmodified"
			,"G"."FirstMetaBlockHash"
			,"H"."Hash" AS "MetablocklistHash"
		FROM (
			SELECT "B"."BlocksetID"
				,"B"."ID"
				,"B"."Path"
				,"D"."Length"
				,"D"."FullHash"
				,"A"."Lastmodified"
				,"F"."Hash" AS "FirstMetaBlockHash"
				,"C"."BlocksetID" AS "MetaBlocksetID"
			FROM "FilesetEntry" A
				,"File" B
				,"Metadataset" C
				,"Blockset" D
				,"BlocksetEntry" E
				,"Block" F
			WHERE "A"."FileID" = "B"."ID"
				AND "B"."MetadataID" = "C"."ID"
				AND "C"."BlocksetID" = "D"."ID"
				AND "E"."BlocksetID" = "C"."BlocksetID"
				AND "E"."BlockID" = "F"."ID"
				AND "E"."Index" = 0
				AND (
					"B"."BlocksetID" = - 100
					OR "B"."BlocksetID" = - 200
					)
				AND "A"."FilesetID" = 2
			) G
		LEFT OUTER JOIN "BlocklistHash" H ON "H"."BlocksetID" = "G"."MetaBlocksetID"
		ORDER BY "G"."Path"
			,"H"."Index"
		)
	)

```

---

_[View the full topic](https://forum.duplicati.com/t/query-during-backup-takes-too-long-does-not-finish/13416)._