Author: Abhishek Yadav
-
Phases of Recovery in Database
At the time of SQL instance reboot or start, database goes in the three phases of recovery… 1. Analysis Phase of recovery 2. Redo Phase of recovery 3. Undo Phase of recovery 1. Analysis Phase of recovery This phase does two things… First, construct a Dirty Page Table (DPT) Second, construct an Active Transaction Table. Dirty Page Table consist…
//
-
Data Page and Transaction Log Page
Hey guys, In this blog I am going to explain you about Data Page and Transaction Log Page. Data page- In SQL Server, the page serves as the basic unit of data storage. A collection of eight physically connected pages is referred to as an extent. Extents aid in effective page management. In all SQL…
//
-
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…
//
-
Contained Database
First introduced in SQL 2012 and its supported now. What it is Contained databases have no dependencies on server-level metadata and settings. User can access a Contained Database without authenticated a login at DB instance level. This helps to isolate the database from database engine and make it possible to easily move the DB from…
//
-
Dive into Buffer pool extension
The Buffer pool extension feature started from SQL server 2014. This is a nice addon to SQL server to extend the memory paging to SSD. This is not an enterprise only feature, this can be enabled on standard edition. The database size has grown huge now the Db’s having size more than 1 TB is…
//
-
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…
//
-
Auditing: Change Data Capture(CDC)
Overview: Change Data Capture, also known as CDC, introduced the first time in SQL Server 2008 version. Change data capture records insert, update, and delete activity that applies to a SQL Server table. if you want to store the audit information about the UPDATE, INSERT, DELETE operations then enable the SQL CDC on that table. Note: …
//
-
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…
//
-
Provisioning a First Azure SQL Database
In this section, we’ll learn: Provisioning Azure SQL database: Provisioning an Azure SQL database refer to creating a new and blank Azure SQL database. We’ll create new SQL database in Azure using the Azure portal: 1. Open a browser and log in to the Azure portal: https://portal.azure.com 2. On the left-hand navigation pane, select Create a resource: 3. On the New page, under Databases, select SQL…
//
-
Moving A Table Data Into New Location Within Database
Problem I often getting low disk space alert from one of my data drive. Adding space on the disk was not possible because of hard drive size limitations and barriers at OS max disk space size. So I started looking what can I do for database end and I found there is only one table…
//