COGNIZANT SQL DBA INTERVIEW QUESTIONS

CTS SQL DBA Interview Questions

1.     What are the differences between SQL 2005 and 2008?

2.     How to find which files are there in backup, without restoring?

3.     How to find locks?

4.     How to find log file size?

5.     How to check fragmentation?

6.     What are problems that you face in your carrier, Can you explain some situations?

7.     How to connect your sql server machine?

8.     What are the services that your company provides?

9.     What is your data base backup strategy?

10.  Difference between truncate and shrink?

Truncate: It truncates commited trns from logfile and not reduce file size, In the case of Shrink it reduce file size

11.  Is it possible to take backup of TempDB? No

12.  How many Clustered nodes are supported for sqlserver 2008R2, In Enterprise Edition?

Up to 16 nodes — Enter prise edition. (Windows 2008 server), in 2003 server 8 nodes

Up to 2 nodes — Enter prise edition.

WIPRO SQL DBA INTERVIEW QUESTIONS

1. What is the performance counters used to check memory utilization?

2. Which DMV's we can use to check perform counters?

3. Which DMFs can we use to check the complete command running in a session?

4. Which exe file can be used to trace the server actitivites?

5. Statistics - How to update on a particular table index?

6. Memory configuration.

        If there is 16GB RAM, how can we make the server to use min 12 and          max 14.

        Lock pages in memory

7. How to improve the performance of Log Shipping?

8. How to move system dbs?

        * TempDb

        * master

9. Execution plans?

10.Why use DAC? How to connect?

11.Regular issues?

HCL SQL DBA INTERVIEW QUESTIONS

HCL SQL DBA Interview Questions 

1.   Tell me about yourself ?
2.   What are the several recovery models?
3.   What is differennce belween FULL and Simple recovery model ? 
4.   What is backup polices in your envirnment ? 
5.   What is the use of differntial backup ? 
6.     Which pages are available in extents?
7.     What is fill factor?
8.     How to take backup of  DB when the db is in Log shipping by taking that backup without changing LSN?
9.     How to change port number for sql server?
10.  Is it possible to change port for mirroring after configuring mirroring?
11.  What is the major difference between Merge replication and Transactional replication?
12.  How to resolve conflicts in merge replication?
13.  What is quorum in clustering?
14.  What happened when quorum gone?
15.  Is it possible to start service when quorum is gone?
16.  How to take backup of 400GB DB with in less time?
17.  How to find what are the driver’s available in our machine?
18.  What is check point?
19.  Is it posible to raise chaeckpoint our self?
20.  What is the difference between procedure and function?
21.  How to call procedure?
22.  What is the advantage of recompile statement in procedure?
23.  How to know the current connected connections?
24.  How to set maximum connections?
25.  How to start sql service without raising checkpoint?
26.  Have you work on cmd prompts?
27.  What is transaction?
28.  Can you say syntax of transaction?
29.  What is difference between cascade drop of table?
30.  What happened if we issue drop table command?
31.  What is SQL Server Architecture?
32.  Why log shipping is not supported in simple recovery model?
33.  What is the default port for mirroring?
34.  How to change default port for sqlserver?
35.  What is resource governor?
36.  What is heart beating in clustering?
37.  Which cable is used for heart beat, Is it cross cable or plain cable?
38.  What are the different types of indexes?
39.  What are the limits for cluster and non cluster indexes? ANS: 1- 255
40.  Why we have only one cluster index for table?
41.  What is the importance of statistics?
42.  Scenario: My server running fast upto the yester day, today onwords slows, As a DBA what you have to do?
43.  How to find what are the queries that are running in particular SPID?
44.  How to find sql server version?
45.  What is another port number for sqlserver?
46.  I have backup and I want to restore upto particular time only?
47.  How to create user, how to assign read and write permissions only for that user?
48.  What is Super admin, who is having total rights in system level? Ans: SA
49.  What is full form of SA? Ans: System Administrator
50.  What are the different locks?
51.  What are different isolation levels?
52.  What is serelizable?
53.  What is exclusive lock?
54.  I have done some schema change (Add column for table) in merge replication for an article, is it applied for subscriber?
55.  Can you list some system tables?
56. What is difference between SQL 2005 & SQL 2008 Clustering installation?
57. Have you faced any issues in Clustering installation ?
58. What is Islive and looklive ?

