LnT Interview Questions on 10 May 2014

1.       Moving Master database from one drive to another?

To move the master database, follow these steps.

 

1.From the Start menu, point to All Programs, point to Microsoft SQL Server, point to Configuration Tools, and then click SQL Server Configuration Manager.

 

2.In the SQL Server Services node, right-click the instance of SQL Server (for example, SQL Server (MSSQLSERVER)) and choose Properties.

 

3.In the SQL Server (instance_name) Properties dialog box, click the Advanced tab.

 

4. Edit the Startup Parameters values to point to the planned location for the master database data and log files, and click OK. Moving the error log file is optional.

 

The parameter value for the data file must follow the -d parameter and the value for the log file must follow the -l parameter. The following example shows the parameter values for the default location of the master data and log files.

 

Copy

 -dC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\

master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\

LOG\ERRORLOG;-lC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\

DATA\mastlog.ldf

If the planned relocation for the master data and log files is E:\SQLData, the parameter values would be changed as follows:

 

Copy

 -dE:\SQLData\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\LOG\ERRORLOG;-lE:\SQLData\mastlog.ldf

 

 

5.Stop the instance of SQL Server by right-clicking the instance name and choosing Stop.

 

6.Move the master.mdf and mastlog.ldf files to the new location.

 

7.Restart the instance of SQL Server.

 

8.Verify the file change for the master database by running the following query.

 

Copy

 SELECT name, physical_name AS CurrentLocation, state_desc

FROM sys.master_files

WHERE database_id = DB_ID('master');

GO

 

Moving Temp DB

1.Determine the logical file names of the tempdb database and their current location on the disk.

 

Copy

 SELECT name, physical_name AS CurrentLocation

FROM sys.master_files

WHERE database_id = DB_ID(N'tempdb');

GO

2.Change the location of each file by using ALTER DATABASE.

 

Copy

 USE master;

GO

ALTER DATABASE tempdb

MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf');

GO

ALTER DATABASE tempdb

MODIFY FILE (NAME = templog, FILENAME = 'F:\SQLLog\templog.ldf');

GO

3.Stop and restart the instance of SQL Server.

 

4.Verify the file change.

 

Copy

 SELECT name, physical_name AS CurrentLocation, state_desc

FROM sys.master_files

WHERE database_id = DB_ID(N'tempdb');

 

 

 

a.       Check the Path of Master DB through sp_HelpDB Master

b.      Stop the Services

c.       Change the path at Startup parameters

SQL Server Configuration manager > Services > Right click on Service > Properties > Advanced > Add new_path at Startup parameters

d.      Move the files to desired location

e.      Restart the Services

f.        Check the Path of Master DB through sp_HelpDB Master

 

 

2.       How can I move file TempDB files?

 

3.       What is the recovery model of Master?

Default: Simple

We can change to Full/Bulk-Logged

 

4.       Use of TempDB?

 

5.       What is Collation and Use?

Collation is basically the sort order. There are three types of sort orders

(1) Dictionary case sensitive , (2) Dictionary case insensitive, (3) Binary

6.        

7.       2008 Advance Features?

 

8.       What is DMV and uses?

9.     As mentioned earlier, not only can DMVs assist you with performance turning

10.  and monitoring, but they can also provided detailed information when you

11.   need to monitor a SQL Server system. For example, the sys.dm_os_sys_info

12.   Gaining Quick Insight into a SQL Server System 635

13.   Wow! eBook <WoweBook.Com>

14.  view can be used to determine the number of logical CPUs in a system, hyperthread

15.  ratio between the logical and physical processors, and the amount of

16.  physical memory available in the system. Here is the Transact-SQL code that

17.  illustrates this example, including the results.

18.   Select cpu_count,

19.   hyperthread_ratio,

20.   physical_memory_in_bytes

21.   From sys.dm_os_sys_info

22.   /*

23.   Results

24.   cpu_count|hyperthread_ratio |physical_memory_in_bytes

25.   1 | 1 | 072447488

26.  Another useful DMV that is applicable at the database scope level is

27.  sys.dm_tran_locks. It allows a DBA to obtain information on currently active

28.  Lock Manager resources. Locks that have been granted or waiting are displayed.

29.  Here are examples of index-related DMVs that gather information pertaining to

30.   index usage information within a database. The sys.dm_db_index_usage_

31.  stats view is a great DMV to validate which indexes are not being heavily

32.   utilized and which indexes are causing maintenance overhead. The sys.dm_db_

33.  missing_index_details DMV returns information about missing indexes.

34.  Additional parameters identify the database and the table where the missing

35.   index resides.

 

36.   OS Level Configuration?

37.   Backup & Restore?

38.   Day to Day activities?

39.   Fragmentation?

 

Calendar,

Maintenance activities

Online/ Offline Reindex

Monitoring Disk spaces

 

 

 

 

Cases resolving

Log, Drive Space issues,

Critical, High & standard

SQL Server Down