Category: L1 DBA
-
DBA Worries Continued DBMS Concepts DBMS Concepts DBMS Concepts L1 DBA Monitoring My SQL Performance Tuning PostgreSQL SQL Server
SQL Server Memory Architecture
There are four major components of SQL memory architecture… 1. Memory Broker2. Memory Object3. Memory Clerk4. Memory Node In short, BOCN. (For remember it “Boy Of Cartoon-Network”) 1. Memory Broker Purpose of Memory Broker is to provide a centralized mechanism to distribute/control memory the allocation made by each component in SQL Server. Note: Memory brokers do…
//
-
DBA Worries Continued DBMS Concepts DBMS Concepts DBMS Concepts High Availability L1 DBA Monitoring My SQL PostgreSQL SQL Server
Mirroring Witness getting disappear from Mirroring Setup…
Recently we have faced one issue where DB mirroring witness getting disappear from mirroring setup. This is happening once Principal get down or SQL services restarts. In the case, we found DB fail-over is also is not working. Once the Principal server come online the DB start serving role as Principal DB and get in…
//
-
Essential SQL Server Topics for Database Administrator
Below are SQL Server topics which are essential for Database Administrator… Server, DB Architecture ○ Server Architecture & Protocols ○ Query Processor & Storage Engine ○ Parser, Optimizer, SQL & DB Manager ○ File Manager and Transaction Services ○ IO & Lock Manager, MDAC, CLR, WAL ○ Buffer Manager, Lazy Writer, SQL OS ○ Linked…
//
-
DBA Worries Continued Installation and Configuration L1 DBA Monitoring Performance Tuning SQL Scripting SQL Server
Let’s talk about In memory OLTP
In-memory OLTP frequently referred to by its code name “Hekaton”, was introduced in SQL Server 2014 but it got more popular from SQL Server 2016. This feature was being developed by Microsoft to stay in the game for high speed OLTP transaction speed and recent hardware changes in terms of the cost and throughput. Many…
//
-
SQL Server Info (Inventory)
— SQL Server Info (Inventory) SELECT SERVERPROPERTY(‘ServerName’) AS ServerName, SERVERPROPERTY(‘MachineName’) AS MachineName, CASE WHEN SERVERPROPERTY(‘InstanceName’) IS NULL THEN @@SERVICENAME ELSE SERVERPROPERTY(‘InstanceName’) END AS InstanceName, SUBSTRING ( (SELECT @@VERSION),1, CHARINDEX(‘-‘,(SELECT @@VERSION))-1 ) as ProductName, SERVERPROPERTY(‘ProductVersion’) AS ProductVersion, SERVERPROPERTY(‘ProductLevel’) AS ProductLevel, SERVERPROPERTY(‘ProductMajorVersion’) AS ProductMajorVersion, SERVERPROPERTY(‘ProductMinorVersion’) AS ProductMinorVersion, SERVERPROPERTY(‘ProductBuild’) AS ProductBuild, SERVERPROPERTY(‘Edition’) AS Edition, CASE SERVERPROPERTY(‘EngineEdition’) WHEN 1 THEN ‘PERSONAL’ WHEN 2 THEN ‘STANDARD’ WHEN 3 THEN ‘ENTERPRISE’ WHEN 4 THEN ‘EXPRESS’ WHEN 5 THEN ‘SQL DATABASE’ WHEN 6 THEN ‘SQL DATAWAREHOUSE’ END AS EngineEdition, CASE SERVERPROPERTY(‘IsHadrEnabled’) WHEN 0 THEN ‘The Always On Availability Groups feature is disabled’ WHEN 1 THEN ‘The Always On Availability Groups feature is enabled’ ELSE ‘Not applicable’ END AS HadrEnabled, CASE SERVERPROPERTY(‘HadrManagerStatus’) WHEN 0 THEN ‘Not started, pending communication’ WHEN 1 THEN ‘Started…
//
-
Backup and Recovery DBA Worries Continued DBMS Concepts DBMS Concepts DBMS Concepts L1 DBA My SQL Performance Tuning PostgreSQL SQL Server
Ghost Records
When any record get deleted from DB, SQL Server does not delete the records physically from disk immediately. The deleted records which exists physically in storage called Ghost Records. SQL Server does not use synchronous method to delete the records physically for DML operation because if SQL Server delete the records synchronously, this will going to drastically…
//
-
Connect to SQL Managed Instance from an on-premises client computer by using a point-to-site connection
In this section, you will learn how to connect SQL managed instance from an on-premises client computer by using a “point-to-site” connection. Point-to-Site VPN connections are useful when you want to connect to your VNet from a remote location. Prerequisites: Follow below sequence steps by step to connect SQL Managed Instance from an on-premises client machine (wherein will…
//
-
DBCC (Database Console Command) Commands
It’s a common question of DBA interview. I already have faced millions of time. HaHa Ha. DBCC is a series of statements in Transact-SQL programming language to check the physical and logical consistency of SQL Server database. We can divide in 4 major category or type… DBCC command type… 1. Informational DBCC command 2. Validational DBCC command 3. Maintenance…
//
-
Azure Azure SQL Database Azure SQL Database Backup and Recovery Backup Tools for SQL Server DBA Worries Continued DBMS Concepts DBMS Concepts DBMS Concepts High Availability L1 DBA Monitoring My SQL Performance Tuning PostgreSQL Security SQL Scripting SQL Server
Important SQL Server Scripts…
Restore Progress Check… ========================================================================== DECLARE @DBName VARCHAR(64) = ‘ODS’ DECLARE @ErrorLog AS TABLE([LogDate] CHAR(24), [ProcessInfo] VARCHAR(64), [TEXT] VARCHAR(MAX)) INSERT INTO @ErrorLog EXEC master..sp_readerrorlog 0, 1, ‘Recovery of database’, @DBName INSERT INTO @ErrorLog EXEC master..sp_readerrorlog 0, 1, ‘Recovery completed’, @DBName SELECT TOP 1 @DBName AS [DBName] ,[LogDate] ,CASE WHEN…
//
-
Important PS (Power shell) Scripts
$a = get-eventlog -logname system -message *SQL* -newest 1 $a | select-object -property * ————————————————————————————————————- EventID : 7036 MachineName : ABCD Data : {83, 0, 81, 0…} Index : 4281381 Category …
//
-
CPU Components
CPU consists of 6 main components: 1. CU Control unit 2. ALU Arithmetic logic unit 3. Registers 4. Cache 5. Buses 6. Clock All the components work together to allow processing and system control. Control Unit (CU) It fetches, decodes, and executes instructions. It issues control signals that control hardware. It moves data around the…
//