This script will check the always ON quorum status and notify an alert when the quorum is not down and disconnected. Table: use dbadata go drop table tbl_alwayson_Quorum go CREATE TABLE [dbo].[tbl_alwayson_Quorum]( [server_name] [nvarchar](256) NOT NULL, [Member_name] [nvarchar](256) NOT NULL, [Status] [varchar](10) NOT NULL, [Vote] [int] NULL, [Upload_date] [datetime] NULL ) use DBAdata_Archive go drop table tbl_alwayson_Quorum go CREATE TABLE [dbo].[tbl_alwayson_Quorum]( [server_name] [nvarchar](256) NOT NULL, [Member_name] [nvarchar](256) NOT NULL, [Status] [varchar](10) NOT NULL, [Vote] [int] NULL, [Upload_date] [datetime] NULL ) SP: /* --drop table tbl_alwayson_Quorum select * from tbl_alwayson_Quorum use dbadata go drop table tbl_alwayson_Quorum go CREATE TABLE [dbo].[tbl_alwayson_Quorum]( [server_name] [nvarchar](256) NOT NULL, [Member_name] [nvarchar](256) NOT NULL, [Status]…
-
-
How to use server side trace to capture running query T SQL
Capturing queries by using server side trace In general, DBA will have a report most of time the server and application web is running slow. You have to find yourself what query is running slow and benchmark and tune it. Profiler trace is good to capture the running code. But it is a GUI and we have to start, capture and stop it. This will also make some performance slowness to the server and you it is hard to read and store data. The server side trace is another option and it is a lightweight and T-SQL code, we can create a stored procedure and schedule it on SQL server…
-
Database mirror status alert notification Automon DBA SPs – AM10
This script will check the database mirror status and notify an alert when the mirror is not working and disconnected. Table: use DBAdata go drop table [tbl_DB_mirroring_status] CREATE TABLE [dbo].[tbl_DB_mirroring_status]( [name] [sysname] NOT NULL, [principal_server_name] [nvarchar](128) NULL, [mirroring_role_desc] [nvarchar](60) NULL, [mirroring_partner_name] [nvarchar](128) NULL, [mirroring_witness_name] [nvarchar](128) NULL, [mirroring_state_desc] [nvarchar](60) NULL, [mirroring_witness_state_desc] [nvarchar](60) NULL, [upload_date] [datetime] NOT NULL ) use DBAdata_archive go drop table [tbl_DB_mirroring_status] CREATE TABLE [dbo].[tbl_DB_mirroring_status]( [name] [sysname] NOT NULL, [principal_server_name] [nvarchar](128) NULL, [mirroring_role_desc] [nvarchar](60) NULL, [mirroring_partner_name] [nvarchar](128) NULL, [mirroring_witness_name] [nvarchar](128) NULL, [mirroring_state_desc] [nvarchar](60) NULL, [mirroring_witness_state_desc] [nvarchar](60) NULL, [upload_date] [datetime] NOT NULL ) SP: /* use DBAdata go drop table [tbl_DB_mirroring_status] CREATE TABLE [dbo].[tbl_DB_mirroring_status]( [name] [sysname] NOT NULL, [principal_server_name]…
-
Database online status alert notification Automon DBA SPs – AM09
This script will check the database status and notify an alert when the database is not online. Table: use DBAdata go drop table [tbl_database_online_status] CREATE TABLE [dbo].[tbl_database_online_status]( [servername] [nvarchar](128) NULL, [dbname] [sysname] NOT NULL, [state_desc] [nvarchar](60) NULL, [upload_date] [datetime] NOT NULL ) select * from tbl_database_online_status use DBAdata_archive go drop table [tbl_database_online_status] CREATE TABLE [dbo].[tbl_database_online_status]( [servername] [nvarchar](128) NULL, [dbname] [sysname] NOT NULL, [state_desc] [nvarchar](60) NULL, [upload_date] [datetime] NOT NULL ) SP: /* use DBAdata go drop table [tbl_database_online_status] CREATE TABLE [dbo].[tbl_database_online_status]( [servername] [nvarchar](128) NULL, [dbname] [sysname] NOT NULL, [state_desc] [nvarchar](60) NULL, [upload_date] [datetime] NOT NULL ) select * from tbl_database_online_status use DBAdata_archive go drop table [tbl_database_online_status] CREATE TABLE…
-
SQL server status alert stop ping alert notification Automon DBA SPs – AM08
This script will try to connect the SQL service and notify an alert is it fails. Table: use dbadata go create table dbadata.dbo.dba_PingServers ( server varchar(255), uptime datetime , status varchar(255) --,loadtime datetime ) use dbadata_archive go create table dbadata_archive.dbo.dba_PingServers ( server varchar(255), uptime datetime , status varchar(255) , loadtime datetime ) SP: /* use dbadata go create table dbadata.dbo.dba_PingServers ( server varchar(255), uptime datetime , status varchar(255) --,loadtime datetime ) use dbadata_archive go create table dbadata_archive.dbo.dba_PingServers ( server varchar(255), uptime datetime , status varchar(255) , loadtime datetime ) */ USE [DBAData] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- select * from dbo.dba_PingServers -- Exec [DBAData].[dbo].[usp_prdServerPing_1]…