Sql error 845

Hi experts,

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.

Read More on sql server latches

Warning: Failure to calculate super-latch promotion threshold — SQL …

Identify Page Latch contention — SQL Server DBA

Author: Tom Collins (http://www.sqlserver-dba.com)

Share:

Источник

(SQL Server 7 SP4)

Last night, my scheduled optimisations job failed while reindexing with the following error:

[Microsoft SQL-DMO (ODBC SQLState: HY000)] Error 5180: [Microsoft][ODBC SQL Server Driver][SQL Server]Could not open FCB for invalid file ID 0 in database ‘PLMS’. Table or database may be corrupted.

[Microsoft][ODBC SQL Server Driver][SQL Server]Index (ID = 1) is being rebuilt.

[Microsoft][ODBC SQL Server Driver][SQL Server]Index (ID = 2) is being rebuilt.

** Execution Time: 0 hrs, 8 mins, 12 secs **

[Microsoft SQL-DMO (ODBC SQLState: 08S01)] Error 0: [Microsoft][ODBC SQL Server Driver]Communication link failure

Now my hourly transaction log backup fails every time with the following:

[1] Database PLMS: Transaction Log Backup…

Destination: [D:PLMS_BACKUPPLMSPLMS_tlog_200901051000.TRN]

[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 845: [Microsoft][ODBC SQL Server Driver][SQL Server]Time out occurred while waiting for buffer latch type 1 for page (0:-1688993522), database ID 5, object ID 0, index ID 0.

[Microsoft][ODBC SQL Server Driver][SQL Server]Backup or restore operation terminating abnormally.

Deleting old text reports… 2 file(s) deleted.

And my SQL log contains the following every few minutes:

2009-01-05 10:12:12.61 spid5 Error: 845, Severity: 17, State: 2

2009-01-05 10:12:12.61 spid5 Time out occurred while waiting for buffer latch type 1 for page (0:-1688993522), database ID 5, object ID 0, index ID 0..

2009-01-05 10:22:12.89 spid63 Time out occurred while waiting for buffer latch type 1, bp 0x145ec580, page (0:-1688993522), stat 0x40d, object ID 5:0:0, waittime 500. Continuing to wait.

2009-01-05 10:33:53.31 spid5 Time out occurred while waiting for buffer latch type 1, bp 0x145ec580, page (0:-1688993522), stat 0x40d, object ID 5:0:0, waittime 500. Continuing to wait.

I’ve successfully reindexed the table in question, but my concern is that my Checkpoint process is sitting with a lastwaittype of PAGEIOLATCH_EX and the waitresource 5:0:-1688993522. What would happen if I restarted the server while the Checkpoint process cannot complete? Is the negative page number a sign of corruption or do pages in a certain state have a negative number?

I’ve checked the disks and there are no problems with them. The server has a healthy amount of available ram and as far as I can see, it is not under any greater load than usual.

Thanks,

Simon

Gail Shaw

SSC Guru

Points: 1004494

January 5, 2009 at 11:01 am

#921308

Eeep. That does not look good.

There’s no way that I know of to get a negative page number, as the page number is an incremental count of 8k chunks of the data file.

Can you run a checkDB on that database? If possible, before you restart the service.

DBCC CHECKDB (< Database Name > ) WITH NO_INFOMSGS, ALL_ERRORMSGS

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Simon Liddle

SSCrazy

Points: 2580

Hi Gail,

I ran the DBCC CHECKDB this morning and it came back completely clean. I restarted the server (had to use WITH NOWAIT as the checkpoint was still throwing an error) and when it came back up, everything seems to be okay. The data is all there and the database was accessible almost immediately, as if the checkpoint had been set successfully before shutdown.

Still baffled by what happened but at least the server appears to be well again — will be keeping a close eye on it though.

Thanks for your help.

Simon

Источник

INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

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

Log In

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

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:

  • Tek-Tips ForumsTalk 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

Источник

Понравилась статья? Поделить с друзьями:
  • Sql error 8152 22001 string or binary data would be truncated
  • Sql error 80040e07
  • Sql error 7412
  • Sql error 605
  • Sql error 58p01 ошибка загрузить библиотеку c program files postgresql 13 lib plpython3 dll