1> what is the difference btw instance and server. Anybody clarify me frnds

Ans- Simply..Server is physically visible instance is logically

2>Abbrevations used in collation : SQL_Latin1_General_CP1_CI_AS

1. CP1 : code page 1252
2. CI: Case Insensitive
3. As: Accent Sensitive

3>How can you check the backup jobsm are running properly or not?

Ans:- SELECT sdb.Name AS DatabaseName,
COALESCE(CONVERT(VARCHAR(12), MAX(bus.backup_finish_date), 101),'-') AS LastBackUpTime
FROM sys.sysdatabases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name
GROUP BY sdb.Name

......it will display when last backup happened

4>in production sqlserver,disk space is full where the .mdf files located.
and ldf files located in other disk...
so please advise me how to reduce unused space in database inorder to solve critical disk issue

Ans:- add another .ndf file on the disk where space available and move the tables which are having maximum transaction on them and which are into .mdf file...... Also, you can use the DBCC SHRINFILE command but it will not much helpful while making space on the drive. Other option is to archive/purge old data and execute DBCC SHRINKFILE command then you can get some space on the drive where .MDF located.

5>This statement will let you know how much free space available in database in percentage

SELECT (AU.unallocated + AU.unused) * 100 / (AU.reserved + AU.unallocated) AS free_space_pct

FROM (SELECT AU.reserved,AU.reserved - AU.used AS unused,

CASE WHEN SF.db_size >= AU.reserved THEN (SF.db_size - AU.reserved) ELSE 0 END AS unallocated

FROM (SELECT SUM(CAST(CASE WHEN SF.status & 64 = 0 THEN SF.size ELSE 0 END AS DECIMAL(38, 2))) AS db_size

FROM dbo.sysfiles AS SF) AS SF


(SELECT SUM(CAST(A.total_pages AS DECIMAL(38, 2))) AS reserved,

SUM(CAST(A.used_pages AS DECIMAL(38, 2))) AS used

FROM sys.partitions AS P


sys.allocation_units AS A

on A.container_id = P.partition_id) AS AU) AS AU;

6>I have the every Sunday 6pm full backup
 every 6 hours differential backup
 every 1 hour tlog backup.
one user requested that Wednesday I need full backup. how can you give?


In This case give latest Full backup , latest Dif backup and subsequent Trns backups to your user


2) perform copy-only backup on your database and give to your user because
copy only backup doesn't disturb your backup strategy

But Don't perform Full backup unnecessary

7>I do have secondary 500 dbs in logshipping in which all the db's are in restoring mode and belongs to different instances. How do I check which db belongs to particular server. I.e., how to check primary db in LS from secondary which are in restoring mode.


use msdb
select * from dbo.log_shipping_secondary

8> ///////// Script to get to know most accessed tables:///////////////////////////

t.name AS 'Table', SUM(i.user_seeks + i.user_scans + i.user_lookups) AS 'Total accesses',
SUM(i.user_seeks) AS 'Seeks', SUM(i.user_scans) AS 'Scans', SUM(i.user_lookups) AS 'Lookups'
sys.dm_db_index_usage_stats i RIGHT OUTER JOIN sys.tables t ON (t.object_id = i.object_id)
GROUP BY i.object_id, t.name
ORDER BY [Total accesses] DESC;

9> can anybody say simple two or 3 diffs  btw the migration and upgradation?

Ans-Migration: Moving DB's from one server to another server

 upgradton means changing version lower to higher right. Upgradation: upgrade the sql server 2000 to 2005 or 2005 to 2008/2008R2/2012 etc

In place: Up-gradation can be done on same machine with same Hardware ; Side by side: done with Fresh/latest hardware system

10>can we view the error log for any specific instance? how??

Ans-Run->cmd->eventvwr is also to displays the errors

11>pls give me proper answer friends
i have 10am full backup.
i hve 2pm diff backup(every 4 hours diff backup)
i have 6pm diff backups
i have 10 pm diff backup.
i have 3 pm tlog backup(every 1 hour tlog generates)
i have 4 pm tog backups..............
so i got i got request that 1 recorded at exactly 3.30 pm. how can u insert that deleted record.

Ans- restore 10 am full backup. Restore 2 pm differential copy. While restoring make sure no recovery options is selected. At this stage run the 3 pm tranaction log and let the db recover. Hope you get your record back assuming it was deleted @ 3 30. Also it would be interesting to know whether you have tried validating your dr strategy because if this is your real life backup strategy then you must revisit the strategy. Nevertheless it appears to be an interview question?

