Fun Zone

Don’t think about Work always… Let’s have some fun…

Interview Question and Answer

Please Note that the material is collection only from different resources of Web. I am just gathering them all to make a complete note.

Please send me the Question and Answer related to SQL Interview

that I can put it in web.

das.joydeep2@gmail.com

[ Question -  1 Date: 26-April-2012 ]

How can you move the master database?


Answer:

The catch to moving the Master database is that you must also move the Resource database.  Microsoft states that the Resource database must reside in the same location as the Master database. 

To find the physical location of database file use the SQL statement:

SELECT physical_name

FROM sys.master_files

WHERE database_id = DB_ID(N'master');

First we will focus on moving the Master database, so we need to tell SQL where we plan to move the master database files.  SQL stores the location of the Master database in the registry, but it’s best to use SQL Server Configuration Manager to make the change.  Open Configuration Manager and select the “SQL Server Services” node on the left.  Next we want to right click and select properties on the SQL Server Service for the instance we are changing.  In the properties dialog box we want to select the Advanced tab and take a look at the Startup Parameters option.

By default there are 3 startup parameters. “-d” specifies the location of the Master database data file.  “-l” specifies the location of the Master database log file.  “-e” specifies the location of the SQL Server error log file.  Here is what it looks like on my laptop for the default instance.

-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

Go ahead and change the location of the Master database data and log file locations and click OK.  You can also change the error log location at the same time if you need to.

We need to shut down SQL.  Once SQL is stopped, copy the physical MDF and LDF to the new file system location.  Remember when I said my resource database was in a local drive on my cluster nodes, even though the Microsoft article said it HAD to be in the same location as Master?  If that is the case for you, then you can leave it alone and just go ahead and restart SQL Server.  However, if your Resource database was indeed in the same place as Master then read on to see how to get that moved.

When we start SQL back up we now have to start it in recovery mode to change the location of the Resource database.  We can do that by running a command prompt as an Administrator and running the following command.  Type this command exactly as you see here.

NET START MSSQLSERVER /f /T3608

Next we need to tell SQL where we plan to move the Resource database files.  Make sure to change the “FILENAME” path but leave the ”NAME” the same as what you see below.

USE master;

GO

ALTER DATABASE mssqlsystemresource

MODIFY FILE (NAME = data, FILENAME = ‘Y:\MSSQL10.MSSQLSERVER\MSSQL\DATA\mssqlsystemresource.mdf’);

GO

ALTER DATABASE mssqlsystemresource

MODIFY FILE (NAME = log, FILENAME = ‘Z:\MSSQL10.MSSQLSERVER\MSSQL\DATA\mssqlsystemresource.ldf’);

GO

Don’t shut down SQL yet.  Go ahead and copy the physical MDF and LDF to the new file system location, and then run the following command.  The system Resource database must be put back into a read only mode.

ALTER DATABASE mssqlsystemresource SET READ_ONLY;

GO

Now we can shut down and restart SQL server.  Once SQL is back up you might want to run the first query again just to make sure everything went as planned and the location has been updated properly.  Also, don’t forget to clean up after yourself and delete the old database files.

[ Question -  2 Date: 26-April-2012 ]

What is blocking in SQL Server?

Answer: 


Blocking in SQL Server is a scenario where one connection to SQL Server locks one or more records, and a second connection to SQL Server requires a conflicting lock type on the record or records locked by the first connection. This causes the second connection to wait until the first connection releases its locks. By default, a connection will wait an unlimited amount of time for the blocking lock to go away.

[ Question -  3 Date: 30-April-2012 ]

What is RDBMS?

[ Question -  4 Date: 30-April-2012 ]

What are the Properties of the Relational Tables?

[ Question -  5 Date: 30-April-2012 ]

What is Normalization?

[ Question -  6 Date: 30-April-2012 ]

What is De-normalization?

[ Question -  7 Date: 30-April-2012 ]

How is ACID property related to Database?

[ Question -  8 Date: 30-April-2012 ]

What are the Different Normalization Forms?

[ Question -  9 Date: 03-March-2012 ]

What is a Stored Procedure?

[ Question -  10 Date: 03-March-2012 ]

What is a Trigger?

[ Question -  11 Date: 03-March-2012 ]

What are the Different Types of Triggers?

[ Question -  12 Date: 03-March-2012 ]

What is a View?

[ Question -  13 Date: 03-March-2012 ]

What is an Index?

[ Question -  14 Date: 03-March-2012 ]

What is a Linked Server?

[ Question -  15 Date: 03-March-2012 ]

What is a Cursor?

[ Question -  16 Date: 03-March-2012 ]

What is Collation?

[ Question -  17 Date: 08-March-2012 ]

What is the Difference between a Function and a Stored Procedure?

[ Question -  18 Date: 08-March-2012 ]

What is subquery?  Explain the Properties of a Subquery?

[ Question -  19 Date: 08-March-2012 ]

What are Different Types of Join?

[ Question -  20 Date: 08-March-2012 ]

What are Primary Keys and Foreign Keys?

[ Question -  21 Date: 08-March-2012 ]

What is User-defined Functions? What are the types of User-defined Functions that can be created?