November 27, 2016 at 11:06 am
Hi all,
I would like to setup some basic monitoring my SQL servers , I would like to seek some opinions of the best (+ simplest) way to do so
q1) check SQL agent service status and start it if necessary (using window task scheduler + bat script/powershell script) ?
q2) check datafile and t-log sizes and warn if they are of certain size/limit ? (using sql agent or task sceduler + sqlcmd ?)
To side track abit
q3) are we able to restore a backup into a database of a different name ? I would like to test the usability of a backup, but i would like to restore + recovery as if it is a totally different database
q4) can i say
- if a backup device/drive for my t-logs backup is full, my t-log backup will fail, but my database will carry on working as it is and my t-log will not get truncated
- if my disk/drvice for my t-log itself is full, then my t-log cannot grow further and the database will get into read-only mode if the t-log cannot be expanded further
Regards,
Noob
November 27, 2016 at 3:31 pm
1 - Use PowerShell and Start-Service and Get-Service to check the status of your service. You can run a job to do this in Windows Scheduler.
2 - There are scripts here to check these items, schedule them in SQL Agent.
3 - Yes
4 - yes
November 28, 2016 at 10:58 am
Hi Steve,
Thanks for your help!
I have setup monitoring via task scheduler + powershell (get-service) on the SQLserver + SQLAgent service.
On point 2) So far , I have only thought of monitoring data/t-log size..
- Would it be possible for you to point to me what are some of the basic useful monitoring scripts I should be looking at ?
I also have AlwaysON AG setup, what should i be actually monitoring to check the if the replicas are in sync ?
sys.dm_hadr_availability_group_states
sys.dm_hadr_availability_replica_states
sys.dm_hadr_database_replica_states ?
Would you recommend monitoring the AG health via SQLPowershell instead ?
Regards,
Noob
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply