Interview Questions

What is the default file group name in SQL Server ?
 -- PRIMARY

To which filegroup Transaction log file is associated with ?
-- LDF file is not associated with any of file group

Is it possible to configure DB Mirroring to multiple Mirror Sites ?
-- The Answer is NO since 2008 R2, It is possible from 2012 onwards.

Is it possible to configure Mirroring and Logshipping on the same database ?
-- The Answer is YES

Is it possible to generate backup from the Mirror Server database?
-- The Answer is NO, but it is possible using SQL Server 2012 Always on availability replicas.
What is the code name for SQL Server 2012
Denali for 2012, Katmai for 2008, Kilimanjaro for 2008R2

There are 3 SQL Instances on one physical server. How many SQL Browser services will be installed on that server ?
-- The Answer is ONE, because sql browser is a shared service, Inadditon to SQL browser we have different shared services like Active Directory Helper.

How can you enable Instant File Initialization ?
By adding SQL Service account into perform volume maintenance tasks we can do that.

Is MSDTC is must to install SQL Server 2008 Failover cluster ?
-- The Answer is NO, it gives a warning message if MSDTC is not selected. But if you want to install SSIS,
or distributed transactions, then surely we need to install MSDTC.

What is the maximum limit of non-clustered indexes can be created on a table ?
-- The Answer is 999

How many IP addresses are required to configure 2 node cluster
1. Two public network addresses for node1 and node2
2. Two private network addresses for node1 and node2
3. Windows Cluster IP
4. MSDTC IP
5. Virtual Server IP

Will Attach_Rebuild_Log function builds a new log file incase of abnormal database shutdown?
The Answer is NO,that option ATTACH_REBUILD_LOG function will only works whenever database is cleanly shutdown if Log file is missing.

How differential backup is generated ?
Differential backup is generated based on the changed extents. But to have a differential backup initially we need to generate a full backup.

Backup Compression available in which versions of SQL Server ?
Initially it is available only in 2008 R2 Enterprise Edition, later it was added to SQL Server 2008 R2 Standard Edition.


What is default SQL Server port number ?

The Answer is 1433.







Keep watch this workspace for more question and answers............

Error 5123 SQL Server Database Attach Failed

In SQL Server 2008 R2, the attach database option may failed with the below error in some scenarios

CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file... (Microsoft SQL Server, Error 5123)

This will occur due to login mismatch, ie., the login name which we used to detach is different while attaching the same database.

Please assign full rights on the physical files (mdf, ldf) to and try attach option it will work.

Timestamp in SQL Server

It is a datatype which automatically generates a unique binary numbers. It is a versioning for rows in the table. Whenever any Insert / Update happend on the table this timestamp column increments a number. It doesn't store any date or time value.

A table can have only one timestamp column. The storage size is 8 bytes.

Both TimeStamp and Rowversion datatypes are similar only with name change. The Timestamp datatype is deprecated feature. It may be removed from future versions of SQL Server.

Syntax for creating a table with timestamp datatype

create table test1 (id int, timestamp) -- If you dont specify any column name it will creates a column with name "timestamp"

The timestamp is a autogenerated rowversion which will increments a value whenever new rows are inserted / updated If you import any table which has timestamp column the target table timestamps will be different from source. Because in source the rows may get updated and timestamp has some other value, whereas incase of target server it treats the rows are newly (first time) inserted rows and it creates a series of binary numbers

Server is in script upgrade mode. Only Administrators can connect at this time Error: 18401

I can able to reply to this error because i faced this situation. I installed SQLServer 2008 on one server. After some time I tried to open the management studio from the server and encountered the below error.

In some articles postes that it is by design,
The issue is due to database state and log file location being unavailable during upgrade, once the drives are visible to the server it started up correctly.

All we need to do is just wait few minutes and let the upgrade complete its process. After that it allow us to connect.

What is VBA

Visual basic for Applications is a  Event Driven programming language from Microsoft. In a nutshell it is used to build user defined functions, for automation of applications, by using windows API and through dynamic link libraries (dll). Using Microsoft Excel we can develop macro programming using VBA, VBA coding constructs are similar to Visual Basic.

Find Port Number Using T-SQL

