Wenn du dich für Plugins (oder OMD oder allgemein Monitoring) interessierst oder von deiner Installation erzählen willst...die Tradition der deutschsprachigen Monitoring-Workshops wird wiederbelebt: https://discord.gg/jDfPZ63FcJ A plugin, which checks various parameters of Microsoft SQL database servers.
I have some deadlocks problem with the database-free mode in my environment. I think, it is related to the select on sysindexes. Maybe I have an solution for this. You should remove the select on sysindexes and use this instead:
use [?]
SELECT SUM(CONVERT(Decimal(15,2),ROUND(FILEPROPERTY(a.Name,'SpaceUsed')/128,2))) AS [Space Used (MB)]
FROM
[?].dbo.sysfiles a (NOLOCK)
WHERE
a.filename not like '%.ldf'
This works fine in my environment.
With this you could also add a new mode like "database-log-free"
Basically it is the same code as database-free, but you need to change the first query to:
use [?]
SELECT
SUM(CONVERT(Decimal(15,2),ROUND(FILEPROPERTY(a.Name,'SpaceUsed')/128,2))) AS [Space Used (MB)]
FROM
[?].dbo.sysfiles a (NOLOCK)
WHERE
a.filename like '%.ldf'
and the second query (to get the maxsize etc.):
SELECT
RTRIM(a.name), RTRIM(a.filename), CAST(a.size AS BIGINT),CAST(a.maxsize AS BIGINT), a.growth
FROM
[?].sys.sysfiles a
WHERE
a.groupid = 0
Maybe you have some time to "official" implement this, so I can use further updates of your plugin.
Hello Mr. Lausser,
I have some deadlocks problem with the database-free mode in my environment. I think, it is related to the select on sysindexes. Maybe I have an solution for this. You should remove the select on sysindexes and use this instead: use [?] SELECT SUM(CONVERT(Decimal(15,2),ROUND(FILEPROPERTY(a.Name,'SpaceUsed')/128,2))) AS [Space Used (MB)] FROM [?].dbo.sysfiles a (NOLOCK) WHERE a.filename not like '%.ldf'
This works fine in my environment.
With this you could also add a new mode like "database-log-free" Basically it is the same code as database-free, but you need to change the first query to: use [?] SELECT SUM(CONVERT(Decimal(15,2),ROUND(FILEPROPERTY(a.Name,'SpaceUsed')/128,2))) AS [Space Used (MB)] FROM [?].dbo.sysfiles a (NOLOCK) WHERE a.filename like '%.ldf'
and the second query (to get the maxsize etc.): SELECT RTRIM(a.name), RTRIM(a.filename), CAST(a.size AS BIGINT),CAST(a.maxsize AS BIGINT), a.growth FROM [?].sys.sysfiles a WHERE a.groupid = 0
Maybe you have some time to "official" implement this, so I can use further updates of your plugin.
Thanks!
marcelfischer