• Indexes,  Scripts

    INDEX DEFRAGMENTATION SCRIPT

      INDEX DEFRAGMENTATION SCRIPT for 2008 / 2005 I dedicate this article to my senior DBA “Roshan Joe Joseph” who has helped me to write a script. I have posted the updated INDEX DEFRAGMENTATION script here, which is already published in sqlservercentral (SSC). http://www.sqlservercentral.com/scripts/Automated+index+defragmentation+script/68802/ For VLDB – Please use Ola Hallengren’s script https://ola.hallengren.com/, Which has many parameters. Script changes: ·         I used while loop instead of a cursor. ·         I used to rebuild the index if the page level locking is disabled. Msg 2552, Level 16, State 2, Line 1 The index “index name” (partition 1) on table “table name” cannot be reorganized because page level locking is disabled   Index…

  • Basics for freshers

    SQL server basics for beginners

      In this article, I am going to discuss about the basics of SQL server which is helpful for newbies. SQL–Structured Query Language (pronounced as SEQUEL) is a Database, computer language designed for managing data in relational database management system (RDBMS). Most of the people have to know SQL server query has designed for set based (declarative) . The SQL commands are   DQL (Select) DML (Insert, Update, Delete) DDL (Create, Alter, Drop, Truncate) DCL (Grant, Revoke, Deny) TCL (Commit, Rollback)   Database A Database stores all the data information (like objects) and has the default data file and log file. The data file is called mdf, ndf…. (One-mdf,more than one…

  • Internals

    sql server internal architecture

      Database Physical Structure & Internals: SQL server internals Allocation Map is important for DBAs. It will help to understand the database internals. Database: A Database is a collection of data that is stored (or) organized. We can manage and access data easily. When we create a new database, by default two files will be created physically. We can create more than one log files and data files (NDF). Data file (MDF) Log file (LDF) Data File: All the data will be stored in a data file. Internally the data’s are stored in data pages. A database file has unique FileID and PageID. Log files do not have pages. They have…