DECLARE @portNumber varchar(20), @key varchar(100)
if charindex('\',@@servername,0) <>0
begin
           set @key = 'SOFTWARE\MICROSOFT\Microsoft SQL Server\' +@@servicename+
                                                                                           '\MSSQLServer\Supersocketnetlib\TCP'
end
else
begin
          set @key = 'SOFTWARE\MICROSOFT\MSSQLServer\MSSQLServer\Supersocketnetlib\TCP'
end

EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key=@key, @value_name='Tcpport', @value=@portNumber OUTPUT

SELECT 'Server Name: '+@@servername + ' Port Number:'+convert(varchar(10),@portNumber)

Import CSV file into SQL Server

Create a table schema and specify the name in Bulk Insert command

BULK
INSERT Table_Name
FROM 'C:\temp\sample_file.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO

Agent xps Disabled

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'AGENT XPs', 1;
GO
RECONFIGURE;
GO

SQL Server Trace Related Information

To find traceid       -- SELECT * FROM :: fn_trace_getinfo(default)
To start a trace      -- sp_trace_setstatus traceid, 1
To stop a trace      -- sp_trace_setstatus traceid, 0
To close and delete a trace   --   sp_trace_setstatus traceid,0
                                                  sp_trace_setstatus traceid, 2

Modifies the current state of the specified trace.

sp_trace_setstatus [ @traceid = ] trace_id , [ @status = ] status
sp_trace_setstatus 2, 2

Status  Description
0 Stops the specified trace.
1 Starts the specified trace.
2 Closes the specified trace and deletes its definition from the server.

Property int Description of the trace:

1= Trace options. For more information, see @options in sp_trace_create (Transact-SQL).
2 = File name
3 = Max size
4 = Stop time
5 = Current trace status. 0 = stopped. 1 = running.

SQL Server Service Accounts

While installation of SQL Server, it will install different services like

1. SQL Server

2. SQL Server Agent

3. Integration Services

4. Analysis Services

5. Browser Service Etc.,

If any service wants to interact with domain resources, network resources like file shares, linked servers, we need use domain account. Many server-to-server activities can be performed only with a domain account.

Below are the steps to create domain account and required privileges required for domain account.

1. Domain account was created by windows administrator

2. Below are rights and privileges required for domain account to access SQL Server.

a. Add domain account to Logon as Service

b. Add domain account to Replace a process-level token

c. Add domain account to Bypass traverse checking

d. Add domain account to Adjust memory quotas for a process

e. Add account to security of MSSQL Install folder and provide full control.

\MSSQL\

f. Create domain account with password never expiry. If not please change password based on the age of the password.

No Special privileges are required for Domain account.

Post Configuration of SQL Server -

1. Disable the xp_cmdshell

2. Hide the Instance from SQL Server Configuration Manager

3. Change the SQL Server Port Number from default to customize

4. Add service account to Lock Pages in Memory

5. Disable the guest account

6. Disable the Built In Administrator (for older versions)

7. Assign Min and Max Memory as per the memory in your machine.

8. Create multiple tempdb files based on the number of processors.

9. Follow the Nomenclature for DBA jobs

10. Add DBA group into Administrator

11. Enable TCP IP / Named Pipes from SQL Server configuration Manager.

12. Allow Remote Connections.

13. Enable DAC

14. Make sure alerting in place etc.,

Script to get space usage information from Data and Log File

Select a.FILEID,

[FILE_SIZE_MB] =
convert(decimal(12,2),round(a.size/128.000,2)),
[SPACE_USED_MB] =
convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)),
[FREE_SPACE_MB] =
convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) ,
NAME = left(a.NAME,15)
from
sysfiles a

Script to get row count for all the tables in all the databases

SET NOCOUNT ON
DECLARE @query VARCHAR(4000)
DECLARE @temp TABLE (DBName VARCHAR(200),TABLEName VARCHAR(300), COUNT INT)
SET @query='SELECT ''?'',sysobjects.Name, sysindexes.Rows
FROM ?..sysobjects INNER JOIN ?..sysindexes ON sysobjects.id = sysindexes.id
WHERE type = ''U'' AND sysindexes.IndId < 2 order by sysobjects.Name'
INSERT @temp
EXEC sp_msforeachdb @query
SELECT * FROM @temp WHERE DBName <> 'tempdb' ORDER BY DBName

The report server cannot create the Report Requests performance counter. The application domain DefaultDomain failed to initialize. Error: Object reference not set to an instance of an object..Service

Reporting Services Upgrade failed (2008 to 2008 R2) due to a dll process was locked.