12> Give users permission to access table



USE AdventureWorks;





13>tell me smthing about Lazy writer and Dirty Read.

Ans-The read is officially “dirty” when it reads data that is uncommitted. The Problem occurs in the case when Locking is not available on either the object or the rows being updated and several users are accessing the data concurrently.

Dirty Read occurs when 1 transaction is updating some rows and no commit has been issued and at the same time another transaction (transaction 2) queries the same rows.

 Lazy writer continue to look the free page in buffer memory and make available for Sql operation..

 lazy writer sleeps for a specific interval of time and when it wake up,it examines the size of free buffer list

14>what is the difference btw instance and server. anybody clarify me frnds

Ans:- SERVER - is an infrastructure on which SQL instances runs.
INSTANCES - You can have multiple SQL instances on a single server... and each instance can have multiple databases...

when you are adding another server into the existing server, then that added server is called instances of that server, maximum we can have 50 instances in one server.

50 Instance for Enterprise edition 16 Instance for Standard edition

I think server and instance might be two different words, but meaning is the same..
Usually windows server gives hosting to SQL servers which may be default instance or named instance.
So here hosting server(windows ) namely called as Server.
Host taking servers(SQL SERVER) namely called as Instances…

15> How to change isolation level?

Ans- Controls the locking and row versioning behavior of Transact-SQL statements issued by a connection to SQL Server.

[ ; ]


The following example sets the TRANSACTION ISOLATION LEVEL for the session. For each Transact-SQL statement that follows, SQL Server holds all of the shared locks until the end of the transaction.

USE AdventureWorks2012;
    FROM HumanResources.EmployeePayHistory;
    FROM HumanResources.Department;


16>How to find session-Id of each individual users?

Ans-Select hostname, nt_username From sys.sysprocesses


exec  Sp_who2

17> How much time does a SQL Native backup take for the 15 GB Database?


if you are taking backup to local server it takes 25 to 30 Min.

that is based on ur environment configuration settings , in my environment it will take less than 10 min

It depends  one of the major factor affecting the speed of the backup i would say is Disk throughput. If you are having faster disk the backup will complete faster.

18>can anybody tell me exact purose of cursor in ms sql


Main purpose of cursor is to access 1 by 1 row from a table in sequence.. without cursor can't do this task in sql server

 if you want to perform a set of actions on a group of records , you have to use the cursor.

19>What is patching SQL Server?

Ans-patching means updating version

Updating to new version like ex: SP2 to SP3

20> how to Monitoring free space of database. ?

Ans:- exec  sp_spaceused

21> what is the difference between user and login?

Ans-The terms SQL user and SQL login are often mixed up, although they are quite different

A SQL Server login is a security entity defined on a SQL Server instance. You can use it to connect to a SQL Server instance, but if it’s not mapped to a database user, you will not be able to connect to a specific database. One login can be mapped to a different user in each database

A SQL Server user is a security entity defined to access databases. Permissions on databases and database objects (tables, views, etc.) are granted or denied to the database user. A user must be mapped to a SQL login, certificate or key

