# \[MSSQL module\] - Transaction log stop after few hours **URL:** \ **Category:** Beats\ **Tags:** metricbeat\ **Created:** [January 3, 2022, 8:01am UTC](https://discuss.elastic.co/t/mssql-module-transaction-log-stop-after-few-hours/293323 "2022-01-03T08:01:21Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![Delta32000](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/delta32000/32/99914_2.png) [@Delta32000](https://discuss.elastic.co/u/Delta32000)\ **Post date:** [January 3, 2022, 8:01am UTC](https://discuss.elastic.co/t/mssql-module-transaction-log-stop-after-few-hours/293323/1 "2022-01-03T08:01:21Z")
I was able to configure the mssql module on metricbeat. I use the ssl config for each Elasticsearch and Kibana. I was able to load the default dashboards in kibana. The thing is that I can see transaction log data for a short period of time : ![image](https://us1.discourse-cdn.com/elastic/original/3X/2/e/2e0c28903abc5726798c26bbc6b2966cbf09e442.png) (each time data starts to be printed is when I manualy restarted metricbeat) But I can see data for the Performance metric for the same period : ![image](https://us1.discourse-cdn.com/elastic/original/3X/c/8/c86741fa54ecd2ba40f4f77e9d072a055c1284b5.png) Plus, there is no error in journalctl and /var/log/metricbeat/metricbeat Is anybody knows why Transaction log data stop to be send to Elasticsearch and how can I fix it ? ---
**Author:** ![Mario\_Castro](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mario_castro/32/35107_2.png) [@Mario\_Castro](https://discuss.elastic.co/u/Mario_Castro)\ **Post date:** [January 4, 2022, 11:00am UTC](https://discuss.elastic.co/t/mssql-module-transaction-log-stop-after-few-hours/293323/2 "2022-01-04T11:00:21Z")
Hi @Delta32000 🙂 Can you check in Discover that you actually have data on those periods? What we want here is to know if it's an error in the dashboard visualization or the module is faulty for whatever reason. Also check that your `sys.dm_db_log_stats`, `sys.dm_db_log_space_usage` and `sys.databases` dbs in sql server is being updated. I mean, if you are not having transactions because you are mostly executing single statements and "selects", it might be the expected output. ---
**Author:** ![Delta32000](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/delta32000/32/99914_2.png) [@Delta32000](https://discuss.elastic.co/u/Delta32000)\ **Post date:** [January 6, 2022, 8:04am UTC](https://discuss.elastic.co/t/mssql-module-transaction-log-stop-after-few-hours/293323/3 "2022-01-06T08:04:34Z")
Hey @Mario_Castro Thanks for your reply ! For the same periode in Discover (with a filter mssql.transaction\_log.space\_usage.total.bytes:exists on metricbeat-\* index pattern) I can see that : ![image](https://us1.discourse-cdn.com/elastic/original/3X/d/b/dba47d508b88a08d9e022dbb7fbbb8c1d3d51f87.png) And with a filter on mssql.performance.logins\_per\_sec : ![image](https://us1.discourse-cdn.com/elastic/original/3X/a/6/a6778d8c963e065645326c08dae23713d490f595.png) I have checked sys.dm\_db\_log\_stats manually with any database\_id during a similar "STOP" period, and I can have data. I try to monitor a huge database with a lot of transaction per minute and with about a transaction\_log backup each 10 minutes. I tried to find any error on winlog / sql server / server itself and elastic host server but I can't relate anything with those blank space period... When I check with ```auto journalctl -xe --unit "metricbeat" | grep transaction_log ``` I have 0 row unless I restart metricbeat (then I have 1 more row each 10 sec as expected) ```auto sudo systemctl restart metricbeat.service ``` I am kinda new in the elastic stack world so maybe I missed something 🤔 ---
**Author:** ![Delta32000](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/delta32000/32/99914_2.png) [@Delta32000](https://discuss.elastic.co/u/Delta32000)\ **Post date:** [January 20, 2022, 10:19am UTC](https://discuss.elastic.co/t/mssql-module-transaction-log-stop-after-few-hours/293323/4 "2022-01-20T10:19:41Z")
[UPDATE] I tried running metricbeat on an other sqlserver instance with only mssql module enabled. I connected this metricbeat to a brand new elastic stack VM with only basics (not even the minimal security was set). And with this configuration I'm facing the same issue... after a random moment transaction\_log monitoring just stops when the performance one continue his life. I'll try to connect two instances of metricbeat to the same mssql instance (and two Elasticsearch) just to see if both stop at the same moment. ---
**Author:** ![Mario\_Castro](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mario_castro/32/35107_2.png) [@Mario\_Castro](https://discuss.elastic.co/u/Mario_Castro)\ **Post date:** [February 8, 2022, 1:18pm UTC](https://discuss.elastic.co/t/mssql-module-transaction-log-stop-after-few-hours/293323/5 "2022-02-08T13:18:15Z")
Maybe it's a version mismatch. The Stack cannot be automatically tested to detect regressions to newer MSSQL versions. Which version are you using? That table uses the `mssql.transaction_log.space_usage.total.bytes` Elasticsearch field which is populated one by one by running `USE db; SELECT * FROM sys.dm_db_log_space_usage;` and getting the value of `total_log_size_in_bytes` which is the second column in a 2017 version. I'm thinking if the problem can be that it executed a `USE db` just before and somehow there's a race condition. Does that rings any bell? ---
**Author:** ![Delta32000](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/delta32000/32/99914_2.png) [@Delta32000](https://discuss.elastic.co/u/Delta32000)\ **Post date:** [February 14, 2022, 10:07am UTC](https://discuss.elastic.co/t/mssql-module-transaction-log-stop-after-few-hours/293323/6 "2022-02-14T10:07:21Z")
Actually we are running 3 sql server instances. Each one are in 12.0.6024.0, 12.0.6372.1 and 12.0.6164.21 version. I used the "hosts" parameter of mssql.yml to connect to both 12.0.6024.0 and 12.0.6372.1 using an sql server connection string. After a while restarting the module manually, it looks like the first one still successfully connected while the second one still "crashing" after a moment. When I try : ```auto USE [DB]; SELECT * FROM sys.dm_db_log_space_usage; ``` I can't see any difference between each table structure. ![image](https://us1.discourse-cdn.com/elastic/original/3X/d/2/d21110ebf2dd9d0f1b168e3de9e9692eaacaa7c3.png) Weird thing : The green part is from the first DB server and the grey one from the second. I left Metricbeat running on his own for a month without changing anything (same for DB servers). It seams that the grey part just poped again two weeks ago... (And that for all DB from the second server) ![image](https://us1.discourse-cdn.com/elastic/original/3X/3/6/36ae1e4879b42a5386036239de3265d924975744.png) I checked and we haven't proceeded any update. Zooming in we can notice a tiny amount of data before it actually works again... ![image](https://us1.discourse-cdn.com/elastic/original/3X/4/c/4cab366b0630e0cfccc03191aef6b4f598cea24b.png) Nothing append on sqlServer logs or Winlogs during that period. I also checked that we got enough disk space on the ELK server So for the moment it works fine but I still don't know what append to our data... Best regards, Dorian ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [March 14, 2022, 12:07pm UTC](https://discuss.elastic.co/t/mssql-module-transaction-log-stop-after-few-hours/293323/7 "2022-03-14T12:07:46Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.