create an alert | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

create an alert

Hi,
How can I create an alert to test if the database is up?
I am beginner in sql server.
Thanks in advance,
joanne
You can run the following query from your client application against the server: SELECT CATALOG_NAME
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE CATALOG_NAME = ‘MyDBName’ If the database is not available on the server to which you’re connecting, then the resultset will not have any rows.
With Adriaan Query, you can create a schedule job to send a mail to you. Just a thougth.
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
This is very nice.It work fine
But if i want to know if sql server is up?
Thanks again,
joanne
There is no alarm to that.
But, you have severals tools to connect (I think you are talking from outside net) via web to test if sql is up, i.e: Terminal services.
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
No, i want to create a job witch verify at every 30 minutes if sql server is up
thanks again,
joanne
Sql Server Agent has an option to restart Sql Server if it stops unexpectedly. You should also configure agent to run constantly. As Adriaan says, you could create a job which runs some arbitrary SELECT on your database. If the agent job fails, it could then send you an email. Although the sql server and agent may be running, its conceivable that it still might not be accessible for some reason (network problems etc) and therefore appear ‘down’ to users. So for a true test, you might want to run the test from a machine external to the sql server. You could have Windows scheduler schedule it to run every so often, connect to the sql server, and then disconnect, mailing you if it is unable to connect
Just a thought. One way might be to pipe the results of<br /><pre><br />EXEC master..xp_cmdshell ‘sc.exe \WST30LT009707 query MSSQL$Frank'<br /><br />output <br />———————————————————————- <br />NULL<br />SERVICE_NAME: MSSQL$Frank<br /> TYPE : 10 WIN32_OWN_PROCESS <br /> STATE : 4 RUNNING <br /> (STOPPABLE,PAUSABLE,ACCEPTS_SHUTDOWN)<br /> WIN32_EXIT_CODE : 0(0x0)<br /> SERVICE_EXIT_CODE : 0(0x0)<br /> CHECKPOINT : 0x0<br /> WAIT_HINT : 0x0<br />NULL<br /><br />(10 row(s) affected)<br /></pre><br /><br />and query for STATE 4 (running). You might also be able to use the scm utility instead of sc. However I can’t figure out how to use scm. [<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />]<br />If you live on the wild side [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />], you can use the undocumented extended procedure xp_servicecontrol like that:<br /><br /><pre><br />EXEC master..xp_servicecontrol ‘querystate’, ‘MSSQL$Frank'<br /><br />Current Service State <br />——————————- <br />Running.<br /><br />(1 row(s) affected)<br /></pre><br /><br />HTH<br /><br />———————–<br />–Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />———————–<br />
Forget to mention, don’t know what happens when you try this while the service is down. ———————–
–Frank
http://www.insidesql.de
———————–
Joanne:
Sqlagent is who control and run Jobs. SQL server could be up and Sqlagen down, no vice versa.
So no one job can tell you if SQL server is up, when all is down.
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
]]>