sql

11 Topics

SQL Stored Procedure Monitoring
Hello, I have a SQL stored procedure that I need to monitor. The stored procedure is scheduled to run evey hour and I need to monitor and alert on any results that do not have 'Success' in the EventStatusDesc column. What is the best approach for monitoring this?
autarch
16 days ago Place Product Discussions
154Views
0likes
1Comment
Can LM run a SQL Query against a specific Server/Database and alert based on the results?
Hi, I had a request for this today, but I've never done anything like this, so I'm not sure If it's possible or how to do it. The original request was to run a certain query and create an alert if it returns any results. I'm assuming that normally this table is blank, and if there's anything in it, that means something is broken, behind, etc, so they want to know. I'm guessing we could either treat it like an Eventsource where any results just makes a stand alone alert every time it runs. Or I could have them modify the query to return a Count of results and then maybe set it up like a Datasource with thresholds based on the Count that is returned. So that brings me to: Is something like this possible? Does anyone have a tutorial or example on how I'd set something like this up? I have a lot of experience using LM with the stuff it comes with, but I have little experience creating new source from scratch or doing scripting or anything like that. Thanks!
Kelemvor
2 months ago Place Product Discussions
294Views
10likes
5Comments
SQL Server Services Status
Hi, We have table widget setup to show SQL Server Service Status; the columns seem to be: RunningStatus State Status What is the difference because all show a ‘1’ at the moment. Also, can you manipulate the values to show ‘Running’, ‘Stopped’, ‘Disabled’ (I’m assuming these match to 1, 2, 3 respectively)? Thanks
Solved
ldoodle
2 years ago Place Product Discussions
198Views
4likes
2Comments
Missing JDBC connection string
Hello, I have recently seen a few of these tickets com across the board and was wondering if anyone would be able to give me a quick breakdown as to what this actually is and what the benefits would be to add this into the instance. SQL Server Troubleshooter-MSSQLSERVER Missing JDBC connection string. Manually add one to mssql.mssqlserver.mssql_url or check login informations exitCode A Microsoft SQL server has configuration problems or a logon error. MSSQLSERVER Missing JDBC connection string. Manually add one to mssql.mssqlserver.mssql_url or check login informations Thanks in Advance!
starboy9
5 years ago Place Product Discussions
859Views
1like
2Comments
SQL Mirror DB alerting
We have a unique SQL setup that is using mirroring. How can we configure LM to not show the databases that are in (Mirror, Sychronized/Restoring...) mode as not 'down'.
JeffH
7 years ago Place Archive
40Views
0likes
7Comments
I wrote this "how to" article on alert when a MS-SQL 'job' fails
DESCRIPTION: This will alert you via LogicMonitor when a MS-SQL ‘job’ fails INSTRUCTIONS: Create a LogicMonitor “EventSource”. Named “SQL job failures” or similar. I suggest that you clone the default “SQL eventsource”. Set these settings: LogName = “Application” SourceName = “SQLSERVERAGENT” Level more urgent than “Information” Use Microsoft’s SQL Management Studio to set each job on each of your SQL servers so that when a job fails, it will write a message to the Windows Application event log (screenshot below) Right click on job and click “Properties” Click on “Notifications” on left pane Click on “Write to Windows event logs” checkbox and click “When the job fails” Test. I suggest you create a job with a bogus query and run it. Look in the Windows Event viewer and you should notice an EventID 208 at “warning” severity. Optional: In LogicMonitor you can create an Alert rule that notifies a specific person or team. Do this by selecting the datasource name of the eventSource you named above Note: I developed this and tested it with SQL version 2008-R2 but it will probably also work with newer versions. Below is screenshot showing how to create/clone a LogicMonitor EventSource Below shows how to set “Write to event log” on each SQL Job Below shows what the alert looks like in LogicMonitor.
Mike_Suding
7 years ago Place Archive
46Views
1like
2Comments
Microsoft_SQLServer_SystemJobs_Troubleshooter DataSource
A hack of the Microsoft_SQLServer_SystemJobs datasource that will alert you in the event that the available credentials cannot gather SQL System Jobs. In brief, it attempts the same SQL query that the original DataSource runs, but creates no instances on a success - in the event of a failure, it will generate one instance whose description will be the error message, and one datapoint will be applied that will trigger a warning alert after a couple of minutes. It'll look a bit like this where the SQL query fails: Note this *only* tries the query for System Jobs ('select * from msdb.dbo.sysjobs') and I created this only when I noticed we were getting SQL database data, but not system jobs, from some customer devices. v1.1.0: 33H94M
Antony_Hawkins
7 years ago Place Archive
20Views
0likes
0Comments
Alternate method of tracking Availability Group synchronization.
Hello all! I just recently created a monitor to track the amount of time in seconds the secondary replicas are behind the primary and I figured I would share my solution. This will display the span of time in which past transactions could be lost in the event of a fail over. This assumes the secondaries are asynchronous. We are using SQL 2016, I do not know if this will work on earlier versions of SQL. I created a datasource and set the "Applies to" to the server in question. I set the collector to "SCRIPT" and I set the datasource to multi-instance and enabled active discovery. I used JDBC as the discovery method and Instance List as the discovery type. The details are as follows: Connection String: jdbc:sqlserver://##HOSTNAME##;IntegratedSecurity=true; SQL Statement: (This query gets the name of each secondary replica) SELECT CS.replica_server_name FROM sys.availability_groups_cluster AS C INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS CSON CS.group_id = C.group_id INNER JOIN sys.dm_hadr_availability_replica_states AS RS ON RS.replica_id = CS.replica_id WHERE RS.Role_desc <> 'PRIMARY' I wrote a script in Powershell that actually pulls the data that I need from the server itself. Powershell Script: [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null $SqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server("##HOSTNAME##") $SqlServer.AvailabilityGroups["your ag name here"].DatabaseReplicaStates | Where-Object {$_.IsLocal -eq $false -and $_.AvailabilityReplicaServerName -eq "##WILDVALUE##" -and $_.AvailabilityDatabaseName -eq "your server name here"} | Select -ExpandProperty EstimatedDataLoss In our environment there is only one database on the server that we are concerned with for tracking this information so I have not explored monitoring multiple databases. This method pulls the same data that is in the Estimated Data Loss(seconds) column in the Availability Groups dashboard in SQL Management Studio. In the event you wanted to track multiple databases on the secondaries, you will likely have to create a separate datasource for each secondary server you want to track. Additionally you would have to adjust the JDBC query to enumerate the databases on the server and the powershell script accordingly. I hope this helps someone. Thanks, Kyle
kholbrook
7 years ago Place Archive
48Views
1like
2Comments
SQL 2016 Databse Sync HA Monitor
Datasource with powershell script to monitor the Sync status of a specific database in a SQL 2016 High Availability instance. I noticed databases can fall into NotSynchronizing status on secondary nodes often without much alerting around it. The datasource monitors per database. If someone has a better way that will take in the status of all databases on the instance I am open to suggestions LM Locator: 6YD2C9
sawyer_lef
7 years ago Place Archive
8Views
0likes
3Comments
File / Database Size Monitoring
Is there a good datasource out there to monitor the size on disk of a specific file or folder? I would love the Microsoft Exchange datasource (or other) to be able to deliver and track the total size of the database. Thoughts? Will
WillFulmer
7 years ago Place Archive
53Views
0likes
6Comments