Sql server DBA Interview Questions and Answers Part 2

1) Tell me about your SQL Server DBA Experience

      a) Tell them Different SQL Server Versions you have worked on

      b) What kind of administration of those instances has been done by you

      c) Your role and responsibilities

2) What are the different DCL(Data Control Language) commands in SQL?

   DCL also delas with data only but security of the data.

   It contains commands are GRANT,REVOKE,DENY

3) What is meant by Fill Factor?

       Fill Factor is a setting that is applicable to Indexes in SQL Server.

       The fill factor value determines how much data is written to an index page when it is created / rebuilt

4) What is the default fill factor value ?

     The default fill factor value is 0 (Zero).

5) How to find the default fill factor value in SSMS ?   

       Using sp_configure

6)  How do you troubleshoot errors in a SQL Server Agent Job?

     Inside Sql Server Management Studio,

    Step1) In Object explorer under SQL Server Agent

    Step2) Look for Job Activity Monitor.

    Step3) The job activity monitor displays the current status of all the jobs on the instance.

    Step5) Choose the particular job which failed,

    Step6) Right click and choose view history from the drop down menu.

        The execution history of the job is displayed and you may choose the execution time (if the job failed multiple times during the same day).
        There would information such as the time it took to execute that Job and details about the error occurred.

7)  What is the default Port No on SQL Server ?

      1433

8) How many files can a Database contain in SQL Server ?

      A Database can contain a maximum of 32,767 files.

9) How many types of data files exists in SQL Server ?

     There are Primarily 2 types of data files

      a) Primary data file

      b) Secondary data file(s)

10) How many Primary data file  Secondary data file(s) can exist for a single database?

    a) Can exist only one Primary data file

    b) Can exist Multiple secondary data files

11) Can we create a SQL Login with Blank Password in Sql Server ?

     Yes.You can create a blank password.

12) How many different types of pages exist in sql server ?

         a) Data

         b) Index

         c) Text /Image

         d) GAM (Global Allocation Map)

         e) SGAM (Shared Gloabl Allocation Map)

         f) PFS (Page Free Space)

         g) IAM (Index Allocation Map)

         h) BCM (Bulk change Map)

         e) DCM ( Differential change Map)

13) How to enable or disable all the triggers on a table ?

       Enable Triggers on a table

          Enable Trigger ON ALL TableName

      Disable Triggers on a table

          Disable Trigger ON ALL TableName

14) How to enable or disable all the triggers on a Database?
   
       Enable Triggers on a Database

          Enable Trigger ON ALL Database

      Disable Triggers on a Database

          Disable Trigger ON ALL Database

14) How to enable or disable all the triggers on a Server ?

       Enable Triggers on a Server

          Enable Trigger ON ALL Server

      Disable Triggers on a Server

15) How to get list of all disabled triggers in sql server database ?

    SELECT TBL.name AS TableName,Schema_name(TBL.schema_id) AS Table_SchemaName,TRG.name  AS TriggerName,TRG.parent_class_desc,
       CASE
         WHEN TRG.is_disabled = 0 THEN 'Enable'
         ELSE 'Disable'
       END     AS TRG_Status
       FROM   sys.triggers TRG
       INNER JOIN sys.tables TBL
               ON TBL.OBJECT_ID = TRG.parent_id
              AND trg.is_disabled=1


16) How to Get list of all Enabled Triggers in SQL Server Database ?

     SELECT TBL.name AS TableName,Schema_name(TBL.schema_id) AS Table_SchemaName,TRG.name  AS TriggerName,TRG.parent_class_desc,
       CASE
         WHEN TRG.is_disabled = 0 THEN 'Enable'
         ELSE 'Disable'
       END     AS TRG_Status
       FROM   sys.triggers TRG
       INNER JOIN sys.tables TBL
               ON TBL.OBJECT_ID = TRG.parent_id
              AND trg.is_disabled=0

17) Can We Create Trigger on Temp Tables and System Tables in SQL Server ?

      Sql server does not allow to create triggers on Temp Tables and System Tables that included local and global Temp Tables

        if you try to create trigger,you will get error

           Msg 167, LEVEL 15, State 1, PROCEDURE Trg_InsertSalePerson, Line 2
           Cannot CREATE TRIGGER ON a TEMPORARY object.

18) How to Get SQL Server Agent Jobs List with Schedules in SQL Server ?

        Use MSDB
        go
        Select * from Sysjobs

       Select * from SysSchedules

       Select * from SysJobSchedules

19) SQL services are not starting, where would you look for the cause ?

     C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log\ERRORLOG

      ErrorLog file name check with date modified.it means that recent log.

20) How to resolve Memory issues related to SQL Server Instance ?

     System Admin sends you an email that SQL Server services are taking almost all the memory, what steps would you take to resolve it?

       Goto SSMS -> Goto Object Exproler ->Right click on SQL Server Instance -> Select Properties -> Select Memory Page

        In this you will find Minimum Server memory and Maximum server memory
About Author: author 4 + years of Information Technology experience in understanding and analyzing the software and hardware engineering requirements, onsite and offshore management of product development. Expert work on design and development for windows,web,mobile Expert knowledge of C#,ADO.NET, ASP.Net MVC, ENTITY FRAMEWORK, LINQ,COLLECTIONS, JAVASCRIPT,AngularJs 1.0,Angular2, Jquery,Jquery-Ajax, SQL SERVER, XAML,Windows phone 8.1 Good knowledge of WCF-Restful services,WCF, WEB API-Services ,Web- Services,MIcrosoft-Azure,JUICE UI,HTML,CSS,AJAX Read More...

Join him on Google+ | Facebook | Linkedin

2 comments

  1. Salaam,

    Gasping at your brilliance! Thanks a tonne for sharing all that content. Can’t stop reading. Honestly!

    Have a user who is db_owner of a database but they cannot access it. I thought to drop the user and recreate, however this is not possible because of the error shown in the subject.
    There is a login at the instance level but no user in the database, however 'dbo' has this user as it's login.
    But nice Article Mate! Great Information! Keep up the good work!

    Merci

    ReplyDelete