I'm just recapping what we've done in case anyone wants to compare notes:

Upgrading the ODBC driver didn't help us at all. We are running Laserfiche 11.02 build 338 on Windows Server 2022 and SQL Server 2019 on Windows Server 2019.

Enabling Legacy Cardinality Estimation might have helped a little but didn't make the problem go away.

It does not seem to be a hardware issue as neither processors nor RAM are ever maxed out.
We tried indexing some fields in our DEV environment but that didn't seem to help either.

I will admit that in our case the repository that throws this error the most often has a very large number of metadata fields and a large number of documents. If any repository of ours was going to have a problem like this then it would be this one.

We tried carefully defining the column profiles based on templates so users don't search for metadata that isn't present in certain documents. Seemed to help a little at first but now not so much.

The errors come and go. The repository can also be searched via WebLink. There are a lot of wildcard searches performed via WebLink and I don't doubt that the desktop user searches might be timing out while the WebLink searches hog resources but I can't prove that. Again, strictly from a hardware point of view, the server never seems to be overtaxed.

We've tried doing SQL profiling to see if that would reveal anything useful. We identified one search in particular that was running for a long time and sent it to LFC Support to examine. We've had a ticket open with them on this for over a year about this.

Our DBAs do have jobs that do indexing maintenance on a weekly basis. They follow the recommendations of some well known SQL guru, but the name escapes me at the moment.

That's all I can recall off the top of my head.

-MB

I haven't seen any new comments on this thread from anyone. Wanted to check in and see if anyone has resolved it?

Adding here in case anyone else is seeing the error for user searches for existing repository with large dataset 

LaserFiche team recommended the below steps for further performance tuning

• Remove all columns from the Search View and test to see if error still occurs
• Rebuild (not recreate) all indexes
• Update Statistics
• Shrink transaction log
• Examples from this link can be used to rebuild indexes and update statistics - https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
• LaserFiche have recommended to apply Example B from this page (towards the bottom, copying it below for reference):

EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y'

Please post back if you get a resolution to the issue. 

We are seeing this as well.  Work around for us is to limit the search.  Example being to limit the depth of folders and not searching text.  If the search criteria is as narrow as possible, it won't error out.

I resolved this issue for one of our users by wiping out the account attributes. Once done, the search error went away.

I am having the same issue.. Has anyone found a solutions?

What is the final resolution for this, we tried deleting the attribute but still its happening. This started happening after we upgraded to V11

Some of our Laserfiche users getting 9008 error message while browsing or searching in Laserfiche.

Please advise

• what is the cause of this issue?
• how can we fix this issue?

Another thought...

I can more often than not reproduce the database error with a specific column viewing in the web client (as LF suggested with narrowing the columns down to find the offending one). The SQL server states this is the offending query (similar to the one @Oncur posted). But my query is different in that the offending column is an actual explicit join in the SQL statement (pvj27.prop_id=62 is the offending column). Why would the SQL statement get an explicit join to the column where the other columns do not? ...this is just a straight text field with nothing special in it.

