• AutoMon

    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)…

  • AutoMon

    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]…

  • DBA

    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…

  • AutoMon

    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]…

  • AutoMon

    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…