22> SQL Server 2008 High CPU usage


 [Average CPU used] = total_worker_time / qs.execution_count,
 [Total CPU used] = total_worker_time,
 [Execution count] = qs.execution_count,
 [Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2, 
         (CASE WHEN qs.statement_end_offset = -1 
            THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 
          ELSE qs.statement_end_offset END - 
,[Parent Query] = qt.text
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Average CPU used] DESC;


23>What is row version in tempdb?

Sol:- The RowVersion (or Timestamp) data type automatically increments whenever a change happen on a table row . These VALUES are always automatic and cannot be added manually. This data type is equivelant to the binary(8) data type which can be added manually to a table.

24>Hi friends, Today i got an issue with production server .. reg: blocking, spid -2 is blocking some spid 152, this spid 152 is blocking some spid 162, like wise chain of spid are blocked, and application was slow ,,,can any please tell me . what is the SPID -2 ?

Sol-First run this: 
Select req_transactionUOW  from master..syslockinfo where req_spid = -2
This will return a 32 digit UOW number like ‘FE4A57F2-28C5-44F9-8416-B08760DFE7E9’. Use this UOW to kill the main blocker.
KILL ‘FE4A57F2-28C5-44F9-8416-B08760DFE7E9’
Run sp_who2 again and you will probably see that the SPID has disappeared. Explanation:
This negative SPID is known as a distributed transaction SPID or an orphaned distributed transaction SPID. What has happened is that when a transaction involves data that resides on more than one server, such as when a database record is replicated out to two or more servers, MSDTC needs to become involved. SQL Server handles this transparently for you.
However, occasionally, all does not go as well as it should. A server drops off the network or there’s a power outage at the distributor server. Something messy those computers aren’t very good at dealing with. MSDTC usually handles these scenarios very well, ensuring that the rules involving the database ACID properties are adhered to, so that everything stays in sync as it should, and everyone’s happy about the data in their tables. However, when MSDTC can’t recover from one of these scenarios, the SPID which is handling the distributed transaction on one (or more) servers can’t do any more work. The result is an orphaned SPID.
In order to mark this as an orphaned, distributed transaction SPID, SQL Server changes the SPID from a positive number to -2. The only problem is the SPID may still be holding on to resources (usually table, page or row locks), and blocking other SPIDs which want to access that database object. Because the KILL command can’t handle SPIDs with a value of less than 1, you can’t use it to kill a negative SPID. Hence the need to look up the UOW (Unit of Work) ID of the offending SPID before the process can be terminated.
I sincerely hope that this has been of some value to you and it’s actually a good and simple way to resolve negative SPID errors. This is a good article for everyone who has faced this issue in the past and a good learning experience for people who may face this issue in future.


25> hi friends how can we know when the statistics are updated

Sol- Version SQL 2005, 2008:

SELECT OBJECT_NAME(A.object_id) AS Object_Name, A.name AS index_name, STATS_DATE(A.OBJECT_ID, index_id) AS StatsUpdated ,
DATEDIFF(d,STATS_DATE(A.OBJECT_ID, index_id),getdate()) DaysOld
FROM sys.indexes A
INNER JOIN sys.tables B ON A.object_id = B.object_id


select t.name,
max( STATS_DATE(t.object_id,stats_id)) AS statistics_update_date
from sys.tables t left join sys.stats s on t.object_id = s.object_id
join ( select OBJECT_ID , sum(rows) rows from sys.partitions group by object_id having SUM(rows) >0 ) pa
on t.object_id = pa.object_id
group by t.name
order by 2

26> How to find who has modified the table structure in database in Sql server..

Sol- Below query will be very useful to trace the list of users who had performed the DDL operations by object level.

SELECT TE.name ,
v.subclass_name ,
DB_NAME(t.DatabaseId) AS DBName ,
t.NTUserName ,
t.HostName ,
t.LoginName ,
t.StartTime ,
t.ObjectName ,
CASE t.ObjectType
WHEN 8259 THEN 'Check Constraint'
WHEN 8260 THEN 'Default (constraint or standalone)'
WHEN 8262 THEN 'Foreign-key Constraint'
WHEN 8272 THEN 'Stored Procedure'
WHEN 8274 THEN 'Rule'
WHEN 8275 THEN 'System Table'
WHEN 8276 THEN 'Trigger on Server'
WHEN 8277 THEN '(User-defined) Table'
WHEN 8278 THEN 'View'
WHEN 8280 THEN 'Extended Stored Procedure'
WHEN 16724 THEN 'CLR Trigger'
WHEN 16964 THEN 'Database'
WHEN 16975 THEN 'Object'
WHEN 17222 THEN 'FullText Catalog'
WHEN 17232 THEN 'CLR Stored Procedure'
WHEN 17235 THEN 'Schema'
WHEN 17475 THEN 'Credential'
WHEN 17491 THEN 'DDL Event'
WHEN 17741 THEN 'Management Event'
WHEN 17747 THEN 'Security Event'
WHEN 17749 THEN 'User Event'
WHEN 17985 THEN 'CLR Aggregate Function'
WHEN 17993 THEN 'Inline Table-valued SQL Function'
WHEN 18000 THEN 'Partition Function'
WHEN 18002 THEN 'Replication Filter Procedure'
WHEN 18004 THEN 'Table-valued SQL Function'
WHEN 18259 THEN 'Server Role'
WHEN 18263 THEN 'Microsoft Windows Group'
WHEN 19265 THEN 'Asymmetric Key'
WHEN 19277 THEN 'Master Key'
WHEN 19280 THEN 'Primary Key'
WHEN 19283 THEN 'ObfusKey'
WHEN 19521 THEN 'Asymmetric Key Login'
WHEN 19523 THEN 'Certificate Login'
WHEN 19538 THEN 'Role'
WHEN 19539 THEN 'SQL Login'
WHEN 19543 THEN 'Windows Login'
WHEN 20034 THEN 'Remote Service Binding'
WHEN 20036 THEN 'Event Notification on Database'
WHEN 20037 THEN 'Event Notification'
WHEN 20038 THEN 'Scalar SQL Function'
WHEN 20047 THEN 'Event Notification on Object'
WHEN 20051 THEN 'Synonym'
WHEN 20549 THEN 'End Point'
WHEN 20801 THEN 'Adhoc Queries which may be cached'
WHEN 20816 THEN 'Prepared Queries which may be cached'
WHEN 20819 THEN 'Service Broker Service Queue'
WHEN 20821 THEN 'Unique Constraint'
WHEN 21057 THEN 'Application Role'
WHEN 21059 THEN 'Certificate'
WHEN 21075 THEN 'Server'
WHEN 21076 THEN 'Transact-SQL Trigger'
WHEN 21313 THEN 'Assembly'
WHEN 21318 THEN 'CLR Scalar Function'
WHEN 21321 THEN 'Inline scalar SQL Function'
WHEN 21328 THEN 'Partition Scheme'
WHEN 21333 THEN 'User'
WHEN 21571 THEN 'Service Broker Service Contract'
WHEN 21572 THEN 'Trigger on Database'
WHEN 21574 THEN 'CLR Table-valued Function'
WHEN 21577
THEN 'Internal Table (For example, XML Node Table, Queue Table.)'
WHEN 21581 THEN 'Service Broker Message Type'
WHEN 21586 THEN 'Service Broker Route'
WHEN 21587 THEN 'Statistics'
WHEN 21825 THEN 'User'
WHEN 21827 THEN 'User'
WHEN 21831 THEN 'User'
WHEN 21843 THEN 'User'
WHEN 21847 THEN 'User'
WHEN 22099 THEN 'Service Broker Service'
WHEN 22601 THEN 'Index'
WHEN 22604 THEN 'Certificate Login'
WHEN 22611 THEN 'XMLSchema'
WHEN 22868 THEN 'Type'
ELSE 'Hmmm???'
END AS ObjectType

FROM [fn_trace_gettable](CONVERT(VARCHAR(150), ( SELECT TOP 1
WHERE [property] = 2
JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
JOIN sys.trace_subclass_values v ON v.trace_event_id =
AND v.subclass_value =
WHERE TE.name IN ( 'Object:Created', 'Object:Deleted', 'Object:Altered' )
-- filter statistics created by SQL server
AND t.ObjectType NOT IN ( 21587 )
-- filter tempdb objects
AND DatabaseID <> 2
-- get only events in the past 24 hours
AND StartTime > DATEADD(HH, -24, GETDATE())
ORDER BY t.StartTime DESC ;

27>how to apply service packs in cluster active active & active passive nodes in 2005 or 2008 r2

Sol:- 1. Failover all the resources to one node
2. Apply patch on passive node first
3.Fail back the instance on that node.. Instance will go in upgrade state.
4.Again patch the second node after making it passive

28>how to take log backup for a database which is involved in Always on Availability group ?

Sol-run DBCC LOG INFO, do you see value of "2" in the status column. this means that portion of log is active.Do you see it the last or at the beginning?? 
Status of 0 means that portion of log is not beginning used and so, any log part you see with status '0' is shrinkable.
However,The way shrink works is it only removes the log from the end. So, If there is '2' in the end of the log files, you shrink will not work .

In any high availability features or always
Taking log backup ll break chain instead of that u can copy_only backup

29>Hiii Experts....just now i configured Log-Shipping in sql server 2008R2 ..... It was successful... but data is not replicating into secondary server... everything should be done clearly...all 3 jobs scheduled for 5min .... Pls let me know....what can i do....

Sol:- Check 3 Points:- 
(1) SQL Server Agent service of primary server of primary database have read/write permission in shared folder or local NTFS folder
(2) SQL Server Agent service of secondary server of secondary database have read only permission in shared folder or local NTFS folder
(3) Check copy and restore job in secondary database is successful or not

30>My DB server goes down... As a DBA wht r my first steps to b taken to identify why the server is down??

Sol:-  First clear one thing DB is down or Server is down. 
If sever is down then inform windows team &
If db is down then check first 
(1) event viewer
(2) error log
(3) status of sql & agent services
(4) check the physical files of db (mdf/ndf/ldf)


31> what are latches

Sol:-Larches are lightweight synchronization primitives that are used by the SQL server engine to guarantee consistency of in-memory structures.

Latches properties:-

32>hi guys... my databse in suspect mode how to get online.

Sol:- When the database is in SUSPECT mode, you can change the database status to the EMERGENCY mode. This could permit the system administrator read-only access to the database. Only members of the sysadmin fixed server role can set a database to the EMERGENCY state.

You can run the following SQL query to get the database to the EMERGENCY mode.


After that, you set the database to the single-user mode. Single user mode allows you to recover the damaged database.


Then you can run DBCC CheckDB command. This command checks the allocation, structural, logical integrity and errors of all the objects in the database. When you specify “REPAIR_ALLOW_DATA_LOSS” as an argument of the DBCC CheckDB command, the procedure will check and repair the reported errors. But these repairs can cause some data to be lost.


If the above script runs without any problems, you can bring your database back to the multi user mode by running the following SQL command:


33>can we shrink ldf files when log shipping enabled

Sol:- when logshipping is enabled..better dont shrink ldf files. i think..performance may be slow or effect on logfile and indexes.

Disconnect log shipping 1) shrink database then enable log shipping

34>hi guys,, in a table one record is deleted. How can identify the record deleted.

Sol:-  we can get the information in hex format using the below query:

1) Query:
[Current LSN],
[Transaction ID],
[Description], SPID,[Begin Time], [Transaction SID],
name 'LoginName'
FROM fn_dblog (NULL, NULL),
(select sid,name from sys.syslogins) sl
where [Transaction Name] LIKE '%delete%' and [Transaction SID] = sl.sid

2) http://www.nareshvadrevu.in/.../sql-server-dba-how-to...

3) Change Data Capture.
4) Better to create a trigger when you create a table.

