SQL Server Interview Questions for DBAs and Devlopers
Download SQL Server Interview Question in PDF
3 Replies
Category Archives: Database
Transaction Log Backup
Transaction Log Backup
In Full or Bulk Logged recovery models, it is very important that we have scheduled periodic Transaction Log backups so it will help us to maintain the the size of the transaction log within reasonable limits and will allow for the recovery of data with the least amount of data loss in case of any failure.
Transaction Log backups come in three forms:
Pure Log Backup: —A Pure Log backup contains only transactions and is completed when the database is in Full recovery model or Bulk Logged recovery model, but no bulk operations have been executed. In case of Bulk Logged recovery Bulk Operations are minimally logged.
Bulk Log Backup: —Bulk Log backups contain both transactional data and any physical extents modified by bulk operations while the database was in Bulk Logged recovery.
Tail Log Backup: —Tail Log backups are completed when the database is in Full or Bulk Logged recovery prior to a database restoration to capture all transaction log records that have not yet been backed up. It is possible in some instances to execute a Tail Log backup even if the database is damaged.
Pure or Bulk Log Backup Example
BACKUP LOG SQLDBPool
TO DISK = ‘D:\SQLBackups\SQLDBPool.TRN’
Tail Log Backup Example
BACKUP LOG SQLDBPool
TO DISK = ‘D:\SQLBackups\SQLDBPoolTailLog.TRN’
WITH NO_TRUNCATE
How the SQLBrowser Service Works Internally?
The SQLBrowser Service is used by SQL Server for named instance name resolution and server name enumeration over TCP/IP and VIA networks.
The default instance of SQL Server is assigned the TCP Port 1433 by default to support client incoming requests. However, because more than one application/SQL Server Instances cannot share a port assignment, any named instances are given a random port number when the service is started. This random port assignment makes it difficult for clients to connect to it, because the client applications don’t know what port the server is listening on. To meet this need, the SQLBrowser Service was created.
On start‐up, the SQLBrowser Service queries the registry to discover all the names and port numbers of installed servers and reserves UDP Port 1434. It then listens on UDP Port 1434 for SQL Server Resolution Protocol (SSRP) requests and responds to the requests with the list of instances and their respective port assignments so that clients can connect without knowing the port number assignment.
It is very important that no unauthenticated traffic on UDP Port 1434 be allowed on the network, because the service will respond to any request on that port.
If the SQLBrowser Service is disabled, it will be necessary to specify a static port number for all named instances of the SQL Server Service and to configure all client applications that connect to those instances with the appropriate connection information.
There will be only one SQL Browser Service for all the instances on same machine.
Row Versioning in SQL Server 2005
SQL Server 2005 has introduced two new Isolation Levels. We can use these Isolation Levels for row versioning.
1. READ_COMMITTED_SNAPSHOT (statement level)
2. ALLOW_SNAPSHOT_ISOLATION (transaction level)
These Isolation level turned on database level. You can turn on the Isolation Level using below command.
ALTER DATABASE sqldbpool
SET READ_COMMITTED_SNAPSHOT ON
ALTER DATABASE sqldbpool
SET ALLOW_SNAPSHOT_ISOLATION ON
When above Isolation level is turned on it will enable the row versioning at database level. Transaction or statement views the data as it existed at the start of the statement or transaction, instead of protecting all reads with locks. Row versioning will reduce the blocking/deadlock issues and boost the database performance. Row versioning also prevents users from reading uncommitted data and prevents multiple users from attempting to change the same data at the same time.
You can query sys.databases to check the above isolation level status.
SELECT name, snapshot_isolation_state_desc, is_read_committed_snapshot_on FROM sys.databases
Ranking Function – NTITLE()
NTITLE() function is used to break up a record set into a specific number of groups.
SELECT NTILE(3) OVER (ORDER BY Age) AS [Group by Age],
EmpName,
Age
FROM Emp
Group by Age EmpName Age
-------------------- --------- -----------
1 Bill 11
1 Dhvani 20
1 Nehal 20
2 R 30
2 Abhinav 40
2 Suvrendu 40
3 Nirmal 50
3 Sunil 95
3 Ram 100
(9 row(s) affected)
aiDeeva.com 