George Zhou Zhiqing

 GENERATE RSA KEYS WITH SSH BY USING PUTTYGEN  5.12.3.9.1 Autologin (TFDBA runbook Unix/Linux Tips) 1. Use puttygen to create keys, move mouse to create random 2. Save keys public and private, if doesn’t want passphrase to login using keys, ignore the passphrase 3. Copy and paste the output on the screen of puttygen to clipboard (for HPUX use "...\putty\PuttyKeysPublicPrivate\authorized_keys" or linux one for linux,  open and copy and paste) 4. In Linux Server, under your name, create $HOME/.ssh/authorized_keys file and paste step 3 to it and save Same as HPUX, for root it is /.ssh/authorized_keys, append the keys (paste below existing if there is) 5. In putty ->connection->SSH->Auth load the private key  :\putty\PuttyKeysPublicPrivate\testCernerPrivateKey.ppk 6. Save session. (connection->data_autologin name, add your loginname)

 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...

 Without grant Drop any table, I used the following script 1. create a stored procedure 2. grant execute on that procedure to that user  create or replace procedure userx.truncateTable( p_tname in varchar2 ) as begin execute immediate 'truncate table ' || p_tname;  --DDL, implicit commit end;  CREATE TABLE userx.TEMP11    (x NUMBER(2,0),     x2 VARCHAR2(20 CHAR),     x3 VARCHAR2(20 CHAR),     x4 VARCHAR2(20 CHAR),       x5 VARCHAR2(20 CHAR)       ); grant execute on   userx.truncateTable to usery; have usery run  execute  userx.truncateTable ('Temp11');

 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.

  SQL server agent password change, not require reboot, but requires retype AD user when reboot in the future when I change sql agent service AD user password to meeting security requirement, I used the below powershell script and change all my 200+ sql instances sql agent service, no service restart required. Today just found after I done a patching on one of my clusters, the sql agent service didn't start, I thought the password was old, and retype pwd, still failed to start, then I tried to reload the AD user by search user name in AD and type the password, it works. script : [System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SqlWmiManagement”) | out-null $text=Get-Content C:\sql\serverlist0.txt $TotalComputers=$text.Length write-host "the total number of computers is $TotalComputers" For ($i=0; $i -lt $text.Length; $i++) {     # write-host " the server name is $text[$i] being working on "     $SMOWmiserver = New-Object (‘Microsoft.SqlServ...

 1. on premise license covers by enterprise server with SA 2. Office 365 E5 license covers powerbi Pro 10GB limit, data can be refreshed max 8 times a day 3. free version is for single user 10GB limit

 free try - covers synchronize millio files(not remmeber exact limit) price is about $30 it can synchronize between files on different server, I am using it to synchronize my documentation on my company personal drive and local pc. it can also synchronize with mapped SharePoint document library on premise. .... to synchronize SharePoint on cloud , then use one drive, free to synchroize with google drive, use Backup and Sync

 just added a dashboard in newer version of powerbi desktop, looks much easier than the one I used before (May2019), noticed the reports requires newer version of powerbi report server, make sense.  so I download it https://www.microsoft.com/en-us/download/details.aspx?id=56722 run it and select upgrade, it requires a reboot. still complaining the version, searched online and found silimar post: https://community.powerbi.com/t5/Report-Server/Report-was-created-with-recent-version-of-Power-BI-Desktop-that/m-p/1466731 

 We have a IT maintenance plan Excel spreadsheet, like to present a calendar view of it. Step 1. open PowerBI Desktop step 2. Get Data from Excel spreadsheet, not sure why it does't recognize when I copy the Sharepoint folder URL so I use the One Drive synchornized folder in local machine step 3. From Market place, search Calender, add Custom Calendard by Akavelon. step 4. add the date and value, I use the target date as date, choose the date instead of the date hirachey which is default. I use number of events as value step 5 add a table visualization and add required fields. step 6, add some filters and advanced filter (Relative date filtering) . step 7. publish it to myworkplace powerbi step 8, in Teams, click + and choose powerbi, then find the report created in step 1-7.

 1 Determine current patch level D:\oracle\product\12.2.0\dbhome_1\OPatch>opatch lsinventory -patch -detail Oracle Interim Patch Installer version 12.2.0.1.6 Copyright (c) 2021, Oracle Corporation.  All rights reserved. Oracle Home       : D:\oracle\product\12.2.0\dbhome_1 Central Inventory : C:\Program Files\Oracle\Inventory    from           : OPatch version    : 12.2.0.1.6 OUI version       : 12.2.0.1.4 Log file location : D:\oracle\product\12.2.0\dbhome_1\cfgtoollogs\opatch\opatch2021-07-09_12-46-58PM_1.log Lsinventory Output file location : D:\oracle\product\12.2.0\dbhome_1\cfgtoollogs\opatch\lsinv\lsinventory2021-07-09_12-46-58PM.txt -------------------------------------------------------------------------------- Local Machine Information:: Hostname: xxxx ARU platform id: 233 ARU platform description:: Microsoft Windows (64-bit AMD) There are no Interim patches installed in thi...

 To get deadlocking info in log , one of my practice is: I enabled the trace flag   DBCC TRACEON(1204, -1)  DBCC TRACEON(1222, -1)  ----select deadlocks SELECT    xed.value('@timestamp', 'datetime2(3)') as CreationDate,    cast(xed.query('.').value('(/event/data/value/text())[1]', 'nvarchar(MAX)') AS xml) AS XEvent FROM (    SELECT CAST([target_data] AS XML) AS TargetData    FROM sys.dm_xe_session_targets AS st       INNER JOIN sys.dm_xe_sessions AS s          ON s.address = st.event_session_address       WHERE s.name = N'system_health'          AND st.target_name = N'ring_buffer' ) AS Data CROSS APPLY TargetData.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData (xed) ORDER BY CreationDate DESC also uses Amit's method https://troubleshootingsql.com/2012/09/06/system-health-session-and-deadlocks/ 

 I created a windows task scheduler job and let it run daily to check disk free space on all 200+ database servers I managed and sent email alerts when free space is lower than given criteria. (btw, I have a nightly job to collecting all free space and save in a database, will blog that later) first export the list of server names to c:\sql\diskspaceServerList.txt then copy below to a file named diskspace1.ps1 then create windows task scheduler job and run this script at the time you specified. $text=Get-Content 'c:\sql\diskspaceServerList.txt' $receipients='dba@transformsso.ca' <#,'Husam.Ibrahim@transformsso.ca','servicedesk@transformsso.ca'#> $lowPercent="20" $TotalComputers=$text.Length write-host "the total number of computers is $TotalComputers" $body="" For ($i=0; $i -lt $text.Length; $i++) {     $computername= $text[$i].ToString()     Write-Host "current check computer $computername";     if ( ([string...

 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'             ...