But as per my knowledge, we cant view the deleted record.

35>how many ip's are in active/active clustering and in active/passive clustering

Sol:-  For Active- Passive
2 public IP for each node
2 private IP for heartbeat purpose
1 ip for cluster
1 virtual ip for sql server instance
1 ip for msdtc
1 for quorum

For Active-Active
1 more ip for another sql instance

///// For Multi Instance SQL Cluster (active/active )

1 IP for Windows Cluster

2 IP for SQL Server Virtual Network Name (SQL instance on each node)

1 IP for MSDTC (this is optional however required if you use distributed transaction like linked server)

2 IP for public network (1 per node)

2 IP for heartbeat network (1 per node)

for Single Instance single cluster ( active/passive)

in above instead of two you will only need one IP address for SQL Server virtual network name



Sol:- actually it came for security purpose all system tables\catalogs are physically stored in resource database and service pack information also will stored in this database only

37>can i upgrade sql server 2000 to 2012 directly?

Sol:- no you cant upgrade 2000 to 2012.You need to upgrade step by step if your sql serevr 2000 with sp4 then you can upgrade direct to sql server 2008r2 and after that you can upgrade 2008 to 2012 directly 2008 R2 should be SP1 or later.or if your sql server 2000 not with sp4 than add one more step upgrade first 2005 sp4 and than directly upgrade 2012 no need 2008.

