Explore topic-wise InterviewSolutions in .

The ERROR comes when you are trying to restore the DB which already exists. Use WITH REPLACE OPTION to restore the DB with a DIFFERENT NAME

The error comes when you are trying to restore the DB which already exists. Use WITH REPLACE option to restore the DB with a different name

Well there are many ways to do this.

1. find the SPID which you want to analyze. An spid is assigned as soon as a client connection is established with the SQL server. To find the spid you can run any of the FOLLOWING command:
   1. SP_WHO2 ‘ACTIVE’ — This will give you only active spids.
   2. SELECT * FROM sys.dm_exec_requests
2. Get the spid from above TWO QUERIES and use any of the following query to get what is happening behind that spid.
   1. dbcc inputbuffer()
   2. sql2005 and sql2008 – SELECT * FROM sys.dm_exec_sql_text()
   3. sql2005 and sql2008 – SELECT * FROM fn_get_sql()

Well there are many ways to do this.

SECONDARY SERVER. This question is basically ASKED to find out WHETHER you have a hands on work on log SHIPPING or not.

Secondary server. This question is basically asked to find out whether you have a hands on work on log shipping or not.

You need to ADD only FAILOVER PARTNER information in your FRONT end code. “Data Source=ServerA;Failover Partner=ServerB;Initial Catalog=AdventureWorks;Integrated Security=True;”.

You need to add only FAILOVER PARTNER information in your front end code. “Data Source=ServerA;Failover Partner=ServerB;Initial Catalog=AdventureWorks;Integrated Security=True;”.

ANSWER :BACKUP LOG TestDB WITH TRUNCATE_ONLY is GONE. SQL SERVER doesn’t allow you to TRUNCATE the log now otherwise whole purpose of a DB is DEFEATED.

understand that whole table WOULD be locked for the processing thenn this is better to use TABLOCK hint and get complete table BLOCKED. This is a nice way to avoid the wastage of sql server DB engine processing for lock escalation. Somewhere you MAY ALSO need to use TABLOCKX when you WANT an exclusive lock on the table in the query.

understand that whole table would be locked for the processing thenn this is better to use TABLOCK hint and get complete table blocked. This is a nice way to avoid the wastage of sql server DB engine processing for lock escalation. Somewhere you may also need to use TABLOCKX when you want an exclusive lock on the table in the query.

Lock: DB engine LOCKS the rows/page/table to access the data which is worked upon according to the QUERY.

Block: When one process BLOCKS the resources of another process then blocking happens. Blocking can be identified by USING

SELECT * FROM sys.dm_exec_requests where blocked <> 0
SELECT * FROM master..sysprocesses where blocked <> 0

Deadlock: When SOMETHING happens as follows: Error 1205 is reported by SQL Server for deadlock.

Lock: DB engine locks the rows/page/table to access the data which is worked upon according to the query.

Block: When one process blocks the resources of another process then blocking happens. Blocking can be identified by using

SELECT * FROM sys.dm_exec_requests where blocked <> 0
SELECT * FROM master..sysprocesses where blocked <> 0

Deadlock: When something happens as follows: Error 1205 is reported by SQL Server for deadlock.