select id,link,type,flags,sdctl,rfid,parentid,toc_uuid,toc_owner,isFrozen,recordState,hasacl,c0,c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16,c17,c18,c19,c20,c21,c22,c23,c24,c25,c26,c27 from (select toc.tocid as id,toc.linkto as link,toc.etype as type,toc.toc_flags as flags,toc.toc_sdctl as sdctl,toc.rfid as rfid,toc.parentid as parentid, toc.toc_uuid as toc_uuid, toc.toc_owner toc_owner,case when toc.tocid in (select tocid from frozen) then 1 else 0 end as isFrozen,case when toc.rfid is null then 0 when toc.isDispositioned = 1 then 4 when toc.iscutoff = 1 then 3 when toc.isclosed = 1 then 2 else 1 end as recordState,case when toc.toc_flags & 32 = 32 then 1 else 0 end as hasacl,case when linktoc.etype>=0 then 1 else 0 end as isfolder,toc.name as c0,toc.created as c1,toc.modified as c2,case linktoc.etype when -2 then linktoc.pagecount else null end as c3,linktoc.pset_id as c4,toc.tocid as c5,0 as c6,case when linktoc.etype = 0 and linktoc.rfid = linktoc.tocid then 320 when linktoc.etype = 0 and (linktoc.toc_flags &  1) = 1 then 257 when linktoc.etype = 0 then 256 when linktoc.etype = 1 then 384 when ((linktoc.edoc_mime IS NOT NULL AND linktoc.edoc_mime <> '') or (linktoc.edoc_ext IS NOT NULL AND linktoc.edoc_ext <> '')) and (linktoc.toc_flags &  1) = 1 then 17 when (linktoc.edoc_mime IS NOT NULL AND linktoc.edoc_mime <> '') or (linktoc.edoc_ext IS NOT NULL AND linktoc.edoc_ext <> '') then 16 when (linktoc.toc_flags &  1) = 1 then 1 else 0 end as c7,linktoc.vol_id as c8,linktoc.pset_id as c9,cast(null as varbinary(85)) as c10,toc.etype as c11,toc.linkto as c12,linktoc.etype as c13,linktoc.rfid as c14,case when exists (select * from frozen where frozen.tocid = linktoc.tocid) then 1 else 0 end as c15,linktoc.isdispositioned as c16,linktoc.pset_id as c17,toc.parentid as c18,linktoc.edoc_ext as c19,case when linktoc.edoc_storeid is null then null else case when linktoc.edoc_size is null then 0 else linktoc.edoc_size end end as c20,case when exists (select * from doc where doc.tocid = linktoc.tocid and doc.img_etag is not null and doc.pagenum >= 0) then 1 else 0 end as c21,case when (linktoc.toc_flags &  512) = 512 then 1 else 0 end as c22,case when (linktoc.toc_flags & 512 = 512 and linktoc.toc_flags & 1024 = 0) then (select top 1 ad.who from active_doc ad where ad.tocid = linktoc.tocid) else cast(null as varbinary(85)) end as c23,plock.owner  as c24,case when exists (select * from doc where doc.tocid = linktoc.tocid and doc.txt_etag is not null and doc.pagenum >= 0) then 1 else 0 end as c25,toc.name as c26,pvj27.str_val as c27, l.row_num from toc with (nolock) right outer join #tmplisting l on toc.tocid=l.tocid  left outer join toc linktoc   with (nolock)  on case when toc.etype = -1 then toc.linkto else toc.tocid end = linktoc.tocid left outer join (select plock.*, ROW_NUMBER() OVER (PARTITION BY tocid ORDER BY owner) AS rn from plock   with (nolock) ) plock on linktoc.tocid=plock.tocid and plock.rn = 1 left outer join propval pvj27 on pvj27.tocid=linktoc.tocid and pvj27.prop_id=62 and pvj27.pos=0 and (pvj27.pos2 is null or pvj27.pos2<=1) where ((toc.etype is null or ((toc.etype=-2 or toc.etype>=0) or (toc.etype = -1 and (linktoc.etype=-2 or linktoc.etype>=0)))))) as q1  where q1.row_num>=@P1 and q1.row_num<=@P2 order by q1.row_num

Has anyone made any progress with this issue?

I know upthread some people have said that there are particular column headings causing the issue in some cases. (I know headings that involve calculations such as the 'Total Document File Size' can cause it to happen.) In our case it is our 'Fiscal Year' field which is part of our Invoice template. I was wondering if there was anything peculiar about it that might cause the issue. Here is how the field is set up:

It's a "List" field which means it appears in a drop down box when doing searches. The 'Blank' option is checked. The field is used in four different templates.

One thing I noticed that is probably 'bad form' is that we left the Width of the field at 40 when it obviously doesn't need to be that long.

The other thing I noticed is that all the values are very similar to each other: They are always 201X or 202X where X is the last digit in the year. Could that matter?

The error pops up when browsing the repository but sometimes it takes a couple of tries. Even when the error doesn't pop up, searching feels very slow. If I remove the 'Fiscal Year' column then suddenly all searches are lightning fast. Almost instantaneous.

Document counts by template:

BOA Claim - 1330

Invoice - 43,480

Legal - 4460

Travel - 1397

So, that's a total of 50,667 documents. Not an astronomical number for the documents that use these four templates.

Does anybody see any commonalities between the fields that throw the error for them and our 'Fiscal Year' field here?

Please open a support case.

Here as well. If there is a solution, please post it!

My server is LF 11 and I've tried both the 11 and 10.4 clients. At the bottom of the error in the event log on the server (after all the query text) I see the timeout is set to 15 seconds?

...order by q1.row_num"; Timeout=15(sec).

I know I'm doing a query that might take a little time. I'm asking the search engine to traverse 636 folders and inspect 14,482 documents with 179,620 pages and return to me any that are over a certain size (or even worse, greater than nn and less than nnn!). 

But I'd rather not break it up into smaller chunks of search. I'm ok with it taking a minute or two.

Steve

Anyone know what the 9008 error specifies? Removing all attributes on the user only makes the error go away temporarily by reducing the amount of data displayed on the screen, for example extra columns.

I can not find anyone who knows what this error specifies, is it a SQL timeout?

In our case it was the Notes column... Removed that and it was fast again

It's 2023 and we're seeing the same thing. Did anyone try upgrading to SQL Server 2022? Did that improve things?

This error is still on the go. It happens when users using the quick search. Doesn't happen all the time, but occasionally. Since we updated LF 11, this error randomly appears. We opened a support case with our provider but there is no tangible solution to it. 

This is what it shows on the error log;

"Timed out when executing an SQL query.

Session ID=186968; Dialog ID=20989173; Repository="PFC"; Function=SysEntryOpenListingStreamEx; Message="[Microsoft][ODBC Driver 11 for SQL Server]Query timeout expired"; Statement="select id,link,type,flags,sdctl,rfid,parentid,toc_uuid,toc_owner,isFrozen,recordState,hasacl,c0,c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16,c17,c18,c19,c20,c21,c22,c23,c24,c25,c26,c27,c28,c29,c30,c30_count,c31,c31_count,c32,c33,c34,c35,c36,c37,c38,c39,c40,c41,c42,c43,c43_count,c44,c45,c46,c47,c47_count,c48 from (select toc.tocid as id,toc.linkto as link,toc.etype as type,toc.toc_flags as flags,toc.toc_sdctl as sdctl,toc.rfid as rfid,toc.parentid as parentid, toc.toc_uuid as toc_uuid, toc.toc_owner toc_owner,case when toc.tocid in (select tocid from frozen) then 1 else 0 end as isFrozen,case when toc.rfid is null then 0 when toc.isDispositioned = 1 then 4 when toc.iscutoff = 1 then 3 when toc.isclosed = 1 then 2 else 1 end as recordState,case when toc.toc_flags & 32 = 32 then 1 else 0 end as hasacl,case when linktoc.etype>=0 then 1 else 0 end as isfolder,toc.tocid as c0,toc.name as c1,linktoc.edoc_ext as c2,toc.parentid as c3,toc.etype as c4,case when linktoc.edoc_storeid is null then null else case when linktoc.edoc_size is null then 0 else linktoc.edoc_size end end as c5,linktoc.etype as c6,toc.etag as c7,toc.linkto as c8,case when linktoc.etype = 0 and linktoc.rfid = linktoc.tocid then 320 when linktoc.etype = 0 and (linktoc.toc_flags &  1) = 1 then 257 when linktoc.etype = 0 then 256 when linktoc.etype = 1 then 384 when ((linktoc.edoc_mime IS NOT NULL AND linktoc.edoc_mime <> '') or (linktoc.edoc_ext IS NOT NULL AND linktoc.edoc_ext <> '')) and (linktoc.toc_flags &  1) = 1 then 17 when (linktoc.edoc_mime IS NOT NULL AND linktoc.edoc_mime <> '') or (linktoc.edoc_ext IS NOT NULL AND linktoc.edoc_ext <> '') then 16 when (linktoc.toc_flags &  1) = 1 then 1 else 0 end as c9,toc.created as c10,case linktoc.etype when -2 then linktoc.pagecount else null end as c11,linktoc.vol_id as c12,linktoc.pset_id as c13,(select hc.hitcount from searchresult12 hc where hc.rsid = sr.rsid  and hc.tocid = toc.tocid) as c14,toc.name as c15,0 as c16,1 as c17,toc.toc_uuid as c18,plock.owner  as c19,cast(null as varbinary(85)) as c20,0 as c21,case when linktoc.final_date is not null or linktoc.isdispositioned = 0 then linktoc.final_date "

I really hope it would be solved one day

I upgraded our development repository yesterday from 10.3.1.479 to 11.0.2305.274 which is the latest version 11 available for download. I too am getting the General Database Error 9008. Before I start trying random things from this article, I wanted to post here and check.

Has there been any final resolution on this problem? 

And, do all of the improvements from this patch: List of Changes for Laserfiche 10.3.1 Update 2 - Knowledge Base exist in Version 11? That patch seems to have a lot of search related fixes.

I am getting this issue as well. 

Hello Team,

Any solution for timeout issue while we use quick search.

Timed out when executing an SQL query. Session ID=519077; Dialog ID=8925961; Repository="HFRD"; Function=SysEntryOpenListingStreamEx; Message="[Microsoft][ODBC Driver 11 for SQL Server]Query timeout expired"; Statement="select id,link,type,flags,sdctl,rfid,parentid,toc_uuid,toc_owner,isFrozen,recordState,hasacl,c0,c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16,c17,c18,c19,c20,c21,c22,c23,c24,c25,c26,c27,c28,c29 from (select toc.tocid as id,toc.linkto as link,toc.etype as type,toc.toc_flags as flags,toc.toc_sdctl as sdctl,toc.rfid as rfid,toc.parentid as parentid, toc.toc_uuid as toc_uuid, toc.toc_owner toc_owner,case when toc.tocid in (select tocid from frozen) then 1 else 0 end as isFrozen,case when toc.rfid is null then 0 when toc.isDispositioned = 1 then 4 when toc.iscutoff = 1 then 3 when toc.isclosed = 1 then 2 else 1 end as recordState,case when toc.toc_flags & 32 = 32 then 1 else 0 end as hasacl,case when linktoc.etype>=0 then 1 else 0 end as isfolder,toc.tocid as c0,toc.name as c1,linktoc.edoc_ext as c2,toc.parentid as c3,toc.etype as c4,case when linktoc.edoc_storeid is null then null else case when linktoc.edoc_size is null then 0 else linktoc.edoc_size end end as c5,linktoc.etype as c6,toc.etag as c7,linktoc.pset_id as c8,toc.linkto as c9,case when linktoc.etype = 0 and linktoc.rfid = linktoc.tocid then 320 when linktoc.etype = 0 and (linktoc.toc_flags &  1) = 1 then 257 when linktoc.etype = 0 then 256 when linktoc.etype = 1 then 384 when ((linktoc.edoc_mime IS NOT NULL AND linktoc.edoc_mime <> '') or (linktoc.edoc_ext IS NOT NULL AND linktoc.edoc_ext <> '')) and (linktoc.toc_flags &  1) = 1 then 17 when (linktoc.edoc_mime IS NOT NULL AND linktoc.edoc_mime <> '') or (linktoc.edoc_ext IS NOT NULL AND linktoc.edoc_ext <> '') then 16 when (linktoc.toc_flags &  1) = 1 then 1 else 0 end as c10,toc.created as c11,toc.modified as c12,case linktoc.etype when -2 then linktoc.pagecount else null end as c13,linktoc.vol_id as c14,linktoc.pset_id as c15,toc.name as c16,0 as c17,1 as c18,toc.toc_uuid as c19,plock.owner  as c20,cast(null as varbinary(85)) as c21,0 as c22,case when linktoc.final_date is not null or linktoc.isdispositioned = 0 then linktoc.final_date else rf_toc.final_date end as c23,(case when linktoc.edoc_storeid is null then 0 else (case when linktoc.edoc_size is null then 0 else linktoc.edoc_size end) end) + (select isnull(sizetemp1.sum1, 0) + isnull(sizetemp2.sum2, 0) + isnull(sizetemp3.sum3, 0) from (select sum(cast(img_size as bigint)) + sum(cast(txt_size as bigint)) + sum(cast(loc_size as bigint)) as sum1 from doc where pagenum >= 0 and linktoc.tocid = doc.tocid) sizetemp1, (select sum(cast(altedoc_size as bigint)) as sum2 from altedoc where altedoc.tocid = linktoc.tocid) sizetemp2, (select isnull(sum(cast(datalength(ann.ann_text) as bigint)), 0) + isnull(sum(cast(ann.attach_size as bigint)),0) + isnull(sum(cast(datalength(ann.bitmap) as bigint)), 0) as sum3 from ann where ann.page_id in (select page_id from doc where doc.tocid = linktoc.tocid and doc.pagenum >= 0)) sizetemp3) as c24,case when (linktoc.toc_flags &  512) = 512 then 1 else 0 end as c25,case when ((linktoc.toc_flags &  512) = 512 and (linktoc.toc_flags &  1024) = 0) then 1 else 0 end as c26,case when (linktoc.toc_flags & 512 = 512 and linktoc.toc_flags & 1024 = 0) then (select top 1 ad.who from active_doc ad where ad.tocid = linktoc.tocid) else cast(null as varbinary(85)) end as c27,linktoc.isdispositioned as c28,linktoc.pset_id as c29, l.row_num from searchresult13 sr inner loop join toc on toc.tocid = sr.tocid and sr.rsid = :rsetid<int>  right outer join #tmplisting l on toc.tocid=l.tocid  left outer join toc linktoc   with (nolock)  on case when toc.etype = -1 then toc.linkto else toc.tocid end = linktoc.tocid left outer join toc rf_toc   with (nolock)  on linktoc.rfid = rf_toc.tocid left outer join (select plock.*, ROW_NUMBER() OVER (PARTITION BY tocid ORDER BY owner) AS rn from plock   with (nolock) ) plock on linktoc.tocid=plock.tocid and plock.rn = 1 where ((toc.etype is null or ((toc.etype=-2 or toc.etype>=0) or (toc.etype = -1 and (linktoc.etype=-2 or linktoc.etype>=0)))))) as q1  where q1.row_num>=:begin<int> and q1.row_num<=:end<int> order by q1.row_num"; Timeout=15(sec).

Thanks,

Pratik

I have 2 customers experiencing this issue also. upgrading to ODBC 17 did not help.  This issue seems to happen when there is nothing chosen in the search fields.  So if your were just searching for all documents by template the search would error out, but if you selected a field on the template and put in some information, the search completes.