Hello Team,
We have very large repository and when we tried to search anything we got error like below. ( Error.png)
Error executing SQL command.
General database error. [9008]
You can check the error log and everything in attachment.
Log from eventviewer attached.
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).
Kindly do needful what to do for the same.
Thanks,
Pratik