Create Partitioned Tables and Indexes

You can create a partitioned table or index in SQL Server 2016 by using SQL Server Management Studio or Transact-SQL. The data in partitioned tables and indexes is horizontally divided into units that can be spread across more than one filegroup in a database.

Partitioning can make large tables and indexes more manageable and scalable.

Creating a partitioned table or index typically happens in four parts:

1. Create a filegroup or filegroups and corresponding files that will hold the partitions specified by the partition scheme.
   
2. Create a partition function that maps the rows of a table or index into partitions based on the values of a specified column.
   
3. Create a partition scheme that maps the partitions of a partitioned table or index to the new filegroups.
   
4. Create or modify a table or index and specify the partition scheme as the storage location

Create or modify a table or index and specify the partition scheme as the storage location.

Ghost_Record_Count values keep increasing in SQL Server 2008 R2 or in SQL Server 2008

Ghost_Record_Count values keep increasing in SQL Server 2008 R2 or in SQL Server 2008 

After you delete some records from a user table in an instance of Microsoft SQL Server 2008 R2 or in an instance of Microsoft SQL Server 2008, the values of the ghost_record_count and version_ghost_record_count columns continuously increase on all the databases of the instance.

Additionally, the allocated space for the deleted records is not released as expected. Therefore, some other issues may occur. For example, a database shrink task may not delete unused space from data tables, or a backup operation may run slowly.

To monitor the values of the ghost_record_count and version_ghost_record_count columns, run the following statement:

SELECT * FROM sys.dm_db_index_physical_stats
(DB_ID(N'<DatabaseName>'), OBJECT_ID(N'<TableName>'), NULL, NULL , 'DETAILED'); 


Solution :

Cumulative update information
SQL Server 2008 Service Pack 3
The fix for this issue was first released in Cumulative Update 4 for SQL Server 2008 Service Pack 3. For more information about this cumulative update package, click the following article number to view the article in the Microsoft Knowledge Base:
2673383 Cumulative update package 4 for SQL Server 2008 Service Pack 3
Note Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2008 fix release. Microsoft recommends that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
2629969 The SQL Server 2008 builds that were released after SQL Server 2008 Service Pack 3 was released
Microsoft SQL Server 2008 hotfixes are created for specific SQL Server service packs. You must apply a SQL Server 2008 Service Pack 3 hotfix to an installation of SQL Server 2008 Service Pack 3. By default, any hotfix that is provided in a SQL Server service pack is included in the next SQL Server service pack.
SQL Server 2008 R2
The fix for this issue was first released in cumulative update package 10. For more information about how to obtain this cumulative update package for SQL Server 2008 R2, click the following article number to view the article in the Microsoft Knowledge Base:
2591746 Cumulative update package 10 for SQL Server 2008 R2 

SQL SERVER BUFFER CACHE HIT RATIO and PAGE LIFE EXPECTANCY

How to find current SQL buffer cache hit ratio and page life expectancy
Buffer cache Hit Ratio:|

 Buffer Cache Hit Ratio shows how SQL Server utilizes buffer 

ItIndicates the percentage of pages found in the buffer cache without having to read from disk. The ratio is the total number of cache hits divided by the total number of cache lookups over the last few thousand page accesses.

After a long period of time, the ratio moves very little. Because reading from the cache is much less expensive than reading from disk, you want this ratio to be high. Generally, you can increase the buffer cache hit ratio by increasing the amount of memory available to SQL Server or by using the buffer pool extension feature.

How to find current SQL buffer cache hit ratio

SELECT (a.cntr_value * 1.0 / b.cntr_value) * 100.0 [BufferCacheHitRatio]

FROM (SELECT *, 1 x FROM sys.dm_os_performance_counters

WHERE counter_name = 'Buffer cache hit ratio')

a

,

(SELECT *, 1 x FROM sys.dm_os_performance_counters

WHERE counter_name = 'Buffer cache hit ratio base')

b

OR

