Recently, one of alwaysON cluster had issue, cause of file share witness box down and we brought up the box online. Errors: The Cluster service failed to bring clustered service or application completely online or offline. One or more resources may be in a failed state. This may impact the availability of the clustered service or application. Cluster resource ‘File Share Witness’ of type ‘File Share Witness’ in clustered role ‘Cluster Group’ failed Based on the failure policies for the resource and role, the cluster service may try to bring the resource online on this node or move the group to another node of the cluster and then restart…
-
-
AlwaysON availability group status alert notification Automon DBA SPs – AM13
This script are used to collect SpServerDiagnostics results on target server, when AG is not working. Additional SP: Sp1 USE [DBAdata] GO /****** Object: StoredProcedure [dbo].[usp_SpServerDiagnostics_new] Script Date: 15-04-2017 14:29:39 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /* CREATE TABLE [dbo].[SpServerDiagnostics]( [create_time] [datetime] NULL, [component_type] [sysname] NOT NULL, [component_name] [sysname] NOT NULL, [state] [int] NULL, [state_desc] [sysname] NOT NULL, [data] [nvarchar](max) NULL ) */ --DROP PROC [dbo].[usp_SpServerDiagnostics] alter proc [dbo].[usp_SpServerDiagnostics_new] (@Server_name varchar(50)) --with Encryption as begin EXEC('Exec ['+@Server_name+'].MASTER.DBO.usp_SpServerDiagnostics') END GO Sp2 USE [DBAdata] GO /****** Object: StoredProcedure [dbo].[usp_SpServerDiagnostics] Script Date: 15-04-2017 14:29:31 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /* CREATE TABLE [dbo].[SpServerDiagnostics]( [create_time] [datetime] NULL,…
-
AlwaysON availability group status alert notification Automon DBA SPs – AM12
This script will check the always ON group and database status and notify an alert when the AG or database is not online and disconnected. Table: use DBAdata go CREATE TABLE [dbo].[tbl_alwayson_monitoring]( [replica_server_name] [nvarchar](256) NOT NULL, [Group_name] [nvarchar](256) NOT NULL, [role_desc] [nvarchar](60) NULL, [operational_state_desc] [nvarchar](60) NULL, [recovery_health_desc] [nvarchar](60) NULL, [synchronization_health_desc] [nvarchar](60) NULL, Upload_date datetime ) use DBAdata_archive go CREATE TABLE [dbo].[tbl_alwayson_monitoring]( [replica_server_name] [nvarchar](256) NOT NULL, [Group_name] [nvarchar](256) NOT NULL, [role_desc] [nvarchar](60) NULL, [operational_state_desc] [nvarchar](60) NULL, [recovery_health_desc] [nvarchar](60) NULL, [synchronization_health_desc] [nvarchar](60) NULL, Upload_date datetime ) SP: /* --drop table tbl_alwayson_monitoring select * from tbl_alwayson_monitoring use DBAdata go CREATE TABLE [dbo].[tbl_alwayson_monitoring]( [replica_server_name] [nvarchar](256) NOT NULL, [Group_name] [nvarchar](256) NOT NULL, [role_desc] [nvarchar](60)…
-
AlwaysON Quorun down status alert notification Automon DBA SPs – AM11
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…