Author: Abhishek Yadav
-
PG Basic Commands
PG Basic Commands ============================================================================================================== Create a User postgres=# create user user_name with encrypted password ‘mypassword’; CREATE USER s2user WITH PASSWORD ‘***************’; CREATE USER s2user WITH PASSWORD ‘***************’ VALID UNTIL 2040-12-01;CREATE USER s2user SUPERUSER LOGIN PASSWORD ‘12345’;CREATE USER s2user SUPERUSER LOGIN PASSWORD ‘12345’; postgres=# grant all privileges on database sample_db to user_name; ============================================================================================================== Insert a huge Data…
//
-
MySQL DBA Hand Book
Connect MySQL DB: mysql> mysql -u root –socket=/var/lib/mysql/mysql.sock -p Enter password:_ Check Running Process List: mysql> show full processlist \G Create User: mysql> create user ‘account_name’@’Ip_address or %’ IDENTIFIED BY ‘**********’; Alter a User: mysql> alter user ‘root’@’localhost’ identified by ‘Secure@123’; Grant Privilege’s to the Mysql User: mysql> grant all privileges on *.* to account_name; mysql> flush privileges; mysql> grant create, select, insert on *…
//
-
My SQL Innodb Cluster Setup
Server List clota-dbl05 @@version 8.0.32-commercial 192.168.0.156 My SQL root password Secure@123 ——————————————————————- ota-dbl06 <cota-dbl06> @@version 8.0.33 192.168.0.197 My SQL root password Secure@123 ——————————————————————- clota-dbl07 192.168.0.195 @@version @@version 8.0.33 My SQL root password Secure@321 ——————————————————————- Step 1: Modify /etc/hosts with IP and server_name on all the nodes… Like below vim /etc/hosts 192.168.0.156 clota-dbl05 192.168.0.197 ota-dbl06 192.168.0.195 clota-dbl07…
//
-
-
Copy User DB and System Data and Log Files from One Drive to Another Due to Disk Slowness Issue
Hi guys, in this blog I am going to explain you about copy User DB and System Data and Log Files from One Drive to Another Due to Disk Slowness Issue. Issue- Client Disk Slowness Issue. Solution- These guys set up a 1TB SSD in the server and made partitions from it as well. In…
//
-
Scan SQL Server Error Log
Scan SQL Server Error Log create table #t_789(Log_date datetime,Process_Info Nvarchar(1000),Txt Nvarchar(3500) ) insert into #t_789 exec sp_readerrorlog; insert into #t_789 exec sp_readerrorlog 1; insert into #t_789 exec sp_readerrorlog 2; insert into #t_789 exec sp_readerrorlog 3; select * from #t_789 where Txt like ‘%error %’; Scan Current Error Log –create table #t_789(Log_date datetime,Process_Info Nvarchar(1000),Txt Nvarchar(3500) ) insert into #t_789 exec sp_readerrorlog; select Log_date,txt from #t_789 order by 1 desc ; truncate table #t_789
//
-
Database Maintenance
Index Fragmentation Info –All Indexes select object_schema_name(ps.object_id) as ObjectSchema, object_name (ps.object_id) as ObjectName, ps.object_id ObjectId, i.name as IndexName, ps.avg_fragmentation_in_percent, ps.page_count from sys.dm_db_index_physical_stats(db_id(), null, null, null, null) ps inner join sys.indexes i on i.object_id = ps.object_id and i.index_id = ps.index_id where avg_fragmentation_in_percent > 10 and ps.index_id > 0 and page_count >100 order by page_count desc –Specific Index select object_schema_name(ps.object_id) as ObjectSchema, object_name (ps.object_id) as ObjectName, ps.object_id ObjectId, i.name as IndexName, ps.avg_fragmentation_in_percent, ps.page_count from sys.dm_db_index_physical_stats(db_id(), null, null, null, null) ps inner join sys.indexes i on i.object_id = ps.object_id and i.index_id = ps.index_id where ps.index_id > 0 and i.name like ‘Index_Name‘ order by 5 desc —————————————————————- ————————————–> use Database_Name ————————————– Declare @TBname nvarchar(255), @schema nvarchar(255), @DB_NAME nvarchar(255), @SQL nvarchar(max) DECLARE @db_id SMALLINT; DECLARE @object_id INT; ————————————–> SET @DB_NAME =…
//
-
ALL About TempDB
For TempDB –Determining the amount of free space in tempdb USE tempdb; SELECT SUM(unallocated_extent_page_count) AS [free pages], (SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB] FROM sys.dm_db_file_space_usage; –Determining the Amount of Space Used by User Objects USE tempdb; SELECT SUM(user_object_reserved_page_count) AS [user object pages used], (SUM(user_object_reserved_page_count)*1.0/128) AS [user object space in MB] FROM sys.dm_db_file_space_usage; use tempdb SELECT GETDATE() AS runtime ,(SUM(user_object_reserved_page_count) * 8)/1024 AS usr_obj_mb ,SUM(internal_object_reserved_page_count) * 8 AS internal_obj_kb ,(SUM(version_store_reserved_page_count) * 8)/1024 AS version_store_mb ,(SUM(unallocated_extent_page_count) * 8)/1024 AS freespace_mb ,(SUM(mixed_extent_page_count) * 8)/1024 AS mixedextent_mb FROM sys.dm_db_file_space_usage use tempdb SELECT session_id, SUM(internal_objects_alloc_page_count) AS NumOfPagesAllocatedInTempDBforInternalTask, SUM(internal_objects_dealloc_page_count) AS NumOfPagesDellocatedInTempDBforInternalTask, SUM(user_objects_alloc_page_count) AS NumOfPagesAllocatedInTempDBforUserTask, SUM(user_objects_dealloc_page_count) AS NumOfPagesDellocatedInTempDBforUserTask FROM sys.dm_db_task_space_usage GROUP BY session_id ORDER BY NumOfPagesAllocatedInTempDBforInternalTask DESC, NumOfPagesAllocatedInTempDBforUserTask DESC…
//
-
CDC in SQL Server
A brief description of SQL Server’s CDC- Change Data Capture, or CDC for short, was initially introduced in the SQL Server 2008 edition as a useful feature to track and record changes made to SQL Server database tables without requiring extra programming work. Prior to SQL Server 2016, a SQL Server database could only have…
//
-
Network actual bandwidth between servers.
Iperf: Download the tool from https://iperf.fr/iperf-download.php Keep it on both source and target server. Copy code iperf3 -s On server (server2){Cline machine}, run: Copy code iperf3 -c server1_IP
//