My Way to identify SQL AGENT SERVICE is running

Collect last date/time SQL agent job is running by using SSIS/SSRS very night to send email report for those date/time is more than 1 days old

Comments

Post a Comment

Popular posts from this blog

Sysaux tablespace is too big

Image
 today I received my report alert regarding one of Oracle database tablespace SYSAUX is over 80% (I created automated job using SSIS package to check tablespace usage daily) following steps to resolve it:  /*  2021.07.08  Documentation steps for troubleshooting Sysaux tablespace is huge --85% full  */   ---1. see what uses the most select occupant_name,occupant_desc, space_usage_kbytes/1024 MB from v$sysaux_occupants where space_usage_kbytes > 0 order by space_usage_kbytes desc; ---find it is SM/Advisor Server Manageability - Advisor Framework 25GB --now look for top 10 big objects   select * from (                select bytes/1024/1024 MB, blocks, s.SEGMENT_NAME, s.partition_name, s.segment_type, s.tablespace_name                  from dba_segments s                 where owner='SYS'             ...
Read more

Oracle blocking

 today one of our interface queued up, I got the call and noticed the table the interface was inserting was locked . I used following script to identify the blocker: select a.sid, a.serial#, a.process  from v$session a, v$locked_object b, dba_objects c  where b.object_id = c.object_id  and a.sid = b.session_id  and OBJECT_NAME=upper('xxxxxtablexxxxname');  then alter system kill session 'sid,servial#' immediate; the blocker was one developer run a stored procedure locking that table, but not commit and left sql developer open.
Read more

blocking in SQL server

 we have a vendor's application, has blocking happens very often, at lease once a week. Without proper tool to trace the application's activity to prove what we believe or what we should suggest to change, cannot get the transaction start as we noticed the blocking ,the statement already passed,  We noticed that the application kept 2 transactions open without commit or rollback, in the transactions, there are a few locks held on page, row levels ,and tens of other sessions were blocked.  submitted the request to Microsoft, suggested using the RCSI level, with the expense of tempdb size and some IO overload. to find the statements in extended event, I searched online https://www.sqlserver-dba.com/2019/09/how-to-read-and-parse-the-microsoft-sql-server-extended-event-log-file-xel-file.html  and used something below: --Read the Extended Event Log File output file and PARSE the returned xml format rows   DECLARE @path NVARCHAR(260);   ----retrieve the loca...
Read more