You are viewing limited content. For full access, please sign in.

Question

Question

What would be performing this query from the repository?

Laserfiche Version 11
Updated March 14
asked on March 14

I have been reviewing the event viewer logs for our Laserfiche Server and have been finding a lot of SQL query timeout errors in the \ContentRepository\Service\Admin log. One example of a query that is listed is the one below. Is this being done when someone is performing a search within the repository or is a system function?

Log Name:      Laserfiche-ContentRepository-Service/Admin
Source:        Laserfiche-ContentRepository-Service
Date:          3/13/2024 10:21:18 PM
Event ID:      197
Task Category: None
Level:         Error
Keywords:      
User:          xxxx
Computer:      xxxx
Description:
Timed out when executing an SQL query. Session ID=615712; Dialog ID=10876631; Repository="Financials"; Function=SysEntryOpenListingStreamEx; Message="[Microsoft][ODBC Driver 17 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,c32,c33,c34,c35,c36,c37,c38 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,toc.modified as c11,case linktoc.etype when -2 then linktoc.pagecount else null end as c12,linktoc.vol_id as c13,linktoc.pset_id as c14,(select p.path from searchresult6 p where p.rsid = sr.rsid  and p.tocid = toc.tocid) as c15,toc.name as c16,0 as c17,1 as c18,toc.toc_uuid as c19,isnull(creator_cache.friendly_name, creator_cache.account_name) as c20,plock.owner  as c21,cast(null as varbinary(85)) as c22,0 as c23,case when (linktoc.toc_flags &  512) = 512 then 1 else 0 end as c24,case when ((linktoc.toc_flags &  512) = 512 and (linktoc.toc_flags &  1024) = 0) then 1 else 0 end as c25,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 c26,linktoc.pset_id as c27,pvj28.str_val as c28,pvj29.str_val as c29,pvj30.str_val as c30,(select count(*) from propval where propval.tocid=linktoc.tocid and propval.prop_id=71)  as c30_count,pvj31.str_val as c31,pvj32.str_val as c32,pvj33.date_val as c33,pvj34.str_val as c34,pvj35.str_val as c35,pvj36.str_val as c36,pvj37.str_val as c37,pvj38.str_val as c38, l.row_num from searchresult6 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 account_cache creator_cache on toc.creator = creator_cache.account_sid 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 pvj28 on pvj28.tocid=linktoc.tocid and pvj28.prop_id=74 and pvj28.pos=0 and (pvj28.pos2 is null or pvj28.pos2<=1) left outer join propval pvj29 on pvj29.tocid=linktoc.tocid and pvj29.prop_id=73 and pvj29.pos=0 and (pvj29.pos2 is null or pvj29.pos2<=1) left outer join propval pvj30 on pvj30.tocid=linktoc.tocid and pvj30.prop_id=71 and pvj30.pos=0 and (pvj30.pos2 is null or pvj30.pos2<=1) left outer join propval pvj31 on pvj31.tocid=linktoc.tocid and pvj31.prop_id=115 and pvj31.pos=0 and (pvj31.pos2 is null or pvj31.pos2<=1) left outer join propval pvj32 on pvj32.tocid=linktoc.tocid and pvj32.prop_id=69 and pvj32.pos=0 and (pvj32.pos2 is null or pvj32.pos2<=1) left outer join propval pvj33 on pvj33.tocid=linktoc.tocid and pvj33.prop_id=67 and pvj33.pos=0 and (pvj33.pos2 is null or pvj33.pos2<=1) left outer join propval pvj34 on pvj34.tocid=linktoc.tocid and pvj34.prop_id=16 and pvj34.pos=0 and (pvj34.pos2 is null or pvj34.pos2<=1) left outer join propval pvj35 on pvj35.tocid=linktoc.tocid and pvj35.prop_id=75 and pvj35.pos=0 and (pvj35.pos2 is null or pvj35.pos2<=1) left outer join propval pvj36 on pvj36.tocid=linktoc.tocid and pvj36.prop_id=64 and pvj36.pos=0 and (pvj36.pos2 is null or pvj36.pos2<=1) left outer join propval pvj37 on pvj37.tocid=linktoc.tocid and pvj37.prop_id=66 and pvj37.pos=0 and (pvj37.pos2 is null or pvj37.pos2<=1) left outer join propval pvj38 on pvj38.tocid=linktoc.tocid and pvj38.prop_id=84 and pvj38.pos=0 and (pvj38.pos2 is null or pvj38.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>=:begin<int> and q1.row_num<=:end<int> order by q1.row_num"; Timeout=15(sec).
Event Xml:
<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
  <System>
    <Provider Name="Laserfiche-ContentRepository-Service" Guid="{82FC23FE-4B0A-435C-82E6-01C7077098AF}" />
    <EventID>197</EventID>
    <Version>0</Version>
    <Level>2</Level>
    <Task>0</Task>
    <Opcode>0</Opcode>
    <Keywords>0x4000000000000000</Keywords>
    <TimeCreated SystemTime="2024-03-14T05:21:18.789746100Z" />
    <EventRecordID>5081693</EventRecordID>
    <Correlation />
    <Execution ProcessID="8228" ThreadID="4884" />
    <Channel>Laserfiche-ContentRepository-Service/Admin</Channel>
    <Computer>xxxx</Computer>
    <Security UserID="xxxx" />
  </System>
  <EventData>
    <Data Name="sessionID">615712</Data>
    <Data Name="dialogID">10876631</Data>
    <Data Name="repository">Financials</Data>
    <Data Name="func">SysEntryOpenListingStreamEx</Data>
    <Data Name="message">[Microsoft][ODBC Driver 17 for SQL Server]Query timeout expired</Data>
    <Data Name="stmt">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,c32,c33,c34,c35,c36,c37,c38 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 &amp; 32 = 32 then 1 else 0 end as hasacl,case when linktoc.etype&gt;=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 &amp;  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 &lt;&gt; '') or (linktoc.edoc_ext IS NOT NULL AND linktoc.edoc_ext &lt;&gt; '')) and (linktoc.toc_flags &amp;  1) = 1 then 17 when (linktoc.edoc_mime IS NOT NULL AND linktoc.edoc_mime &lt;&gt; '') or (linktoc.edoc_ext IS NOT NULL AND linktoc.edoc_ext &lt;&gt; '') then 16 when (linktoc.toc_flags &amp;  1) = 1 then 1 else 0 end as c9,toc.created as c10,toc.modified as c11,case linktoc.etype when -2 then linktoc.pagecount else null end as c12,linktoc.vol_id as c13,linktoc.pset_id as c14,(select p.path from searchresult6 p where p.rsid = sr.rsid  and p.tocid = toc.tocid) as c15,toc.name as c16,0 as c17,1 as c18,toc.toc_uuid as c19,isnull(creator_cache.friendly_name, creator_cache.account_name) as c20,plock.owner  as c21,cast(null as varbinary(85)) as c22,0 as c23,case when (linktoc.toc_flags &amp;  512) = 512 then 1 else 0 end as c24,case when ((linktoc.toc_flags &amp;  512) = 512 and (linktoc.toc_flags &amp;  1024) = 0) then 1 else 0 end as c25,case when (linktoc.toc_flags &amp; 512 = 512 and linktoc.toc_flags &amp; 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 c26,linktoc.pset_id as c27,pvj28.str_val as c28,pvj29.str_val as c29,pvj30.str_val as c30,(select count(*) from propval where propval.tocid=linktoc.tocid and propval.prop_id=71)  as c30_count,pvj31.str_val as c31,pvj32.str_val as c32,pvj33.date_val as c33,pvj34.str_val as c34,pvj35.str_val as c35,pvj36.str_val as c36,pvj37.str_val as c37,pvj38.str_val as c38, l.row_num from searchresult6 sr inner loop join toc on toc.tocid = sr.tocid and sr.rsid = :rsetid&lt;int&gt;  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 account_cache creator_cache on toc.creator = creator_cache.account_sid 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 pvj28 on pvj28.tocid=linktoc.tocid and pvj28.prop_id=74 and pvj28.pos=0 and (pvj28.pos2 is null or pvj28.pos2&lt;=1) left outer join propval pvj29 on pvj29.tocid=linktoc.tocid and pvj29.prop_id=73 and pvj29.pos=0 and (pvj29.pos2 is null or pvj29.pos2&lt;=1) left outer join propval pvj30 on pvj30.tocid=linktoc.tocid and pvj30.prop_id=71 and pvj30.pos=0 and (pvj30.pos2 is null or pvj30.pos2&lt;=1) left outer join propval pvj31 on pvj31.tocid=linktoc.tocid and pvj31.prop_id=115 and pvj31.pos=0 and (pvj31.pos2 is null or pvj31.pos2&lt;=1) left outer join propval pvj32 on pvj32.tocid=linktoc.tocid and pvj32.prop_id=69 and pvj32.pos=0 and (pvj32.pos2 is null or pvj32.pos2&lt;=1) left outer join propval pvj33 on pvj33.tocid=linktoc.tocid and pvj33.prop_id=67 and pvj33.pos=0 and (pvj33.pos2 is null or pvj33.pos2&lt;=1) left outer join propval pvj34 on pvj34.tocid=linktoc.tocid and pvj34.prop_id=16 and pvj34.pos=0 and (pvj34.pos2 is null or pvj34.pos2&lt;=1) left outer join propval pvj35 on pvj35.tocid=linktoc.tocid and pvj35.prop_id=75 and pvj35.pos=0 and (pvj35.pos2 is null or pvj35.pos2&lt;=1) left outer join propval pvj36 on pvj36.tocid=linktoc.tocid and pvj36.prop_id=64 and pvj36.pos=0 and (pvj36.pos2 is null or pvj36.pos2&lt;=1) left outer join propval pvj37 on pvj37.tocid=linktoc.tocid and pvj37.prop_id=66 and pvj37.pos=0 and (pvj37.pos2 is null or pvj37.pos2&lt;=1) left outer join propval pvj38 on pvj38.tocid=linktoc.tocid and pvj38.prop_id=84 and pvj38.pos=0 and (pvj38.pos2 is null or pvj38.pos2&lt;=1) where ((toc.etype is null or ((toc.etype=-2 or toc.etype&gt;=0) or (toc.etype = -1 and (linktoc.etype=-2 or linktoc.etype&gt;=0)))))) as q1  where q1.row_num&gt;=:begin&lt;int&gt; and q1.row_num&lt;=:end&lt;int&gt; order by q1.row_num</Data>
    <Data Name="timeout">15</Data>
  </EventData>
