• 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://www.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://www.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

    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…

  • PowerShell

    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…

  • AutoMon

    Automon server report alert notification Automon DBA SPs – AM21

    This script will check the automon server status report and notify an alert.   SP: alter procedure USP_DBA_AutoMon_server_Report /* Summary: Send a server which are not monitored by tool Contact: Muthukkumaran Kaliyamoorthy SQL DBA Description: Send a server which are not monitored by tool 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 @SVR_status varchar(100) -- select * from dbadata.dbo.tbl_get_datafiles_size if exists (select Description,SVR_status from dbadata.dbo.DBA_All_servers where SVR_status<>'running') begin DECLARE Svr_running_CUR CURSOR FOR select Description,SVR_status from dbadata.dbo.DBA_All_servers where SVR_status<>'running' OPEN Svr_running_CUR FETCH NEXT…