SQL DBA interview questions+ HCL + 2nd round

1.     What is the difference between sys.databases and sysdatabases

2.     How can we know linked servers information?—Ans: select * from sys.servers

3.     How to know all databases info that are in box? Ans: select * from sysdatabases

4.     Are we run query on mirror server?

5.     Are we run query on secondary server of log shipping?

6.     What is default port for sqlserver? 1433(tcp/Ip), UDP:1434

7.     What is dynamic port in sqlserver?

8.     How to set default backup directory path?

9.     How to add full text service after installation?

10.  How to find orphan logins?

11.  What is quorum?

12.  What is the advantage of active & active two node clustering?

13.  What are the reasons when a particular user account is not working?

14.  What are the reasons for connection is slow?

15.  What is the default time for remote connections? 600

16.  What is auditing?

17.  Which server role is assigned for backup? ANS: DB_BACKUPOPERATOR

18.  I have 30GB Ram, How can we configure for production environment? Ans: Depends SLA

19.  What are problems that you faced for log shipping?

20.  What is the distribution database?

21.  What does distribution database contains is it contains data?

22.  What is fill factor?

23.  How to shrink database? What are the steps?

24.  How to move temp data base?

25.  What are suggestible raid levels for mdf and log files?mdf-raid1,  log – raid5

26.  How many temp db’s are recommended as per Microsoft standards?

27.  Have you used profiler?

28.  Is it possible to save profiler data into table? Yes, in text file aslo

29.  What are table partitions?

30.  What is schema binding?

31.  Can I create index on table partitions?

32.  Can I get error while index is rebuilt?

33.  I have 4 databases, in that having 4 users, those 4 users are mapped with some server roles, and those 4 users are there in one database. If I am deleting that major db, is remaining db users are working or not? Yes, they have access with reaming db’s. Those are called orphan logins

34.  How to take resource data base backup?

Backing up the Resource database 
Since the Resource database is not available from the SQL Server tools, we cannot perform a backup similar to how we do it with the other databases. You can backup the database using the following options:

1.    You can use a simple xcopy command to copy from the source location to a destination where you keep your daily database backups. Use the -Y option to suppress the prompt to confirm if you want to overwrite the file. You can create a scheduled task to do this on a daily basis. If you want to keep multiple copies of the database files, you can create an automated script to rename them after the copy process.

xcopy :\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mssqlsystemresource.mdf /Y xcopy :\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mssqlsystemresource.ldf /Y

2.     

3.     

4.    You can use your file-based backup utilities such as NTBackup, IBM Tivoli Storage Manager, Symantec BackupExec, etc.

Restoring the Resource database 
It is important to document the location of your master database as part of your disaster recovery process. In previous versions of SQL Server, all we need to do to restore the server instance is to worry about the masterdatabase.

After a SQL Server 2005 instance has been rebuilt a restore of the master database will be done, the Resourcedatabase files should go along with it should a WITH MOVE option be required. This means that if the old location of themaster database will be different from the one after the restore, the Resource database files should already be there prior to restoring the master database. This is very critical if a hardware failure occurred and you need to move the system databases on a different drive during the server instance rebuild.

To restore the Resource database, just copy the database files to the location of the master database files. If you have an older version of the Resource database, it is important to re-apply any subsequent updates. This is why the recommended approach is to simply do a daily backup of these files.