After performing reporting services upgrade from 2008 to 2008R2 SSRS service was not started
and it is showing the below error while bringing up the service.

The report server cannot create the Report Requests performance counter.
The application domain DefaultDomain failed to initialize. Error: Object reference not set to an instance of an object..Service

Generally this error will occur when reporting services performance dll file was locked by some process while performing the upgrade.

WorkAround
To repair the performance counters

1. Add the below path to PATH environment variable
path=C:\Windows\Microsoft.NET\Framework\v4.0.30319

2. Change the default directory to Reporting Services bin location
CD C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer\bin

Run the following command from the command prompt to uninstall the report server resources:
installutil.exe /u ReportingServicesLibrary.dll

Run the following command from the command prompt to re-install the report server resources:
installutil.exe ReportingServicesLibrary.dll

When SQL Server was Restarted ?????

Generally we use the following commands to find out when SQL Server was restarted

SELECT create_date  FROM sys.databases
WHERE database_id = 2 -- (TempDb)

SELECT start_time  FROM sys.dm_exec_requests
WHERE (session_id = 1)

SELECT login_time FROM sys.sysprocesses
WHERE (spid = 1)

SELECT MIN(login_time)  FROM sys.sysprocesses

From Now Onwards

SELECT sqlserver_start_time  FROM sys.dm_os_sys_info

Error Log Management in SQL Server

There are few system stored procs which helps us to manage the error logs in SQL Server

1. msdb.dbo.sp_cycle_errorlog
2. msdb.dbo.sp_cycle_agent_errorlog
3. xp_readerrorlog

xp_readerrorlog  To view current error log, This SP takes 4 parameters
1. 0 – Current Log file, 1 (Archive#1), 2(Archive#2)
2. 1, 2 (1 Indicates SQL Server, 2 Indicates SQL Agent)
3. Search Text
4. Refine Search with in the search
5. StartDate
6. EndDate
7. Asc | Desc

Examples
xp_readerrorlog 0            -- Reads SQL Server Error Log
xp_readerrorlog -1,2        -- Reads SQL Server Agent Log
xp_readerrorlog 0,1, "Logging SQL Server messages in file"  This command helps to identify the location of error log file.

This above system stored procedure helps us to create a new error log file without restarting the SQL Service & SQL Agent Service

This will creates a new error log file from the point when you execute it. It requires sysadmin role to execute.

Script level upgrade for database 'master' failed because upgrade step 'sqlagent100_msdb_upgrade.sql' encountered error 598, state 1, severity 25. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion

I encountered the below errors in SQL Error Log after completion of service pack / cumulative update on SQL Server 2008. Below are my work around to fix the issue

Error: 3417, Severity: 21, State: 3.

Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.

Cannot create file "\MSSQL10.\MSSQL\Data\temp_MS_AgentSigningCertificate_database.ldf' because it already exists. Change the file path or the file name, and retry the operation.
SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.


Error: 1802, Severity: 16, State: 1.

CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

WorkAround

1. Firstly we need to verify the SQL Server has enough permissions on data and log folders.

2. Need to verify the below registry Key Default Data Path is >> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.\Setup\SQLDataRoot

If this key has invalid path we can update the path and restart the SQL Server will solve the issue, if it has valid path and it still showing the error then

We need to move the temp_MS_AgentSigningCertificate_database.ldf & mdf to some other location and try to bring the service up from the cluster manager. Hope it will resolve the issue.

How to rename a database along with physical & logical files

Select * from SourceDB..sysfiles


ALTER DATABASE SourceDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE SourceDB SET MULTI_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE SourceDB MODIFY Name = TargetDB
ALTER DATABASE TargetDB MODIFY FILE (NAME=N'SourceDB', NEWNAME=N'TargetDB')
ALTER DATABASE TargetDB MODIFY FILE (NAME=N'SourceDB_log', NEWNAME=N'TargetDB_log')


USE [master]
ALTER DATABASE TargetDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
EXEC master.dbo.sp_detach_db @dbname = N'TargetDB'
Rename Physical files as per the ‘TargetDB’


USE [master]
CREATE DATABASE TargetDB ON
( FILENAME = N'F:\DataFiiles\TargetDB_data.mdf'),
( FILENAME = N'G:\LogFiles\TargetDB_log.ldf')
FOR ATTACH


select * from TargetDB..sysfiles