Sql error 845

Hi experts,

I got an error message and the error message is ambiguous. I google and find a lot of discussions but I don’t know how to troubleshoot.

( https://support.microsoft.com/en-za/help/310834/description-of-common-causes-of-sql-server-error-message-844-or-error )

I run our SAP on SQL with HPE DL580. Please give me a guideline and any information is appreciated. I upload my complete errorlog on google drive( https://drive.google.com/file/d/1EKLhIGBxsEFYaJ6EIEPxUREqj0HOlLSj/view?usp=sharing )

By the way, database files and transaction log both are located on PCIe SSDs.

— systeminfo —

Processor(s): 4 Processor(s) Installed.
[01]: Intel64 Family 6 Model 85 Stepping 4 GenuineIntel ~2494 Mhz
[02]: Intel64 Family 6 Model 85 Stepping 4 GenuineIntel ~2494 Mhz
[03]: Intel64 Family 6 Model 85 Stepping 4 GenuineIntel ~2494 Mhz
[04]: Intel64 Family 6 Model 85 Stepping 4 GenuineIntel ~2494 Mhz

Total Physical Memory: 3,145,395 MB

—

«Windows Server 2016 Version 1607(OS Build 14393.2395)»

SQL: «Microsoft SQL Server 2012 (SP3-CU10) (KB4025925) — 11.0.6607.3 (X64)

Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 14393: )»

— when the problem occurred —

2018-12-07 10:15:26.97 spid20s Error: 5901, Severity: 16, State: 1.
2018-12-07 10:15:26.97 spid20s One or more recovery units belonging to database ‘TP1’ failed to generate a checkpoint. This is typically caused by lack of system resources such as disk or memory, or in some cases due to database corruption.
Examine previous entries in the error log for more detailed information on this failure.
2018-12-07 10:15:26.97 spid20s Error: 845, Severity: 17, State: 1.
2018-12-07 10:15:26.97 spid20s Time-out occurred while waiting for buffer latch type 3 for page (16:880781), database ID 5.

— partial errorlog —

2018-12-07 10:15:13.93 spid20s A time-out occurred while waiting for buffer latch — type 3, bp 0000013662FA21C0, page 16:880781, stat 0xb, database id: 5, allocation unit Id: 72057643394072576, task 0x00000133A2455498 : 1, waittime 300 seconds,
flags 0x100000001a, owning task 0x00000133A1A4D498. Not continuing to wait.
2018-12-07 10:15:13.97 spid20s Using ‘dbghelp.dll’ version ‘4.0.5’
2018-12-07 10:15:13.98 spid20s **Dump thread — spid = 20, EC = 0x0000009729DAA160
2018-12-07 10:15:13.99 spid20s ***Stack Dump being sent to C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLLOGSQLDump0002.txt
2018-12-07 10:15:13.99 spid20s * *******************************************************************************
2018-12-07 10:15:13.99 spid20s *
2018-12-07 10:15:13.99 spid20s * BEGIN STACK DUMP:
2018-12-07 10:15:13.99 spid20s * 12/07/18 10:15:13 spid 22652
2018-12-07 10:15:13.99 spid20s *
2018-12-07 10:15:13.99 spid20s * Latch timeout
2018-12-07 10:15:13.99 spid20s *
2018-12-07 10:15:13.99 spid20s *
2018-12-07 10:15:13.99 spid20s * *******************************************************************************
2018-12-07 10:15:13.99 spid20s * ——————————————————————————-
2018-12-07 10:15:13.99 spid20s * Short Stack Dump
2018-12-07 10:15:14.03 spid20s Stack Signature for the dump is 0x000000011415DBA4
2018-12-07 10:15:26.94 spid169 Remote harden of transaction ‘user_transaction’ (ID 0x000000008eadcb41 0010:ab2858a7) started at Dec 7 2018 10:15AM in database ‘TP1’ at LSN (957263:18905904:250) failed.
2018-12-07 10:15:26.94 spid39s A connection timeout has occurred on a previously established connection to availability replica ‘DL580-2’ with id [B85FD98F-6E3B-420B-BAD3-CBD4FF4D133D]. Either a networking or a firewall issue exists
or the availability replica has transitioned to the resolving role.
2018-12-07 10:15:26.94 spid819 Remote harden of transaction ‘user_transaction’ (ID 0x000000008eadcbda 0010:ab2858a8) started at Dec 7 2018 10:15AM in database ‘TP1’ at LSN (957263:18905969:87) failed.
2018-12-07 10:15:26.94 spid36s Remote harden of transaction ‘GhostCleanupTask’ (ID 0x000000008eadcd5b 0010:ab2858ab) started at Dec 7 2018 10:15AM in database ‘TP1’ at LSN (957263:18906120:20) failed.
2018-12-07 10:15:26.95 spid645 Remote harden of transaction ‘user_transaction’ (ID 0x000000008eadcbb1 0010:ab2858ac) started at Dec 7 2018 10:15AM in database ‘TP1’ at LSN (957263:18906127:51) failed.
2018-12-07 10:15:26.95 spid44s AlwaysOn Availability Groups connection with secondary database terminated for primary database ‘TP1’ on the availability replica with Replica ID: {b85fd98f-6e3b-420b-bad3-cbd4ff4d133d}. This is an informational
message only. No user action is required.
2018-12-07 10:15:26.95 spid782 Remote harden of transaction ‘user_transaction’ (ID 0x000000008eace892 0010:ab2858ad) started at Dec 7 2018 10:15AM in database ‘TP1’ at LSN (957263:18906148:70) failed.
2018-12-07 10:15:26.95 spid1656 Remote harden of transaction ‘user_transaction’ (ID 0x000000008eadc933 0010:ab2858af) started at Dec 7 2018 10:15AM in database ‘TP1’ at LSN (957263:18906182:23) failed.
2018-12-07 10:15:26.94 Server Error: 19419, Severity: 16, State: 1.
2018-12-07 10:15:26.94 Server Windows Server Failover Cluster did not receive a process event signal from SQL Server hosting availability group ‘AGTP1’ within the lease timeout period.
2018-12-07 10:15:26.95 Server Error: 19407, Severity: 16, State: 1.
2018-12-07 10:15:26.95 Server The lease between availability group ‘AGTP1’ and the Windows Server Failover Cluster has expired. A connectivity issue occurred between the instance of SQL Server and the Windows Server Failover Cluster. To
determine whether the availability group is failing over correctly, check the corresponding availability group resource in the Windows Server Failover Cluster.
2018-12-07 10:15:26.95 Server AlwaysOn: The local replica of availability group ‘AGTP1’ is going offline because either the lease expired or lease renewal failed. This is an informational message only. No user action is required.
2018-12-07 10:15:26.95 Server The state of the local availability replica in availability group ‘AGTP1’ has changed from ‘PRIMARY_NORMAL’ to ‘RESOLVING_NORMAL’. The replica state changed because of either a startup, a failover, a communication
issue, or a cluster error. For more information, see the availability group dashboard, SQL Server error log, Windows Server Failover Cluster management console or Windows Server Failover Cluster log.
2018-12-07 10:15:26.95 spid44s AlwaysOn Availability Groups connection with secondary database terminated for primary database ‘TP1’ on the availability replica with Replica ID: {b85fd98f-6e3b-420b-bad3-cbd4ff4d133d}. This is an informational
message only. No user action is required.
2018-12-07 10:15:26.95 spid934 Remote harden of transaction ‘user_transaction’ (ID 0x000000008eadcb69 0010:ab2858b0) started at Dec 7 2018 10:15AM in database ‘TP1’ at LSN (957263:18906186:137) failed.
2018-12-07 10:15:26.95 Logon Error: 17191, Severity: 16, State: 2.
2018-12-07 10:15:26.95 Logon Cannot accept a new connection because the session has been terminated. This error occurs when a new batch execution is attempted on a session that is logging out, or when a severe error is encountered
upon connection. Check the error log to see if this session was terminated by a KILL command or because of severe errors. [CLIENT: 192.168.28.178]
2018-12-07 10:15:26.95 spid2182 Remote harden of transaction ‘user_transaction’ (ID 0x000000008eadbfd1 0010:ab2858b1) started at Dec 7 2018 10:15AM in database ‘TP1’ at LSN (957263:18906217:18) failed.
2018-12-07 10:15:26.95 Logon Error: 17191, Severity: 16, State: 2.
2018-12-07 10:15:26.95 Logon Cannot accept a new connection because the session has been terminated. This error occurs when a new batch execution is attempted on a session that is logging out, or when a severe error is encountered
upon connection. Check the error log to see if this session was terminated by a KILL command or because of severe errors. [CLIENT: 192.168.28.172]
2018-12-07 10:15:26.95 spid1171 Remote harden of transaction ‘user_transaction’ (ID 0x000000008eadcc25 0010:ab2858b2) started at Dec 7 2018 10:15AM in database ‘TP1’ at LSN (957263:18906224:63) failed.
2018-12-07 10:15:26.95 Logon Error: 17191, Severity: 16, State: 2.
2018-12-07 10:15:26.95 Logon Cannot accept a new connection because the session has been terminated. This error occurs when a new batch execution is attempted on a session that is logging out, or when a severe error is encountered
upon connection. Check the error log to see if this session was terminated by a KILL command or because of severe errors. [CLIENT: 192.168.28.172]
2018-12-07 10:15:26.95 spid620 Remote harden of transaction ‘user_transaction’ (ID 0x000000008e6bdf8d 0010:ab2858b3) started at Dec 7 2018 10:11AM in database ‘TP1’ at LSN (957263:18906246:32) failed.
2018-12-07 10:15:26.95 spid1806 Remote harden of transaction ‘user_transaction’ (ID 0x000000008eadc908 0010:ab2858b5) started at Dec 7 2018 10:15AM in database ‘TP1’ at LSN (957263:18906255:5) failed.
2018-12-07 10:15:26.95 spid281 Remote harden of transaction ‘user_transaction’ (ID 0x000000008ead6f6c 0010:ab2858b4) started at Dec 7 2018 10:15AM in database ‘TP1’ at LSN (957263:18906257:1) failed.
2018-12-07 10:15:26.95 Logon Error: 17191, Severity: 16, State: 2.
2018-12-07 10:15:26.95 Logon Cannot accept a new connection because the session has been terminated. This error occurs when a new batch execution is attempted on a session that is logging out, or when a severe error is encountered
upon connection. Check the error log to see if this session was terminated by a KILL command or because of severe errors. [CLIENT: 192.168.28.173]
2018-12-07 10:15:26.95 spid1744 Remote harden of transaction ‘user_transaction’ (ID 0x000000008ead9cb8 0010:ab2858b7) started at Dec 7 2018 10:15AM in database ‘TP1’ at LSN (957263:18906258:27) failed.
2018-12-07 10:15:26.95 spid2445 Remote harden of transaction ‘user_transaction’ (ID 0x000000008eadc1e6 0010:ab2858b6) started at Dec 7 2018 10:15AM in database ‘TP1’ at LSN (957263:18906258:29) failed.
2018-12-07 10:15:26.95 Logon Error: 17191, Severity: 16, State: 2.
2018-12-07 10:15:26.95 Logon Cannot accept a new connection because the session has been terminated. This error occurs when a new batch execution is attempted on a session that is logging out, or when a severe error is encountered
upon connection. Check the error log to see if this session was terminated by a KILL command or because of severe errors. [CLIENT: 192.168.28.174]
2018-12-07 10:15:26.95 Logon Error: 17191, Severity: 16, State: 2.
2018-12-07 10:15:26.95 Logon Cannot accept a new connection because the session has been terminated. This error occurs when a new batch execution is attempted on a session that is logging out, or when a severe error is encountered
upon connection. Check the error log to see if this session was terminated by a KILL command or because of severe errors. [CLIENT: 192.168.28.173]
2018-12-07 10:15:26.95 spid1776 Remote harden of transaction ‘user_transaction’ (ID 0x000000008eadcb80 0010:ab2858b8) started at Dec 7 2018 10:15AM in database ‘TP1’ at LSN (957263:18906268:62) failed.
2018-12-07 10:15:26.95 Logon Error: 17191, Severity: 16, State: 2.
2018-12-07 10:15:26.95 Logon Cannot accept a new connection because the session has been terminated. This error occurs when a new batch execution is attempted on a session that is logging out, or when a severe error is encountered
upon connection. Check the error log to see if this session was terminated by a KILL command or because of severe errors. [CLIENT: 192.168.28.178]
2018-12-07 10:15:26.95 spid2059 Remote harden of transaction ‘user_transaction’ (ID 0x000000008eadae7b 0010:ab2858b9) started at Dec 7 2018 10:15AM in database ‘TP1’ at LSN (957263:18906291:57) failed.
2018-12-07 10:15:26.95 spid2315 Remote harden of transaction ‘user_transaction’ (ID 0x000000008eadcab4 0010:ab2858bb) started at Dec 7 2018 10:15AM in database ‘TP1’ at LSN (957263:18906313:39) failed.
2018-12-07 10:15:26.95 Logon Error: 17191, Severity: 16, State: 2.
2018-12-07 10:15:26.95 Logon Cannot accept a new connection because the session has been terminated. This error occurs when a new batch execution is attempted on a session that is logging out, or when a severe error is encountered
upon connection. Check the error log to see if this session was terminated by a KILL command or because of severe errors. [CLIENT: 192.168.28.179]
2018-12-07 10:15:26.95 spid994 Remote harden of transaction ‘user_transaction’ (ID 0x000000008eadc886 0010:ab2858ba) started at Dec 7 2018 10:15AM in database ‘TP1’ at LSN (957263:18906327:51) failed.
2018-12-07 10:15:26.95 spid20s External dump process return code 0x20000001.
External dump process returned no errors.

2018-12-07 10:15:26.95 spid866 Remote harden of transaction ‘user_transaction’ (ID 0x000000008eadcb18 0010:ab2858bd) started at Dec 7 2018 10:15AM in database ‘TP1’ at LSN (957263:18906345:151) failed.
2018-12-07 10:15:26.95 Logon Error: 17191, Severity: 16, State: 2.
2018-12-07 10:15:26.95 Logon Cannot accept a new connection because the session has been terminated. This error occurs when a new batch execution is attempted on a session that is logging out, or when a severe error is encountered
upon connection. Check the error log to see if this session was terminated by a KILL command or because of severe errors. [CLIENT: 192.168.28.174]
2018-12-07 10:15:26.95 spid1635 Remote harden of transaction ‘user_transaction’ (ID 0x000000008eadcbf3 0010:ab2858be) started at Dec 7 2018 10:15AM in database ‘TP1’ at LSN (957263:18906379:41) failed.
2018-12-07 10:15:26.95 spid1289 Remote harden of transaction ‘user_transaction’ (ID 0x000000008eadcc56 0010:ab2858bf) started at Dec 7 2018 10:15AM in database ‘TP1’ at LSN (957263:18906394:31) failed.
2018-12-07 10:15:26.95 Logon Error: 17191, Severity: 16, State: 2.
2018-12-07 10:15:26.95 Logon Cannot accept a new connection because the session has been terminated. This error occurs when a new batch execution is attempted on a session that is logging out, or when a severe error is encountered
upon connection. Check the error log to see if this session was terminated by a KILL command or because of severe errors. [CLIENT: 192.168.28.179]
2018-12-07 10:15:26.96 spid780 Remote harden of transaction ‘user_transaction’ (ID 0x000000008eadcbc4 0010:ab2858c0) started at Dec 7 2018 10:15AM in database ‘TP1’ at LSN (957263:18906405:47) failed.
2018-12-07 10:15:26.96 Logon Error: 17191, Severity: 16, State: 2.
2018-12-07 10:15:26.96 Logon Cannot accept a new connection because the session has been terminated. This error occurs when a new batch execution is attempted on a session that is logging out, or when a severe error is encountered
upon connection. Check the error log to see if this session was terminated by a KILL command or because of severe errors. [CLIENT: 192.168.28.173]
2018-12-07 10:15:26.96 Logon Error: 17191, Severity: 16, State: 2.
2018-12-07 10:15:26.96 Logon Cannot accept a new connection because the session has been terminated. This error occurs when a new batch execution is attempted on a session that is logging out, or when a severe error is encountered
upon connection. Check the error log to see if this session was terminated by a KILL command or because of severe errors. [CLIENT: 192.168.28.175]
2018-12-07 10:15:26.96 spid1782 Remote harden of transaction ‘user_transaction’ (ID 0x000000008eadcc09 0010:ab2858c1) started at Dec 7 2018 10:15AM in database ‘TP1’ at LSN (957263:18906423:58) failed.
2018-12-07 10:15:26.96 spid2130 Remote harden of transaction ‘user_transaction’ (ID 0x000000008eadcc3d 0010:ab2858c2) started at Dec 7 2018 10:15AM in database ‘TP1’ at LSN (957263:18906445:50) failed.
2018-12-07 10:15:26.96 Logon Error: 17191, Severity: 16, State: 2.
2018-12-07 10:15:26.96 Logon Cannot accept a new connection because the session has been terminated. This error occurs when a new batch execution is attempted on a session that is logging out, or when a severe error is encountered
upon connection. Check the error log to see if this session was terminated by a KILL command or because of severe errors. [CLIENT: 192.168.28.175]
2018-12-07 10:15:26.96 spid572 Remote harden of transaction ‘user_transaction’ (ID 0x000000008eada858 0010:ab2858c3) started at Dec 7 2018 10:15AM in database ‘TP1’ at LSN (957263:18906462:46) failed.
2018-12-07 10:15:26.96 Logon Error: 17191, Severity: 16, State: 2.
2018-12-07 10:15:26.96 Logon Cannot accept a new connection because the session has been terminated. This error occurs when a new batch execution is attempted on a session that is logging out, or when a severe error is encountered
upon connection. Check the error log to see if this session was terminated by a KILL command or because of severe errors. [CLIENT: 192.168.28.179]
2018-12-07 10:15:26.96 spid1048 Remote harden of transaction ‘user_transaction’ (ID 0x000000008eadcc8f 0010:ab2858c4) started at Dec 7 2018 10:15AM in database ‘TP1’ at LSN (957263:18906479:29) failed.
2018-12-07 10:15:26.96 spid1800 Remote harden of transaction ‘user_transaction’ (ID 0x000000008eadc99d 0010:ab2858c5) started at Dec 7 2018 10:15AM in database ‘TP1’ at LSN (957263:18906490:30) failed.
2018-12-07 10:15:26.96 spid1388 Remote harden of transaction ‘user_transaction’ (ID 0x000000008eadcc6b 0010:ab2858c6) started at Dec 7 2018 10:15AM in database ‘TP1’ at LSN (957263:18906501:62) failed.
2018-12-07 10:15:26.96 Logon Error: 17191, Severity: 16, State: 2.
2018-12-07 10:15:26.96 Logon Cannot accept a new connection because the session has been terminated. This error occurs when a new batch execution is attempted on a session that is logging out, or when a severe error is encountered
upon connection. Check the error log to see if this session was terminated by a KILL command or because of severe errors. [CLIENT: 192.168.28.172]
2018-12-07 10:15:26.96 Logon Error: 17191, Severity: 16, State: 2.
2018-12-07 10:15:26.96 Logon Cannot accept a new connection because the session has been terminated. This error occurs when a new batch execution is attempted on a session that is logging out, or when a severe error is encountered
upon connection. Check the error log to see if this session was terminated by a KILL command or because of severe errors. [CLIENT: 192.168.28.173]
2018-12-07 10:15:26.96 Logon Error: 17191, Severity: 16, State: 2.
2018-12-07 10:15:26.96 Logon Cannot accept a new connection because the session has been terminated. This error occurs when a new batch execution is attempted on a session that is logging out, or when a severe error is encountered
upon connection. Check the error log to see if this session was terminated by a KILL command or because of severe errors. [CLIENT: 192.168.28.178]
2018-12-07 10:15:26.96 spid150 AlwaysOn: The local replica of availability group ‘AGTP1’ is preparing to transition to the resolving role in response to a request from the Windows Server Failover Clustering (WSFC) cluster. This is an informational
message only. No user action is required.
2018-12-07 10:15:26.96 spid881 Remote harden of transaction ‘user_transaction’ (ID 0x000000008eadcb2c 0010:ab2858c7) started at Dec 7 2018 10:15AM in database ‘TP1’ at LSN (957263:18906524:72) failed.
2018-12-07 10:15:26.96 Logon Error: 17191, Severity: 16, State: 2.
2018-12-07 10:15:26.96 Logon Cannot accept a new connection because the session has been terminated. This error occurs when a new batch execution is attempted on a session that is logging out, or when a severe error is encountered
upon connection. Check the error log to see if this session was terminated by a KILL command or because of severe errors. [CLIENT: 192.168.28.175]
2018-12-07 10:15:26.96 spid1298 Remote harden of transaction ‘user_transaction’ (ID 0x000000008eadccaf 0010:ab2858ca) started at Dec 7 2018 10:15AM in database ‘TP1’ at LSN (957263:18906549:561) failed.
2018-12-07 10:15:26.96 spid718 Remote harden of transaction ‘user_transaction’ (ID 0x000000008ead234b 0010:ab2858cb) started at Dec 7 2018 10:15AM in database ‘TP1’ at LSN (957263:18906647:64) failed.
2018-12-07 10:15:26.97 Logon Error: 17191, Severity: 16, State: 2.
2018-12-07 10:15:26.97 Logon Cannot accept a new connection because the session has been terminated. This error occurs when a new batch execution is attempted on a session that is logging out, or when a severe error is encountered
upon connection. Check the error log to see if this session was terminated by a KILL command or because of severe errors. [CLIENT: 192.168.28.173]
2018-12-07 10:15:26.97 spid20s Error: 5901, Severity: 16, State: 1.
2018-12-07 10:15:26.97 spid20s One or more recovery units belonging to database ‘TP1’ failed to generate a checkpoint. This is typically caused by lack of system resources such as disk or memory, or in some cases due to database corruption.
Examine previous entries in the error log for more detailed information on this failure.
2018-12-07 10:15:26.97 spid20s Error: 845, Severity: 17, State: 1.
2018-12-07 10:15:26.97 spid20s Time-out occurred while waiting for buffer latch type 3 for page (16:880781), database ID 5.
2018-12-07 10:15:26.97 Logon Error: 17191, Severity: 16, State: 2.
2018-12-07 10:15:26.97 Logon Cannot accept a new connection because the session has been terminated. This error occurs when a new batch execution is attempted on a session that is logging out, or when a severe error is encountered
upon connection. Check the error log to see if this session was terminated by a KILL command or because of severe errors. [CLIENT: 192.168.28.174]
2018-12-07 10:15:26.97 Logon Error: 17191, Severity: 16, State: 2.
2018-12-07 10:15:26.97 Logon Cannot accept a new connection because the session has been terminated. This error occurs when a new batch execution is attempted on a session that is logging out, or when a severe error is encountered
upon connection. Check the error log to see if this session was terminated by a KILL command or because of severe errors. [CLIENT: 192.168.28.172]
2018-12-07 10:15:26.98 spid35s The availability group database «TP1» is changing roles from «PRIMARY» to «RESOLVING» because the mirroring session or availability group failed over due to role synchronization.
This is an informational message only. No user action is required.
2018-12-07 10:15:26.98 Server Error: 10801, Severity: 16, State: 1.
2018-12-07 10:15:26.98 Server Failed to stop the listener for Windows Failover Cluster resource ‘8b8c6680-2508-45a4-823b-5d91499ed14c’. Error: 87. The parameter is incorrect.
2018-12-07 10:15:26.99 Backup Error: 3041, Severity: 16, State: 1.

Источник

Permalink

Cannot retrieve contributors at this time

description	title	ms.custom	ms.date	ms.service	ms.reviewer	ms.subservice	ms.topic	helpviewer_keywords	ms.assetid	author	ms.author
MSSQLSERVER_845	MSSQLSERVER_845 \| Microsoft Docs		04/04/2017	sql		supportability	reference	845 (Database Engine error)	8fff6ad4-234c-44be-b123-e25d5e1cd63e	MashaMSFT	mathoma

MSSQLSERVER_845

[!INCLUDE SQL Server]

Details

Attribute	Value
Product Name	SQL Server
Event ID	845
Event Source	MSSQLSERVER
Component	SQLEngine
Symbolic Name	BUFLATCH_TIMEOUT
Message Text	Time-out occurred while waiting for buffer latch type %d for page %S_PGID, database ID %d.

Explanation

A process was waiting to acquire a latch, but the process waited until the time limit expired and failed to acquire one. This can occur if an I/O operation takes too long to complete, usually as a result of other tasks blocking system processes. In some instances, this error may be the result of hardware failure.

Cause

This error message is dependent on the overall environment of your system. Any of the following circumstances may lead to an overstressed system:

Hardware that doesn’t meet your input/output (I/O) and memory needs
Improperly configured and tested settings
Inefficient design

You may observe error 845 when your system is under a heavy load and can’t meet the workload demands. Some of the most common causes of a stressed environment are:

Hardware problems
Compressed volumes
Non-default [!INCLUDEssNoVersion] configuration settings
Inefficient queries or index design
Frequent database AutoGrow or AutoShrink operations

User Action

Try the following to prevent this error from occurring:

Determine if you have any hardware bottlenecks. See Identifying Bottlenecks for a good place to start. If necessary, upgrade your hardware so it can service the needs of your environment’s configuration, queries, and load.
Verify that all your hardware functions properly. Check for any logged errors and run any diagnostics provided by your hardware vendor. Check for associated I/O failures in error log or event log. I/O failures typically point to a disk malfunction.
Make sure that your disk volumes aren’t compressed. Storing data and log files on compressed drives isn’t supported, see Database Files and Filegroups. For additional information on compressed drive support, review the following article: SQL Server Databases Not Supported on Compressed Volumes
See if the error messages disappear when you turn off all the following [!INCLUDEssNoVersion] configuration options:
- The priority boost option
- The lightweight pooling (fiber mode) option
- The set working set size option
For more information see HOW TO: Determine Proper SQL Server Configuration Settings
Tune queries to reduce resources used on the system. Performance tuning will help reduce the stress on a system and improve response time for individual queries
Set the AutoShrink property to OFF to reduce the overhead of changes to your database size
Make sure you set the AutoGrow property to increments that are large enough to be infrequent. Schedule a job to check the available space in your databases, and then increase the database size during non-peak hours.
Check the error log for non-yielding tasks and other critical errors. Resolve those errors first as they could point to the root cause of the underlying issue.
If critical errors such as asserts frequently occur, resolve these problems
If the 845 error messages are infrequent, then you can ignore the errors

Источник

Question: I received this error message when running a recreate index job on a SQL Server database

Msg 845, Level 17, State 1, Line 2
Time-out occurred while waiting for buffer latch type 3 for page (1:66909436), database ID 371

There was a very heavy wokload on the system at the time.

Answer: A Msg 845 Time-out occurred while waiting for buffer latch type 3 refers to a timeout when a
SQL Server task is attempting to acquire a latch but it is currently acquired by another task.

The requesting task waits until the latch release. If the requesting task has to wait for longer than 5 minutes , the requesting task aborts.
Normally the process of requesting and releasing latches is very quick. So to receive a Msg 845 Time-out occurred while waiting for buffer latch type 3 indicates a serious performance problem.

Tyoe 3 refers to the update mode, if you’d like see a full list of latch modes, execute this sql code:

select * from sys.dm_xe_map_values where name=’latch_mode’

The symptoms are normally very slow or server hanging, timeouts of tasks , such as the example presented.

The best strategy to adopt is an overall system environment analysis.

1) IO requests of SQL Server cannot be met by the hardware.This becomes pronounced when there is a heavy workload on the system. For example, if you’re running a batch processing job, full backups and index maintenace job , all in parallel — then there is a greater demand on IO channels.
If you think this may be the cause, then review the timings of these operations and schedule at separate times
Maybe when the server was first set up it was suitable for the workload and the workload has now grown?

