Author: Jugal Shah

  • SQL Server Service Packs and Hotfixes

    What is Service Pack and Hot fixes in SQL Server?

    With respect to SQL Server, if SQL Server has some in house error/bugs. We can’t resolve it. To remove product level error Microsoft has implemented two ways.

    1. Service Packs
    2. Hot fixes

     

    What is the difference between a Service Pack and a Hotfix?

    Service Packs normally have three properties:

    ·    Provide security fixes

    • Correct software errors
    • Enhance performance

    Service Packs don’t add new functionality or change the interface dramatically. Service Packs are bundled into a programmed delivery method, and are cumulative. That means that you can install Service Pack three without applying Service Pack two, or even one. They are for general use — pretty much everyone should install the Service Pack

     A Hotfix is usually a specific security or software flaw that is addressed in code. There may or may not be a packaged delivery method — some Hotfixes just come with instructions of how and where to copy the patch. Hotfixes are normally not for everyone — Microsoft states that you should only apply the patch if you’re having the specific problem it addresses. Even then, some Hotfixes are only available from a Microsoft support representative.

     

    How to check which Service Pack installed?

    Run below SQL Server command

    SELECT SERVERPROPERTY('productversion')
    , SERVERPROPERTY ('productlevel')
    , SERVERPROPERTY ('edition')
    GO

    If you’re using SQL Server version 7, you need a different command:

    SELECT @@VERSION
    GO

     

    The first line of what you will see shows the version number of SQL Server software installed on your server. The last three digits (called the build number) of the version number are used to determine the Service Pack installed on your SQL Server.

     

    How to find which Hotfix applied on SQL Server?

    It’s a bit more difficult to determine the Hotfix number, since each one will be different, and many may be installed. To determine the Hotfixes on your server, download the program called HFNetChk (http://support.microsoft.com/kb/305385) from Microsoft. There’s a commercial version of this tool by the way that will keep your servers up to date, but this is the free one Microsoft provides.

    There are now stored procedures you can use to show you the build number on your server, such as:

    EXEC sp_server_info 
    GO
    and 
    EXEC master..xp_msver
    GO

     

    How to download/get the latest service packs?

    You can download the latest service packs from the below URL

    http://technet.microsoft.com/en-us/sqlserver/bb331754.aspx

     

     

     

  • How do we see the SQL Plan in textual format?

     

    SET SHOWPLAN_TEXT ON

    To quickly analyze a slow-running query, examine the query execution plan to determine what is causing the problem.

    SET SHOWPLAN_TEXT causes SQL Server to return detailed information about how the statements are executed.

    Example

    USE Northwind;
    GO
    SET SHOWPLAN_TEXT ON;
    GO
    SELECT *
    FROM Customers
    WHERE CustomerID = ‘ALFKI’;
    GO
    SET SHOWPLAN_TEXT OFF;
    GO

    Displays how indexes are used:
    –Clustered Index Seek(OBJECT:([Northwind].[dbo].[Customers].[PK_Customers]), SEEK:([Northwind].[dbo].[Customers].[CustomerID]=CONVERT_IMPLICIT(nvarchar(4000),[@1],0)) ORDERED FORWARD)

    Some Important Points :

    1. SET SHOWPLAN_TEXT cannot be specified when using a stored procedure

    2. You need to have the SHOWPLAN permission while running SET SHOWPLAN_TEXT

     

  • How to enable or disable ‘sa’ login?

    /* To disable 'sa' Login */
    ALTER LOGIN [sa] DISABLE
    GO
    /* To Enable 'sa' Login */
    ALTER LOGIN [sa] ENABLE
    GO

  • DBA Roles and Responsibilities

    Click Me to Check How to be DBA?

    DBA Responsibilities

    • Installation, configuration and upgrading of Microsoft SQL Server/MySQL/Oracle server software and related products.
    • Evaluate MSSQL/MySQL/Oracle features and MSSQL/MySQL/Oracle related products.
    • Establish and maintain sound backup and recovery policies and procedures.
    • Take care of the Database design and implementation.
    • Implement and maintain database security (create and maintain users and roles, assign privileges).
    • Database tuning and performance monitoring.
    • Application tuning and performance monitoring.
    • Setup and maintain documentation and standards.
    • Plan growth and changes (capacity planning).
    • Work as part of a team and provide 7×24 supports when required.
    • Do general technical trouble shooting and give consultation to development teams.
    • Interface with MSSQL/MySQL/Oracle for technical support.
    • ITIL Skill set requirement (Problem Management/Incident Management/Chain Management etc)

    Types of DBA

    1. Administrative DBA – Work on maintaining the server and keeping it running. Concerned with backups, security, patches, replication, etc. Things that concern the actual server software.
    2. Development DBA – works on building queries, stored procedures, etc. that meet business needs. This is the equivalent of the programmer. You primarily write T-SQL.
    3. Architect – Design schemas. Build tables, FKs, PKs, etc. Work to build a structure that meets the business needs in general. The design is then used by developers and development DBAs to implement the actual application.
    4. Data Warehouse DBA – Newer role, but responsible for merging data from multiple sources into a data warehouse. May have to design warehouse, but cleans, standardizes, and scrubs data before loading. In SQL Server, this DBA would use DTS heavily.
    5. OLAP DBA – Builds multi-dimensional cubes for decision support or OLAP systems. The primary language in SQL Server is MDX, not SQL here

    Application DBA- Application DBAs straddle the fence between the DBMS and the application software and are responsible for ensuring that the application is fully optimized for the database and vice versa. They usually manage all the application components that interact with the database and carry out activities such as application installation and patching, application upgrades, database cloning, building and running data cleanup routines, data load process management, etc.

    Download SQL Server Interview Question

    AI-era companion reading: The DBA role hasn’t disappeared — it’s evolving into the steward of AI-ready data. If you’re thinking about how these responsibilities stretch into governance and architecture today, see R-I-S-K-S: The 5-Dimension Framework for AI Risk Management and The Agentic Architecture Framework: A TOGAF-Style Method for AI & Data.

  • Datbase Snapshot

    What is database snapshot?
    A database snapshot provides a read-only, static view of a source database as it existed at snapshot creation, minus any uncommitted transactions. Uncommitted transactions are rolled back in a newly created database snapshot because the Database Engine runs recovery after the snapshot has been created. 

     

    Is database snapshot transitionally consistent?
    Yes.
    Each database snapshot is transitionally consistent with the source database at the moment of the snapshot’s creation. When we create a database snapshot, the source database will typically have number open transactions. Before the snapshot becomes available, the open transactions are rolled back to make the database snapshot transitionally consistent. Just like it follows the recovery interval step and it will not affect the source database. 

     

    What are the uses of database snapshot?
    Reporting Purpose
    we can recover damaged database using database snapshot
    also useful if we are planning to do major change in the source database
    Mirroring database is always in recovering mode, to read that database we can use database snapshot.
    Which edition of SQL Server 2005 supports database snapshot? Enterprise Edition 

     

    Is Developer edition of SQL Server 2005 supports database snapshot?
    No. 

     

    What is copy-on-write operation in database snapshot?
    Database snapshots operate at the data-page level. Before a page of the source database is modified for the first time, the original page is copied from the source database to the snapshot. This process is called a copy-on-write operation. The snapshot stores the original page, preserving the data records as they existed when the snapshot was created.

     

    What is Sparse file in Database Snapshot?

    To store the copied original pages, the snapshot uses one or more sparse files. Initially, a sparse file is an essentially empty file that contains no user data and has not yet been allocated disk space for user data. As more and more pages are updated in the source database, the size of the file grows. When a snapshot is taken, the sparse file takes up little disk space. As the database is updated over time, however, a sparse file can grow into a very large file.

     

    What is NTFS File System?

    The NTFS acronym stands for New Technology File System. The name derives from the implementation of very innovative data storage techniques that were refined in NTFS. While none of the techniques are unique to NTFS, it is the first time that so many innovations were released at once on a production file system. The FAT file system had long been criticized for not including some of the more obvious improvements such as journaling, disk quotas, and file compression. However, these improvements made NTFS incompatible with previous versions of Windows, and also with hard disk tools designed for FAT file systems. For example, data recovery tools such as GetDataBack and partitioning tools such as PartitionMagic would run on Windows NT, yet could not function on the newer file system. This led to much frustration with users who had purchased licenses for these products before upgrading to Windows NT.

     

    Explain Sparse file Size in database snapshot

    At the creation time sparse file will take very little space, but as the data changes occurred into the parent database, data page before the changes copied into the sparse file. Thus sparse file grows. Sparse files are the feature of NTFS file system. As the sparse file grows NTFS will allocate the space to sparse file gradually.

     

    Why does the size of a sparse file slightly exceed than the space actually filled by pages in it?

    Sparse files grow in 64-kilobyte (KB) increments; thus, the size of a sparse file on disk is always a multiple of 64 KB. The latest 64-KB increment holds from one to eight 8-KB pages, depending on how many pages have been copied from the source database. This means that, on the average, the size of a sparse file slightly exceeds the space actually filled by pages.

     

    How to create database snapshot?

    1. Based on the current size of the source database, ensure that you have sufficient disk space to hold the database snapshot. The maximum size of a database snapshot is the size of the source database at snapshot creation.
    2. Issue a CREATE DATABASE statement on the files using the AS SNAPSHOT OF clause. Creating a snapshot requires specifying the logical name of every database file of the source database.

     

    Syntax

    CREATE DATABASE AdventureWorks_SS ON

    (

    NAME = AdventureWorks_Data,

    FILENAME = ‘D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.ss’

    )

    AS SNAPSHOT OF AdventureWorks