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 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)…
-
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…
-
Automon auto maintenance mode alert notification Automon DBA SPs – AM22
This script will check the Automon server status, when the ping fails five times based on the run schedule, it will automatically place it on maintenance mode. SP: -- exec usp_ping_server_morethan_5_fail_status_change -- select * from DBA_All_servers where SVR_status<>'running' -- select * from tbl_Error_handling where Module_name='ping' alter proc usp_ping_server_morethan_5_fail_status_change /* Summary: Auto maintenance after 5 ping fails Contact: Muthukkumaran Kaliyamoorthy SQL DBA Description: Auto maintenance after 5 ping fails ChangeLog: Date Coder Description 2013-jan-21 Muthukkumaran Kaliyamoorhty Updated the 2012 functionality */ as BEGIN declare @count_s int select @count_s=count(*) from tbl_Error_handling E join DBA_All_servers A on (e.Server_name=a.Description) where Module_name='ping' and Upload_Date>=DATEADD(HH,-1,getdate()) --and a.SVR_status='running' group by Server_name having count(*)>=5 --select @count_s if…
-
Patch Update apply and automate SQL server patch for multiple servers patching compliance by DBATools Powershell
I just started recently using DBATools PowerShell modules. It is excellent and we can automate everything. When I started searching to patch multiple list of servers by PowerShell, I could found two blog post about automate patching. http://www.sqlnuggets.com/blog/patching-multiple-sql-servers-with-powershell-and-dbatools/ https://claudioessilva.eu/2018/01/23/using-dbatools-to-verify-your-sql-server-instances-version-compliance/ Pre requesting: The target remote server should have minimum PowerShell 3.0 You should install or update DBATools module with latest version on your desktop or CMS. If you have internet connection, you can directly install from gallery. If you do not have download the module and copy it just import it to PowerShell. https://dbatools.io/getting-started/ https://dbatools.io/join-us/guidelines/ https://dbatools.io/download/ Install-Module dbatools –Force If you do not have internet into a server, copy and…