SQL Performance tuning is about Asking the right question

2) OS and SQL Server configurations. Have you completed a thorough review of the configurations. Most configuration out of the box , tend to be suitable for most workloads, but there are a few useful ones to check. Read up on Instant file initialization

3) Hardware issues — request from the server administrator if firmware is up to date or any errors appearing in the logs

4) Query optimisastion — have the queries or indexes become inefficient , leading to increased IO demands

5) AutoGrow — Are there to many auto grow activities?SQL LOG FILE AUTOGROW performance troubleshooting — SQL …

6) Storage devices — In a shared infrastructure it is common for underlying systems to shift , new storage neighbours may lead to increased competition for IO throughput.

Contact US

Thanks. We have received your request and will respond promptly.

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!

Talk With Other Members
Be Notified Of Responses
To Your Posts
Keyword Search
One-Click Access To Your
Favorite Forums
Automated Signatures
On Your Posts
Best Of All, It’s Free!

*Tek-Tips’s functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Server: Msg 845, Level 17, State 1, Line 1

(OP)

12 Jan 07 09:55

Hi,
I have a job that does a backup of all databases in a clustered instance.
Database version is sql 2000 sp3a.
From three days the backup is failing and the error is on one database. All other backups are fine.
This database reside on the same directory and disk of the other databases. Also the backup is done on the same disk of all databases.
I tried to manually backup this database as follows:
backup database ge to disk = ‘C:tempge.bak’
The error is:
Server: Msg 845, Level 17, State 1, Line 1
Time-out occurred while waiting for buffer latch type 3 for page (1:23983), database ID 40.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

