• AutoMon

    Automon linked server working status report alert notification Automon DBA SPs – AM25

    This script will check the Automon linked server status and notify an alert, when it is not running.   SP: /* use [DBAdata] drop table tbl_Linked_Server_Status go CREATE TABLE [dbo].[tbl_Linked_Server_Status]( [servername] varchar(200), error_no bigint, errors varchar(2000), Date datetime ) */ -- select * from tbl_Linked_Server_Status use [DBAdata] go -- Exec [DBAdata].[dbo].[Usp_Linked_server_Status_Check] --DROP PROC [Usp_Linked_server_Status_Check] alter PROCEDURE [dbo].[Usp_Linked_server_Status_Check] /* Summary: Check the SQL agent stats Contact: Muthukkumaran Kaliyamoorhty SQL DBA Description: Check the SQL agent stats ChangeLog: Date Coder Description 2013-jan-21 Muthukkumaran Kaliyamoorhty Updated the 2012 functionality */ --WITH ENCRYPTION AS BEGIN SET nocount ON -- select * from dbadata.dbo.tbl_Linked_Server_Status Truncate table dbadata.dbo.tbl_Linked_Server_Status DECLARE @server_name SYSNAME DECLARE @DESC SYSNAME --DECLARE @sql…

  • AutoMon

    Agent SQL service status report alert notification Automon DBA SPs – AM24

    This script will check the SQL server agent service status and notify an alert, when it is not running.   SP: /* use [DBAdata] drop table tbl_agent_Status go CREATE TABLE [dbo].[tbl_agent_Status]( [servername] varchar(200) not null primary key, instance_name varchar(200), Edition varchar (50), Status varchar (50), Date datetime, ) */ -- select * from tbl_agent_Status use [DBAdata] go -- Exec [DBAdata].[dbo].[Usp_Agent_Status] --DROP PROC [Usp_Agent_Status] alter PROCEDURE [dbo].[Usp_Agent_Status] /* Summary: Check the SQL agent stats Contact: Muthukkumaran Kaliyamoorhty SQL DBA Description: Check the SQL agent stats ChangeLog: Date Coder Description 2013-jan-21 Muthukkumaran Kaliyamoorhty Updated the 2012 functionality */ --WITH ENCRYPTION AS BEGIN SET nocount ON -- select * from dbadata.dbo.tbl_agent_Status Truncate table…

  • AutoMon

    Automon server error report alert notification Automon DBA SPs – AM23

    This script will check last day server error report and notify an alert.   SP: alter procedure USP_DBA_ErrorHandling_Report /* Summary: Send a error report of script Contact: Muthukkumaran Kaliyamoorthy SQL DBA Description: Send a error report of script ChangeLog: Date Coder Description 2013-jan-21 Muthukkumaran Kaliyamoorhty Updated the 2012 functionality */ --WITH ENCRYPTION AS BEGIN SET nocount ON --Send an email to DBA team ----------------------------------------------------------------- DECLARE @minid INT DECLARE @maxid INT DECLARE @servername varchar(100) DECLARE @Module_name varchar(100) DECLARE @error varchar(100) -- select * from dbadata.dbo.tbl_get_datafiles_size if exists ( select 1 from tbl_Error_handling where Upload_Date>=DATEADD(Day,-1,getdate()) and Module_name <>'Perfmon' group by Server_name,Module_name,[error_message] having count(*)>=1 ) begin DECLARE Svr_error_CUR CURSOR FOR select Server_name,Module_name, left([error_message],25)…

  • Performance

    How to find slow running T SQL query

    Top 10 ways to find slow running T SQL query Followings are ways to identity and capture the slow running queries in SQL server database.   1.Use common activity monitor and task manager to get some overview Right click the instance – Activity monitor 2.Use DMVs Use Glenn Berry’s DMV pack https://www.sqlskills.com/blogs/glenn/category/dmv-queries/ 3.Check wait stats Use Paul Randal’s DMV  https://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/ 4.Use Whoisactive Use Adam Machanic’s script https://sqlserverblogforum.com/dba/whoisactive-capturing-a-sql-server-queries-performance-tuning/ 5.Use SP_BiltZ Use Brent Ozar’s script https://www.brentozar.com/blitz/ 6.Use server side trace https://sqlserverblogforum.com/dba/how-to-use-server-side-trace-to-capture-running-query-t-sql/ 7.Use extended events https://blog.sqlauthority.com/2010/03/29/sql-server-introduction-to-extended-events-finding-long-running-queries/ 8.Use actual execution plan, if you have query In SSMS, check mark the actual execution plan and run the query to tune further. 9.Use query store, if…