Clota Blog

Category: SQL Server

  • SQL Agent Jobs running multiple times out of schedule

         This was interesting issue which I faced in one of my production environment. Customer complained their all SSIS jobs are running multiple times at a schedule. But in job history, it was showing all well. We found, all the scheduled Backup maintenance jobs also triggering two times at a schedule. We tried below…

  • SQL Server VSS Writer Service

     Microsoft built the capability into windows allowing files to be backed up to disk while other programs are using those files.  The VSS Servicer interacts with the Windows Volume Shadow Copy service (VSS), allowing files that are in use, such as SQL Server data files, to be copied (or backed up) without stopping all other…

  • SQL Server Architecture Diagram

    Hey guys, In this blog I am going to explain you about SQL Server Architecture Diagram  In almost every SQL DBA interview, this is the question which thrown as a hard ball from interviewer. Below is high level SQL Server Architecture Diagram which is hard to explain in telephonic interview as well as face to…

  • 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…

  • 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…

  • 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…