.::: Change Server Name in Microsoft SQL Server MSSQL :::.

1. Check before
select @@servername as ServerName, @@version as VersionSQL;
exec sp_helpserver;

 

 

.::: Set Limit Max Server Memory in SQL Server :::.

1. Check Memory server 

 

2. Capture before
SELECT @@servername as ServerName,[name],[value], [value_in_use], description
FROM sys.configurations
WHERE [name] = 'max server memory (MB)' or
      [name] = 'min server memory (MB)' ;

.::: Upgrade SQL Server 2019 to MSSQL 2022 :::.

1. Backup Database
2. Capture version before upgrade
---Open SSMS & Run Command to check version (Capture)
select @@servername as ServerName, @@version as VersionSQL;

-- select IP
SELECT  @@servername as ServerName,
   CONNECTIONPROPERTY('net_transport') AS net_transport,
   CONNECTIONPROPERTY('protocol_type') AS protocol_type,
   CONNECTIONPROPERTY('auth_scheme') AS auth_scheme,
   CONNECTIONPROPERTY('local_net_address') AS local_net_address,
   CONNECTIONPROPERTY('local_tcp_port') AS local_tcp_port,
   CONNECTIONPROPERTY('client_net_address') AS client_net_address

.::: Backup Database Full & Transaction & Restore Database SQL Server :::.

correlation with https://teguhth.blogspot.com/2023/06/backup-database-full-transaction.html

 

A. check status backup and restore

SELECT command, percent_complete,total_elapsed_time, estimated_completion_time, start_time
  FROM sys.dm_exec_requests
  WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE')
 

.::: Change License Key SQL Server 2019 from Evaluation to Enterprice :::.

A. Check before update License

 ---Open SSMS & Run Command to check version (Capture)
select @@servername as ServerName, @@version as VersionSQL;

-- select IP
SELECT  @@servername as ServerName,
   CONNECTIONPROPERTY('net_transport') AS net_transport,
   CONNECTIONPROPERTY('protocol_type') AS protocol_type,
   CONNECTIONPROPERTY('auth_scheme') AS auth_scheme,
   CONNECTIONPROPERTY('local_net_address') AS local_net_address,
   CONNECTIONPROPERTY('local_tcp_port') AS local_tcp_port,
   CONNECTIONPROPERTY('client_net_address') AS client_net_address
   

.:: Check ServerName, SQL Server Version, IP, compatibility_level, log_reuse_wait_desc, recovery_model_desc , Active Transaction :::.

---Open SSMS & Run Command to check version (Capture)
select @@servername as ServerName, @@version as VersionSQL;

-- select IP
SELECT  @@servername as ServerName,
   CONNECTIONPROPERTY('net_transport') AS net_transport,
   CONNECTIONPROPERTY('protocol_type') AS protocol_type,
   CONNECTIONPROPERTY('auth_scheme') AS auth_scheme,
   CONNECTIONPROPERTY('local_net_address') AS local_net_address,
   CONNECTIONPROPERTY('local_tcp_port') AS local_tcp_port,
   CONNECTIONPROPERTY('client_net_address') AS client_net_address

.::: Repair Unable to launch SQL Server Configuration Manager – Invalid class [0x80041010] :::.

1. Get Error open SQL SQL Server 2019 Configuration Manager
 

Here is the text of the error message:

—————————
SQL Server Configuration Manager
—————————
Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 and later servers with SQL Server Configuration Manager.
Invalid class [0x80041010]
—————————
OK 

.::: Check Hostname, IP & Version for MSSQL, MariaDB, MySQL, OracleDB, PostgreSQL from SQL Server LinkedServer :::.

1. MSSQL
select @@servername as ServerName,@@VERSION as VersionSQL from sys.databases;
select @@servername as ServerName,name,log_reuse_wait_desc, recovery_model_desc,compatibility_level,@@VERSION as VersionSQL from sys.databases
 

.::: Remove a node to existing MariaDB Galera Cluster :::.

 A. Temporary remove
1. check status existing galera status before remove. for 3 node
show status like 'wsrep_incoming_addresses';
show status like 'wsrep_cluster_weight';
show status like 'wsrep_cluster_size';
show status like 'wsrep_connected';

.::: Adding a node to existing MariaDB Galera Cluster :::.

1. additional hosts node at hosts <add node3= teguhth03>
[root@teguhth01 ~]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
10.10.10.15 loadbalancer
10.10.10.11 teguhth01
10.10.10.12 teguhth02
10.10.10.13 teguhth03
[root@teguhth01 ~]#

.::: How to Shrink Log Database SQL Server :::.

 1.check active transcation

select @@servername as ServerName,name,log_reuse_wait_desc, recovery_model_desc,compatibility_level,@@VERSION  from sys.databases
 

.::: Check Running Query In SQL Server/ Check Query Database Berjalan :::.

1. query 1 - full not simple

SET NOCOUNT ON   
SET ANSI_PADDING ON
SET QUOTED_IDENTIFIER ON  
DECLARE @record_id int, @SQLProcessUtilization int, @CPU int,@EventTime datetime--,@MaxCPUAllowed int   
select  top 1  @record_id =record_id,
      @EventTime=dateadd(ms, -1 * ((SELECT ms_ticks from sys.dm_os_sys_info) - [timestamp]), GetDate()),-- as EventTime,
      @SQLProcessUtilization=SQLProcessUtilization,
      --SystemIdle,
      --100 - SystemIdle - SQLProcessUtilization as OtherProcessUtilization,
      @CPU=SQLProcessUtilization + (100 - SystemIdle - SQLProcessUtilization) --as CPU_Usage
from (

 

.::: Reindex, Reorganize and Rebuild Indexes & Update statistics in SQL Server base on Fragmentation :::.

1. Check Database_ID (contoh database id 5 & 6 )
select @@Servername as ServerName,name as dbname,database_id  from sys.databases;
select @@Servername as ServerName,name as dbname,database_id  from sys.databases where name = 'teguhth';
select @@Servername as ServerName,name as dbname,database_id  from sys.databases where name = 'AdventureWorks2019';