From my point of view the only explanation is that the database has some corrupted blocks.
As I told all other databases backup are fine.
Have you ever encountered this error?

Thanks.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Join Tek-Tips^® Today!

Join your peers on the Internet’s largest technical computer professional community.
It’s easy to join and it’s free.

Here’s Why Members Love Tek-Tips Forums:

Talk To Other Members
Notification Of Responses To Questions
Favorite Forums One Click Access
Keyword Search Of All Posts, And More…

Register now while it’s still free!

Already a member? Close this window and log in.

Join Us Close

Источник

Hi Friends,

This is my third blog in the series of “SQL Server Trace Flags”. You can go to previous blog by clicking here. This blog is about SQL Server Trace Flag 845.

Initially locked pages in memory feature came with enterprise and developer editions. If we want to use SQL Server standard 64 bit edition along with locked pages in memory (LPIM) feature then we will use this trace flag 845. For using LPIM on 64 bit standard edition, you must have below service pack and cumulative update also.

SQL Server 2005 standard edition 64 bit: Service Pack 3 + Cumulative Update package 4 + enable “Lock Pages in Memory” for the SQL Server service account + Trace Flag 845

SQL Server 2008 standard edition 64 bit: Service Pack 1 + Cumulative Update package 2 + enable “Lock Pages in Memory” for the SQL Server service account + Trace Flag 845