</Event>

We are running Laserfiche Server version 11.0.2.338.

0 0

Replies

replied on March 14 • Show version history

Hi Blake,

The query you're referencing is used when viewing entry listings in the repository. For example, when performing a search and viewing the result set, or when browsing folders in the repository.

Typically, for issues related to SQL query timeouts, we first recommend performing database maintenance to see if that may help. One example we commonly suggest to customers is example B from https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

If the query timeout still occurs, then the next suggestion may be to examine what columns the user encountering the issue has enabled. Perhaps they have one or more columns enabled that may particularly tax the SQL Server more than others and perhaps they can disable that column from the listing if it isn't required.

If the issue still occurs after doing this, then a third suggestion is to increase the query timeout specifically for getting entry listings. In the Laserfiche repository database, you can insert the following option into the dboptions table

EntryListingQueryTimeout

and give it a value of 30 to start. Restart the Laserfiche Server service and see if that additional time is enough for the query to complete.

Note that a high timeout value may degrade the performance of other sessions and in the worst case it could cause the server to become unresponsive if it's set too high and there's a large number of very slow listing queries that the server starts to run. 30 is a safe starting point if going this route.

Regards

1 0
replied on March 14

Thank you for the reply, Alex. We are already running the Ola Hallengren maintenance queries on a schedule per his recommendations.

I have not had end users complain about the errors, it is just what I saw when looking in the logs. I will try the database option and start at 30 to see if that helps lower the number of these errors that are being recorded.

0 0
You are not allowed to follow up in this post.

Sign in to reply to this post.

Details
Related Posts
Loading ...