SQL Server 2012 supports upgrade from only the following versions: SQL 2005 SP4 or SQL 2008 SP2 or SQL 2008 R2 SP1.we cannot use the SQL 2012 Upgrade Advisor to examine SQL 2000 instances..So not possible direct upgrade.

38>hi guys any one help me how to restore or rebuild msdb,model databases

Sol:- to restore first we need to check the version of the sql server on which backup was taken and check the version of destination server on which you want to restore

 to restore msdb database first you have to stop SQL SERVER AGENT,  then you can restore the msdb by running restore command

39>What is SQL server Architecture 

Sol:- In this Article we will discuss about MS SQL Server architecture.


The major components of SQL Server are:

  1. Relational Engine
  2. Storage Engine
  3. SQL OS

Now we will discuss and understand each one of them.

1) Relational Engine: Also called as the query processor, Relational Engine includes the components of SQL Server that determine what your query exactly needs to do and the best way to do it. It manages the execution of queries as it requests data from the storage engine and processes the results returned.

Different Tasks of Relational Engine:

  1. Query Processing
  2. Memory Management
  3. Thread and Task Management
  4. Buffer Management
  5. Distributed Query Processing

2) Storage Engine: Storage Engine is responsible for storage and retrieval of the data on tothe storage system (Disk, SAN etc.). to understand more, let’s focus on the concepts.

When we talk about any database in SQL server, there are 2 types of files that are created at the disk level – Data file and Log file. Data file physically stores the data in data pages. Log files that are also known as write ahead logs, are used for storing transactions performed on the database.