SQL Server 2008 R2 standard edition 64 bit: enable “Lock Pages in Memory” for the SQL Server service account + Trace Flag 845

Step 1: Install required service pack and cumulative update package.

Step 2: enable “Lock Pages in Memory” for the SQL Server service account

1- Go to Start -> Run -> type gpedit.msc (group policy editor will open)

2- Computer configuration -> Windows Settings -> Security Settings -> Local Policies -> User Rights Assignment

3- In the right pane Double click on Lock Pages in Memory (this will open up a properties window)

4- Go to Local Security Settings -> Click on Add User or Group

5- Add the account here which is running SQL Server Service

6- Restart the machine

Step 3: Trace Flag 845

1- Go to Start -> Click on Microsoft SQL Server 200520082008 R2 -> Configuration Tools -> click on SQL Server Configuration Manager

2- Click on SQL Server Services in Right Pane.

3- Double click to open properties on SQL Server service

4- Now Click on Advanced Tab.

5- Here you can add ‘;-T845’ at the end of Start up Parameter Value.

PS: Do not use trace flags in production environment without testing it on non production environments and without consulting because everything comes at a cost.

HAPPY LEARNING.

Regards:
Prince Kumar Rastogi

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook

Follow Prince Rastogi on Twitter | Follow Prince Rastogi on FaceBook

Источник

MSSQLSERVER_845

Details

Explanation

Cause

User Action

Read More on sql server latches