# "The connection is broken and recovery is not possible" after MSSQL reboot

**URL:** <https://discuss.elastic.co/t/the-connection-is-broken-and-recovery-is-not-possible-after-mssql-reboot/338793>\
**Category:** Logstash\
**Tags:** jdbc\
**Created:** [July 19, 2023, 1:41pm UTC](https://discuss.elastic.co/t/the-connection-is-broken-and-recovery-is-not-possible-after-mssql-reboot/338793 "2023-07-19T13:41:39Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![mr18](https://avatars.discourse-cdn.com/v4/letter/m/a9adbd/32.png) [@mr18](https://discuss.elastic.co/u/mr18)\
**Post date:** [July 19, 2023, 1:41pm UTC](https://discuss.elastic.co/t/the-connection-is-broken-and-recovery-is-not-possible-after-mssql-reboot/338793/1 "2023-07-19T13:41:39Z")

</div>

Hi all !  
I use a jdbc input plugin to get MS SQL data. Everything works fine, but when my SQL server reboots for maintenance, the queries don't work anymore, and I have the following error:

> [ERROR][logstash.inputs.jdbc] Java::ComMicrosoftSqlserverJdbc::SQLServerException: The connection is broken and recovery is not possible. The connection is marked by the client driver as unrecoverable. No attempt was made to restore the connection.: _sql query here_

I have this message everytime the query is executed. The only way to retrieve my connection is to restart logstash service.  
I tried several parameters, just as:

```auto
       use_column_value => true
       tracking_column => "id"
       tracking_column_type => "numeric"

```

or  
` clean_run => true`

or  
` connection_retry_attempts => 0`

and all of them in one configuration.

I don't know how to deal with this error, and I can't find a lot of mention of it. Do you have some solution?  
Thanks

---

<div class="post-metadata">

**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\
**Post date:** [July 19, 2023, 4:02pm UTC](https://discuss.elastic.co/t/the-connection-is-broken-and-recovery-is-not-possible-after-mssql-reboot/338793/2 "2023-07-19T16:02:50Z")

</div>

I believe there are two levels of connection retries. [One](https://learn.microsoft.com/en-us/sql/connect/jdbc/connection-resiliency?view=sql-server-ver16) in the JDBC driver, which will retry once after 10 seconds. I think that "recovery is not possible" exception is returned if that fails. There is [another level of retry](https://github.com/logstash-plugins/logstash-integration-jdbc/blob/c06e3192d8da3528bf4fc045b3fd8721b60a5a1e/lib/logstash/plugin_mixins/jdbc/jdbc.rb#L118) within the jdbc integration (used by the input), which retries once after 500 milliseconds.

If your SQL server takes more than 10.5 seconds to reboot (which seems likely) then the input will not reconnect. Try increasing [connection\_retry\_attempts\_wait\_time](https://www.elastic.co/guide/en/logstash/current/plugins-inputs-jdbc.html#plugins-inputs-jdbc-connection_retry_attempts_wait_time) to 120 or something that will result in the retry happening after the DB server is back up.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [August 16, 2023, 4:03pm UTC](https://discuss.elastic.co/t/the-connection-is-broken-and-recovery-is-not-possible-after-mssql-reboot/338793/3 "2023-08-16T16:03:32Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.