Let’s understand data file and log file in more details:

Data FileData File stores data in the form of Data Page (8KB) and these data pages are logically organized in extents.

Extents: Extents are logical units in the database. They are a combination of 8 data pages i.e. 64 KB forms an extent. Extents can be of two types, Mixed and Uniform. Mixed extents hold different types of pages like index, system, data etc (multiple objects). On the other hand, Uniform extents are dedicated to only one type (object).

Pages: As we should know what type of data pages can be stored in SQL Server, below mentioned are some of them:

Log File: It also known as write ahead log. It stores modification to the database (DML and DDL).

·         Roll back transactions if requested

·         Recover the database in case of failure

·         Write Ahead Logging is used to create log entries

·         Transaction logs are written in chronological order in a circular way

·         Truncation policy for logs is based on the recovery model


SQL OS: This lies between the host machine (Windows OS) and SQL Server. All the activities performed on database engine are taken care of by SQL OS. It is a highly configurable operating system with powerful API (application programming interface), enabling automatic locality and advanced parallelism. SQL OS provides various operating system services, such as memory management deals with buffer pool, log buffer and deadlock detection using the blocking and locking structure. Other services include exception handling, hosting for external components like Common Language Runtime, CLR etc.

I guess this brief article gives you an idea about the various terminologies used related to SQL Server Architecture. In future articles we will explore them further.

40>while configuring mirroring can i keep same port numbers on principal & mirror servers? plz could you explain any one...

Sol:-  U r having instances under same domain then u should give different port numbers...

Even if you have all the instances in the same domain but not on the same computer you can have same port number. To put it in simple terms if you have the principal,mirror,witness instances running on the same server then you cannot have same port otherwise you can have same port

41> What is .tuf file in Log shipping

The .tuf file is the Transaction Undo File, and is created when performing log shipping to a server in Standby mode.

In the standby mode, database recovery is done when the log is
restored and this mode also creates a file with the extension .TUF
(which is the transaction Undo file on the destination server). In
this mode we will be able to access the databases.


TUF file is the Transaction Undo File, which is created when performing log shipping to a server in Standby mode.

42> what is the diffrence b/w truncat_only and no_truncat?where which one is use full?

Sol-  No_truncate is used for taking Tail-log backup.truncate_only is for truncating the transaction log file(to clear log file space).this has been depricated in sql 2008 by Microsoft as it was not good practise,if i am not wrong.

truncate_only will flush out all the uncommitted transactions and clear the log space, where as with the help of no_truncate we can take the log backup from the time of last log backup to the point of time failure, so that whatever the transactions are occurred over that time we can recover those transactions.

43> how to add articles to existing publisher?

Sol- In ssms--> replication -->select publisher->right click -> properties -> articles.

 So if we are adding any new table to existing article for publishing it ,full snapshot is required rite?


44> 11 Keyboard Shortcuts Every SQL Server Geek Should Know
1.Open a new Query Window with current connection (Ctrl + N) 
2. Toggle between opened tabs (Ctrl + Tab)
3. Show/Hide Results pane (Ctrl + R)
4. Execute highlighted query (Ctrl + E)
5. Cancel the executing query (Alt + Break or Alt + Scroll Lock)
6. Make selected text uppercase or lowercase (Ctrl + Shift + U, Ctrl + Shift + L)
7. Display estimated execution plan (Ctrl + L)
8. Include actual execution plan (Ctrl + M)
9. Intellisense list member and complete word (Ctrl + Space, Tab)
10. Go to line (Ctrl + G)
11. Comment and uncomment lines of code ( Ctrl + K & Ctrl + C; Ctrl + K & Ctrl + U)

45> Please suggest how to take tail log backup with pros and cons of the same. 

Sol- backup log database_name to disk='path' with no_truncate,stats=1

BACKUP LOG [DBMaint2008] TO DISK = N'D:\SQLskills\DemoBackups\DBMaint_Log_Tail.bck' WITH INIT, NO_TRUNCATE;


46> can i stop sql services when applying services packs or patches or migration process?
any difference b/w sql 2005 and 2008?

Sol- Yes you can apply sp without stopping sql services which is the best way to apply sp. however sp will internally start and stop sql services 3 to 4 times. so there will a downtime so pls notify your business teams and client. Also make sure that you have good db backups and configuration settings and a good recovery plan. Also dont apply sp's directly in prod without proper testing in lower environment. Happy  Patching.

Please note that if you stop sql services and apply sp and then if you start sql services you will not be able to connect to sql server immediately. you need to wait for 2 to 5 mins and then only you will be able to connect to sql server as internally it will run some upgradation scripst

