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]…
-
Big transaction log file space usage alert notification Automon DBA SPs – AM07
This script will check the large Transaction Log file used and notify an alert based on the threshold. Table: USE [DBAdata] GO drop TABLE [dbo].[tbl_get_logfiles_Huge] CREATE TABLE [dbo].[tbl_get_logfiles_Huge]( [servername] [sysname] NULL, [dbname] [sysname] NOT NULL, [filename] [sysname] NOT NULL, [recovery_model] [sysname] NOT NULL, [log_size] [int] NULL, [log_reuse_wait_desc] [sysname] NOT NULL, [freespace] [varchar](50) NULL, [Drive_letter] [sysname] NULL ) USE [DBAdata_Archive] GO drop table tbl_get_logfiles_Huge CREATE TABLE [dbo].[tbl_get_logfiles_Huge]( [servername] [sysname] NULL, [dbname] [sysname] NOT NULL, [filename] [sysname] NOT NULL, [recovery_model] [sysname] NOT NULL, [log_size] [int] NULL, [log_reuse_wait_desc] [sysname] NOT NULL, [freespace] [varchar](50) NULL, [Drive_letter] [sysname] NULL, [Upload_date] [datetime] NULL ) SP: USE [DBAdata] GO /****** Object: StoredProcedure [dbo].[Usp_dba_send_logfiles_huge] Script Date:…