SELECT

      (CAST(SUM(CASE LTRIM(RTRIM(counter_name)) WHEN 'Buffer cache hit ratio' THEN CAST(cntr_value AS INTEGER) ELSE NULL END) AS FLOAT) /

      CAST(SUM(CASE LTRIM(RTRIM(counter_name)) WHEN 'Buffer cache hit ratio base' THEN CAST(cntr_value AS INTEGER) ELSE NULL END) AS FLOAT)) * 100

      AS BufferCacheHitRatio

FROM sys.dm_os_performance_counters

 

Solution : 

Step 1: Open QueryWindow
Step 2: Run Commit (to commit all the data from dirty pages to disk)
Step 3: run DBCC DROPCLEANBUFFERS
To deep digger in to the problem: find the below for further trouble shooting. If the SQL Server Memory looks good
After finding the quarries
Based on Query plan needs to
Create Indexes / rebuilding the indexes
Update Status

 
Page Life expectancy:
Indicates the number of seconds a page will stay in the buffer pool without references.

SELECT object_name, counter_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Buffer Manager%'
AND [counter_name] = 'Page life expectancy'

2

3

4

SELECT object_name, counter_name, cntr_value

FROM sys.dm_os_performance_counters

WHERE [object_name] LIKE '%Buffer Manager%'

AND [counter_name] = 'Page life expectancy'

- See more at: http://www.sqlshack.com/sql-server-memory-performance-metrics-part-4-buffer-cache-hit-ratio-page-life-expectancy/#sthash.LPsKszhE.dpuf

SP_HELP_REVLOGIN SCRIPT for supporting SQL 2005 ,2008

create sp_help_revlogin stored procedure
Transfer logins
----- Begin Script, Create sp_help_revlogin procedure -----


USE master

GO

IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL

    DROP PROCEDURE sp_hexadecimal

GO

CREATE PROCEDURE sp_hexadecimal

   @binvaluevarbinary(256),

          @hexvaluevarchar(256) OUTPUT

AS

DECLARE @charvaluevarchar(256)

DECLARE @iint

DECLARE @length int

DECLARE @hexstringchar(16)

SELECT @charvalue = '0x'

SELECT @i = 1

SELECT @length = DATALENGTH (@binvalue)

SELECT @hexstring = '0123456789ABCDEF'

WHILE (@i <= @length)

BEGIN

    DECLARE @tempintint

    DECLARE @firstintint

    DECLARE @secondintint

    SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))

    SELECT @firstint = FLOOR(@tempint/16)

    SELECT @secondint = @tempint - (@firstint*16)

    SELECT @charvalue = @charvalue +

          SUBSTRING(@hexstring, @firstint+1, 1) +

          SUBSTRING(@hexstring, @secondint+1, 1)

    SELECT @i = @i + 1

END

SELECT @hexvalue = @charvalue

GO

IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL

    DROP PROCEDURE sp_help_revlogin

GO

CREATE PROCEDURE sp_help_revlogin @login_namesysname = NULL AS

DECLARE @name          sysname

DECLARE @xstatusint

DECLARE @binpwd    varbinary (256)

DECLARE @txtpwd    sysname

DECLARE @tmpstr    varchar (256)

DECLARE @SID_varbinaryvarbinary(85)

DECLARE @SID_stringvarchar(256)

IF (@login_name IS NULL)

    DECLARE login_curs CURSOR FOR

            SELECT sid, name, xstatus, password FROM master..sysxlogins

            WHERE srvid IS NULL AND name <> 'sa'

ELSE

    DECLARE login_curs CURSOR FOR

            SELECT sid, name, xstatus, password FROM master..sysxlogins

            WHERE srvid IS NULL AND name = @login_name

OPEN login_curs

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd

IF (@@fetch_status = -1)

BEGIN

    PRINT 'No login(s) found.'

    CLOSE login_curs

      DEALLOCATE login_curs

      RETURN -1

END

SET @tmpstr = '/* sp_help_revloginscript '

PRINT @tmpstr

SET @tmpstr = '** Generated '

      + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'

PRINT @tmpstr

PRINT ''

PRINT 'DECLARE @pwdsysname'

WHILE (@@fetch_status <> -1)