47> I've one dout regarding Instances
Can we know how many instances installed in our sql server By using query(only). ?

Sol- DECLARE @GetInstances TABLE
( Value nvarchar(100),
InstanceNames nvarchar(100),
Data nvarchar(100))

Insert into @GetInstances
EXECUTE xp_regread
@rootkey = 'HKEY_LOCAL_MACHINE',
@key = 'SOFTWARE\Microsoft\Microsoft SQL Server',
@value_name = 'InstalledInstances'


Insert into @GetInstances
EXECUTE xp_regread
@rootkey = 'HKEY_LOCAL_MACHINE',
@key = 'SOFTWARE\Microsoft\Microsoft SQL Server',
@value_name = 'InstalledInstances'

Select InstanceNames from @GetInstances


48> SQL Server Execution Plans

Sol- Execution Plan

• In simple words, it is how SQL Server query optimizer is going to execute a query or already executed a query. A query can be executed in multiple ways but the trick is to find the optimal one in minimum possible time with maintaining the integrity of the database.
• SQL Server uses different means like statistics, index etc.to calculate the optimal plan. But it’s costly and time consuming to calculate the optimal plan every time a query is executed. So SQL Server saves the query plan into the Plan Cache area of the buffer pool and reuses it.
• Query plan or Execution plan serves as the primary mean of troubleshooting and investigating SQL Server Query Performance.

Execution Plan Types
• Estimated
 As the name suggest it is an estimate of the query optimizer about how the query will be executed.
 Generated using statistics.
 Generated before actual execution of the query.
 Some times can vary greatly with the actual plan due to outdated statistics (not desirable).
 Can be viewed by selecting the query and using
§ Ctrl + L key or clicking on Display Estimated Plans on SSMS.
• Actual
 It is the Actual Plan used to execute the query.
 Get stored in Plan Cache if the cost is high and reused.
 Generated using the estimated plan if the estimated plan is used or gets generated during the run time (CPU intensive and costly).
 Can be viewed only after the query gets executed using
§ Ctrl + M key or clicking on Show Actual Plan in SSMS.
§ sys.dm_exec_query_plan DMF can be used to view the actual plan, if the plan is already cached in the Plan Cache.

Execution Plan Formats




Displays a sample graphical plan

Tool Tips:
Each node displays ToolTip information when the cursor is pointed at it as described in the following table. Not all nodes in a graphical execution plan contain all ToolTips items described below

• Physical Operation:
 Displays the physical operator used, such as Hash Join or Nested Loops. Physical operators displayed in red indicate that the query optimizer has issued a warning, such as missing column statistics or missing join predicates.
• Logical Operation:
 Displays the logical operator that matches the physical operator, such as the Inner Join operator. The logical operator is listed after the physical operator at the top of the ToolTip.

• Estimated Row Size:
 Displays the estimated row size.
• Estimated I/O Cost:
 Displays the estimated cost of all I/O activity for the operation. This value should be as low as possible.
• Estimated CPU Cost:
 Displays the estimated cost of all CPU activity for the operation.
• Estimated Operator Cost:
 Displays the cost to the query optimizer for executing this operation. The cost of this operation as a percentage of the total cost of the query is displayed in parentheses. Because the query engine selects the most efficient operation to perform the query or execute the statement, this value should be as low as possible.
• Estimated Subtree Cost:
 Displays the total cost to the query optimizer for executing this operation and all operations preceding it in the same subtree.
• Estimated Number of Rows:
 Displays the number of rows produced by the operator
• Estimated Number of Execution:
 Displays how many times the operator is executed.
• Actual Number of Rows:
 Displays the actual no. of rows processed. Difference between Actual no. of rows and estimated no. of rows is called cardinal estimation. And should be as low as possible.



49> What is the difference between Checkpoint and Lazy Writer




Lazy writer


Checkpoint is used by sql engine to keep database recovery time in check

Lazy writer is used by SQL engine only to make sure there is enough memory left in sql buffer pool to accommodate new pages


Check point always mark entry in T-log before it executes either sql engine or manually

Lazy writer doesn’t mark any entry in T-log


To check occurrence of checkpoint , we can use below query
select * from ::fn_dblog(null,null) 
WHERE [Operation] like ‘%CKPT’

To check occurrence of lazy writer we can use performance monitor
SQL Server Buffer Manager Lazy writes/sec


Checkpoint only check if page is dirty or not