For the 200+ SQL servers I managed,  I use a few methods to check disk free space, 1. nightly SSIS job to collect didsk space and report each morning(blog later) 2. scheduled alert job using powershell script (blog later) 3. adhoc check the free space of disk /mount point folder  remotely :  Get-WmiObject -Class win32_volume -ComputerName mySQLserverNames| Select-Object Name,Label,@{N='free';E={[math]::round($_.freespace / 1GB,2)}}, @{N='capacity';E={[math]::Round($_.capacity / 1GB,2)}}

  script to stop/disable spooler service on all servers not required regarding the Print Nightmare vulnerability,  I have deal with all 226 sql servers and 12 oracle servers on windows, so resue one of my ps script to stop /disable spooler service on all servers. this also helps to disable spooler on some of other 10000+ windows machines we have.  Windows Print Spooler Remote Code Execution Vulnerability  https://msrc.microsoft.com/update-guide/vulnerability/CVE-2021-34527  step1, create a list of servers to change in text file with one servername in a line, saved it toc:\sql\serverlist0.txt step 2. copy below script to power script ISE step 3. run it, see screenshot $service = "Spooler" $text =Get-Content C:\sql\serverlist0.txt $TotalComputers = $text .Length write-host "the total number of computers is $TotalComputers " For ( $i = 0 ; $i -lt $text .Length; $i ++) { $Computer = $text [ $i ] Write-Host "Working on $...