Another Monday....another bleary-eyed white-knuckle drive to work, both hands wrapped around a coffee mug...which I forgot to fill....phone buzzes....crap I'm late again...nope...it's work: "The geodatabase isn't working. Did you know that?".
If only had I known. I could have, at my leisure, not on a day when I have 234 conference calls and 2156 meetings, fired up the vpn from home, quick server reboot.
There are a few "Service" health checks out there which test a GIS REST service and can fire an email when something is amiss, but that doesn't tell me if the problem is in the database or the application server, and all it does is tell me that the service is alive.
I'm probably the ONLY GIS SERVER ADMINISTRATOR IN THE ENTIRE WORLD that experiences this, but I often find that Windows Update or a weekend party in the data center leaves some wonky residue in SQL, and "something" ends up "hung up" somewhere, so the maps work, GIS server is running, but can't edit anything. I only hear about it when I least have time to deal with it.
The following, run as a SQL Agent Job, will perform a test edit against a feature class (versioned or not), delete it if everything is working, or send an email if it's not. You can run it hourly (as I do) and modify accordingly to only get one email (what if you don't care it's not working?). Note how the emails appear to come from Captain Kirk, thus increasing chances of being paid attention to. I'm sure there are more elegant ways of doing this, but I like to keep things low-tech.
BEGIN TRY
DECLARE @id as integer
EXEC dbo.next_rowid 'dbo', 'TRAILSIGN', @id OUTPUT;
SET QUOTED_IDENTIFIER ON
INSERT INTO [dbo].[TRAILSIGN]
(OBJECTID,
LOC_NAME,
shape)
VALUES(@id, 'ZZZ_HEALTH_CHECK_TEST_DELETE', geography::STPointFromText('POINT(-83.499752 35.687597)', 4269))
DELETE FROM TRAILSIGN WHERE LOC_NAME = 'ZZZ_HEALTH_CHECK_TEST_DELETE'
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
DECLARE @ErrorText NVARCHAR(4000);
DECLARE @ErrorDisclaimer NVARCHAR(4000);
SET @ErrorText = 'Dammit Jim! The Engines can''t take this! Something's wrong with the IandM Veg Database! '
SET @ErrorDisclaimer = 'The IandM Veg Database is having some sort of problem. Editing is disabled and read-access may not be working. '
SET @ErrorMessage = @ErrorText + @ErrorMessage + @ErrorDisclaimer
EXEC msdb.dbo.sp_send_dbmail
@profile_name='Captain Kirk',
@recipients='sasquatch@bigfoot.com',
@subject='Error in the IandM Veg Database',
@body = @ErrorMessage;
END CATCH;Combined with the python ArcGIS health checks, you can get a pretty robust error reporting matrix. Warp Speed!
