Vision: SQL Homeostasis

You may and should have monitoring in place to monitor state of your servers, services, jobs, critical and not critical errors etc. Here I just wanted to share a quick script to create a SQL Server agent job to alert you whenever SQL Server service is started/restarted. If you have CMS setup, you could use it to deploy this across all SQL or most critical instances after tweaking it to meet your requirements -- For SQL 2005 or up -- Make sure it is running SQL 2005 or up IF ( @@microsoftversion / 0x1000000 ) & 0xff >= 9    PRINT 'This server is running SQL Server 2005 or up.' ELSE BEGIN    RAISERROR ( 'ERROR: This server is running SQL 2000 or older version, exiting...' , 16 , 1 )    return END   USE [msdb] GO EXEC msdb . dbo . sp_set_sqlagent_properties @alert_replace_runtime_tokens = 1 GO   SELECT NEWID () USE [msdb] GO -- IF THE SERVER IS DEV OR TEST, CHANGE THE CRITICAL OPERATO FROM PAGE TO JUST EMAIL DECLARE @CriticalOperator v...

If you are a DBA, especially Infrastructure DBA, Production DBA or Operations DBA then you don't necessarily always know what type of application load is being handled by your SQL Servers.  In some cases you don't even need to know to do your job. But if you are a performance engineer/DBA or one of your job description includes performance tuning and optimization then it is a very crucial piece of information. Here is one of the queries I have used.  SELECT   DB_NAME ( vfs . database_id ) dbname ,               mf . name file_name ,               mf . type_desc file_type ,         CASE WHEN num_of_writes = 0 THEN NULL                ELSE num_of_reads / num_of_writes END read_vs_writes ,         CASE WHEN num_of_bytes_written = ...

If you were like me, you would know your AG configuration by heart. However, what if you get busy with increasing responsibilities in other areas, projects etc.? Therefore, you do not just want to rely on your memory. On the other hand, maybe you want to simply collect snapshot of your configuration every now and then or have it documented before a major upgrades, for your junior DBAs, new hire etc... Alternatively, maybe you just started working for a new employer or a new client.  Whatever is your reason, here is a DMV query to gather the information. SELECT   ag . name ag_name ,         ar . replica_server_name replica_server ,         CASE WHEN hdrs . is_primary_replica = 0                   AND hdrs . replica_id = ar . replica_id THEN 0              ELSE 1 ...

-- Health and status of WSFC cluster. These two queries work only if the WSFC has quorum SELECT * FROM sys . dm_hadr_cluster SELECT * FROM sys . dm_hadr_cluster_members -- Health of the AGs SELECT ag . name agname , ags .* FROM sys . dm_hadr_availability_group_states ags INNER JOIN sys . availability_groups ag ON ag . group_id = ags . group_id -- Health and status of AG replics from the WsFC perspective SELECT ar . replica_server_name , harc .* FROM sys . dm_hadr_availability_replica_cluster_states harc INNER JOIN sys . availability_replicas ar ON ar . replica_id = harc . replica_id -- Health and status of AG replicas, run this on the primary replica.  -- On secondary this will only show info for that instance SELECT * FROM sys . dm_hadr_availability_replica_states  -- Health and status of AG databases from the WSFC perspective SELECT * FROM sys . dm_hadr_database_replica_cluster_states  -- Health and status of AG databases, run this on the prima...