BEGIN

    IF (@@fetch_status <> -2)

    BEGIN

          PRINT ''

          SET @tmpstr = '-- Login: ' + @name

          PRINT @tmpstr

            IF (@xstatus & 4) = 4

          BEGIN -- NT authenticated account/group

                IF (@xstatus & 1) = 1

                BEGIN -- NT login is denied access

                      SET @tmpstr = 'EXEC master..sp_denylogin ''' + @name + ''''

                      PRINT @tmpstr

                  END

                ELSE BEGIN -- NT login has access

                      SET @tmpstr = 'EXEC master..sp_grantlogin ''' + @name + ''''

                      PRINT @tmpstr

                  END

          END

          ELSE BEGIN -- SQL Server authentication

                IF (@binpwd IS NOT NULL)

                BEGIN -- Non-null password

                      EXEC sp_hexadecimal @binpwd, @txtpwd OUT

                      IF (@xstatus & 2048) = 2048

                            SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')'

                      ELSE

                            SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')'

                      PRINT @tmpstr

                            EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

                      SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name

                              + ''', @pwd, @sid = ' + @SID_string + ', @encryptopt= '

                END

                ELSE BEGIN

                        -- Null password

                         EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

                      SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name

                              + ''', NULL, @sid = ' + @SID_string + ', @encryptopt= '

                END

                IF (@xstatus & 2048) = 2048

                      -- login upgraded from 6.5

                      SET @tmpstr = @tmpstr + '''skip_encryption_old'''

                  ELSE

                        SET @tmpstr = @tmpstr + '''skip_encryption'''

                PRINT @tmpstr

            END

    END

    FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd

    END

CLOSE login_curs

DEALLOCATE login_curs

RETURN 0

GO

Q
--revlogin script
USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
  DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
    @binvalue varbinary(256),
    @hexvalue varchar (514) OUTPUT
AS
DECLARE @charvalue varchar (514)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
  DECLARE @tempint int
  DECLARE @firstint int
  DECLARE @secondint int
  SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
  SELECT @firstint = FLOOR(@tempint/16)
  SELECT @secondint = @tempint - (@firstint*16)
  SELECT @charvalue = @charvalue +
    SUBSTRING(@hexstring, @firstint+1, 1) +
    SUBSTRING(@hexstring, @secondint+1, 1)
  SELECT @i = @i + 1
END

SELECT @hexvalue = @charvalue
GO

IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
  DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @type varchar (1)
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @PWD_varbinary  varbinary (256)
DECLARE @PWD_string  varchar (514)
DECLARE @SID_varbinary varbinary (85)
DECLARE @SID_string varchar (514)
DECLARE @tmpstr  varchar (1024)
DECLARE @is_policy_checked varchar (3)
DECLARE @is_expiration_checked varchar (3)

DECLARE @defaultdb sysname

IF (@login_name IS NULL)
  DECLARE login_curs CURSOR FOR

      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
      ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
ELSE
  DECLARE login_curs CURSOR FOR


      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
      ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
OPEN login_curs

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
IF (@@fetch_status = -1)
BEGIN
  PRINT 'No login(s) found.'
  CLOSE login_curs
  DEALLOCATE login_curs
  RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
WHILE (@@fetch_status <> -1)
BEGIN
  IF (@@fetch_status <> -2)
  BEGIN
    PRINT ''
    SET @tmpstr = '-- Login: ' + @name
    PRINT @tmpstr
    IF (@type IN ( 'G', 'U'))
    BEGIN -- NT authenticated account/group

      SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
    END
    ELSE BEGIN -- SQL Server authentication
        -- obtain password and sid
            SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
        EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
        EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

        -- obtain password policy state
        SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
        SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name

            SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'

        IF ( @is_policy_checked IS NOT NULL )
        BEGIN
          SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
        END
        IF ( @is_expiration_checked IS NOT NULL )
        BEGIN
          SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
        END
    END
    IF (@denylogin = 1)
    BEGIN -- login is denied access
      SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
    END
    ELSE IF (@hasaccess = 0)
    BEGIN -- login exists but does not have access
      SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
    END
    IF (@is_disabled = 1)
    BEGIN -- login is disabled
      SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
    END
    PRINT @tmpstr
  END

  FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
   END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO