////////////////Facebook/////////////////////
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
CROSS JOIN
(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
INNER JOIN
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?
Ans:-
In This case give latest
Full backup , latest Dif backup and subsequent Trns backups to your user
otherwise
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.
Ans-
use
msdb
select * from dbo.log_shipping_secondary
8> ///////// Script to get to know most accessed
tables:///////////////////////////
SELECT
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'
FROM
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?
CREATE LOGIN sam
WITH PASSWORD = '';
USE AdventureWorks;
CREATE USER sam FOR LOGIN sam;
GO
GRANT SELECT TO sam
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.
SET TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SNAPSHOT | SERIALIZABLE }[ ; ]
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;GOSET TRANSACTION ISOLATION LEVEL REPEATABLE READ;GOBEGIN TRANSACTION;GOSELECT * FROM HumanResources.EmployeePayHistory;GOSELECT * FROM HumanResources.Department;GOCOMMIT TRANSACTION;GO
16>How to find session-Id of each individual users?
Ans-Select hostname, nt_username From sys.sysprocesses
Or
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
Ans-
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
Ans-
SELECT TOP 50 [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 - qs.statement_start_offset)/2),[Parent Query] = qt.text,DatabaseName = DB_NAME(qt.dbid)FROM sys.dm_exec_query_stats qsCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qtORDER 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
WHERE A.name IS NOT NULL
ORDER BY DATEDIFF(d,STATS_DATE(A.OBJECT_ID, index_id),getdate()) DESC
--or
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
value
FROM
[fn_trace_getinfo](NULL)
WHERE [property] = 2
)), DEFAULT) T
JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
JOIN sys.trace_subclass_values v ON v.trace_event_id =
TE.trace_event_id
AND v.subclass_value =
t.EventSubClass
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.
ALTER DATABASE dbName SET EMERGENCY
After that, you set the database to the single-user mode. Single user mode
allows you to recover the damaged database.
ALTER DATABASE dbName SET SINGLE_USER
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.
DBCC CheckDB (dbName , REPAIR_ALLOW_DATA_LOSS)
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:
ALTER DATABASE dbName SET MULTI_USER
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:
SELECT
[Current
LSN],
[Operation],
[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
36>WHAT IS THE PERPOSE OF MAKING
RESOURCE DATABASE HIDDEN?
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:
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:
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 File: Data 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;
GO
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
/////////////////////////////////////////////////////////////////////////////////
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
Graphical
Text
XML
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.§
Sol-
|
Row |
Checkpoint |
Lazy writer |
|
1 |
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 |
|
2 |
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 |
|
3 |
To check occurrence
of checkpoint , we can use below query |
To check occurrence
of lazy writer we can use performance monitor |
|
4 |
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 |
|
6 |
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 |
|
7 |
Checkpoint will run
as per defined frequency |
No memory pressure,
no lazy writer |
|
9 |
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 |
|
10 |
We can find last run
entry of checkpoint in Boot page |
Lazy writer doesn’t
update boot page |
|
11 |
Checkpoint can be
executed by user manually or by SQL engine |
Lazy writer cant be
controlled by user |
|
12 |
It keeps no. of
dirty pages in memory to minimum |
It helps to reduce
paging |
|
17 |
Checkpoint is
affected by Database recovery model |
Lazy writer doesn’t
get impacted with recovery model of database |
|
18 |
To get checkpoint
entry in error log |
Not Applied |
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())
BEGINDELETETOP(5000) FROM giganticTable WHERE exp_date < getDate()
ENDI 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.
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] DISABLEGOALTER INDEX [indexName2] ON [dbo].[tableName] DISABLEGO -- 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 intSet @BatchSize = 100000 -- select this number based on your context Declare @RowsAffected intSet @RowsAffected = 1 While (@RowsAffected > 0)begin delete top (@BatchSize) from [tableName] where [your condition] set @RowsAffected = @@rowcountend-- 4) After the above operation completes its execution, rebuild-- all the indexes you disabled in step 2:
ALTER INDEX [indexName1] ON [dbo].[tableName] REBUILDGOALTER INDEX [indexName2] ON [dbo].[tableName] REBUILDGO
////////////////////////////////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:
The
above topics may give an idea of the correct strategy for deletion. Very often
the following recommendation is given in forums:
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.