Clota Blog

Category: DBMS Concepts

  • Deadlock

    Deadlock is a situation where two or more transactions wait for completion of each-other for resources but none of them completes. or Deadlock occur between the transactions when 2 or more translations permanently block each-other in such a ways that each transaction having a lock on the resource where the other tasks are trying to…

  • SQL Server Services

    Below are the SQL Server services which might come after SQL installation based on your selection… •       SQL Server • SQL Server Agent • Other SQL Services • SQL Server Browser • SQL Server Full Text Search • SQL Server Integration Services   • SQL Server Reporting Services  • SQL Server Analysis Services

  • SQL Server Security Check List

     Data is very crucial aspect of any organization or entity. Loosing, stole or destroy no one want and most of time can not effort. Below are the points which we need to consider if we are considering for Database security… 1. Physical Security2. OS Level Security3. SQL Instance Level Security4. Login Security 1. Physical Security …

  • SQL Server Extended Events

    DROP EVENT SESSION (Transact-SQL) Drops an event session. SQL> DROP EVENT SESSION [Collect_Deadlocks_New] ON SERVER View all the event session that exists in SQL Server SQL> select * from sys.server_event_sessions Create a Dead Lock Ex. session in SQL Server 2008 use [master]; CREATE EVENT SESSION [Collect_Deadlocks_New] ON SERVER     ADD EVENT sqlserver.xml_deadlock_report(         ACTION (             package0.collect_system_time,             sqlos.task_time,             sqlserver.client_app_name,             sqlserver.client_hostname,             sqlserver.database_id,             sqlserver.is_system,             sqlserver.username               )                                             )     ADD TARGET package0.asynchronous_file_target(         SET filename           = N’C:\SQL\xE\Collect-Deadlocks_New\Collect-Deadlocks.xel’,             max_file_size      = (512),             max_rollover_files = (3)                                                   ) WITH (      MAX_MEMORY            = 4096 KB,      EVENT_RETENTION_MODE  = ALLOW_SINGLE_EVENT_LOSS,      MAX_DISPATCH_LATENCY  = 20 SECONDS,      MAX_EVENT_SIZE        = 0…

  • Delete vs Truncate

    Its a common interview question which people like to ask to know your understanding on RDMS concepts. I too few times encountered the question in interviews. Pleases don’t ask me interview result. Haha ha… DELETE: 1. DELETE is a DML Command.  2. DELETE statement is executed using a row lock, each row in the table…

  • Pages in Database

    Boot Page Every DB has only one boot page. Boot page stores DB configuration information. It can find in page # 9 of the first DB data file. Bulk Changed Map (BCM) Page BCM page keep track of the extends which has modified by bulked-logged operations. Log backup thread reads this page and get the extends…

  • Wait Types in SQL Server

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

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