Lazy writer clears any page from memory when it satisfies all of 3 conditions.

1.      Memory is required by any object and available memory is full

2.      Cost factor of page is zero

3.      Page is not currently reference by any connection


Check point should not be very low , it can cause increasing recovery time of database

No. of times lazy writer is executing per second should always be low else it will show memory pressure


Checkpoint will run as per defined frequency

No memory pressure, no lazy writer


checkpoint process does not put the buffer page back on the free list

Lazy writer scans the buffer cache and reclaim unused pages and put it n free list


We can find last run entry of checkpoint in Boot page

Lazy writer doesn’t update boot page


Checkpoint can be executed by user manually or by SQL engine

Lazy writer cant be controlled by user


It keeps no. of dirty pages in memory to minimum

It helps to reduce paging


Checkpoint is affected by Database recovery model

Lazy writer doesn’t get impacted with recovery model of database


To get checkpoint entry in error log
DBCC TRACEON(3502, -1)

Not Applied


50>How can I delete expired data from a huge table without having the log file grow out of control?


I've found it useful when doing deletes from table with a large number of rows to delete rows in batches of say 5000 or so (I usually test to see which value works the fastest, sometimes it's 5000 rows, sometimes 10,000, etc.). This allows each delete operation to complete quickly, rather than waiting a long time for one statement to knock out 400 million records.

In SQL Server 2005, something like this should work (please test first, of course):

WHILE EXISTS ( SELECT * FROM giganticTable WHERE exp_date < getDate())
  DELETE TOP(5000) FROM giganticTable WHERE exp_date < getDate()

I would see what deleting in batches does to the log file size. If it is still blowing up the logs, then you could try changing the Recovery Model to Simple, deleting the records, and then switching back to Bulk Logged, but only if the system can tolerate the loss of some recent data. I would definitely make a Full Backup before attempting that procedure. This thread also suggests that you could setup a job to backup the logs with truncate only specified, so that could be another option. Hopefully you have an instance you can test with, but I would start with the batched deletes to see how that affects performance and the log file size.


//////////////////////2nd sol////////////////////////////////


Each time you perform an insert, update or delete operation on a database table it also affects the indexes for that particular table. If you need to bulk load or delete a mass of data from a highly indexed table, your best choice is to disable all the indexes on a table, perform the insert/delete operation, and then rebuild all the indexes.

Below I will demonstrate a quick way to delete a lot of data from a highly indexed database table in MS SQL 2005 or MS SQL 2008. I used this method to delete 20 million rows from a table on a super active MS SQL database server.

-- 1)    Identify all existing indexes on a table
sp_helpindex [tableName]
-- 2)    Disable each nonclustered index in the list
ALTER INDEX [indexName1] ON [dbo].[tableName] DISABLE
ALTER INDEX [indexName2] ON [dbo].[tableName] DISABLE
-- Important: Do not disable the clustered index because it will prevent
-- access to table data until you drop or rebuild it.
-- 3)    Use new SQL 2005 (2008) DELETE TOP syntax to delete dynamically
--        declared number of rows in batches in order
--        to prevent extensive table locking:
Declare @BatchSize int
Set @BatchSize = 100000 -- select this number based on your context
Declare @RowsAffected int
Set @RowsAffected = 1
While (@RowsAffected > 0)
    delete top (@BatchSize) from [tableName] where [your condition]
    set @RowsAffected = @@rowcount
-- 4) After the above operation completes its execution, rebuild
--     all the indexes you disabled in step 2:
ALTER INDEX [indexName1] ON [dbo].[tableName] REBUILD
ALTER INDEX [indexName2] ON [dbo].[tableName] REBUILD




////////////////////////////////3rd sol/////////////////////////////

How often do you have a situation when you need to remove old records from a table to free storage for new data? If you have a very large table with billions of records the correct solution is essential for the workload and all other underlying operations:

  • Consider the huge amount of transaction log a DELETE operation will cause
  • If you have a high availability scenario the logged operation need to be transferred to the mirror / AlwaysOn-Secondaries / Log shipping partners
  • Each logged operation is time consuming
  • Resources will be locked until the operation is confirmed

The above topics may give an idea of the correct strategy for deletion. Very often the following recommendation is given in forums:

  • Set the database in SIMPLE recovery mode
  • Make database in Single User
  • Make a full backup
  • Delete the data through truncate command.
  • Rebuild the index
  • Return to the FULL recovery mode

The above strategy may work on a single server but cannot be used in a High Availability scenario because it requires the FULL recovery mode.