Search content within the blog

Friday, April 17, 2009

SqlServer Interview Questions Part-II

Q.1. A database is consists of how many type of files ??
A. A database can consist of three types of file :
Primary file , Seconday file and transaction logs
The primary data file is the starting point of the database and points to any other files in the database. If the primary file is not large enough to hold all the data in the database, then secondary files are required.Transaction logs files hold records of updates to the data files, and can be used to recover the database after an error. Every database has at least one transaction log file, and some databases may have more than one.

Q.2. What are the extensions of these three type of file in above question.
A. The recommended file name extension for primary data files is .mdf.
The recommended file name extension for secondary data files is .ndf.
The recommended file name extension for log files is .ldf

Q.3. How many transaction Isolation Level are there in MSSQL Server?
A.There are four isolation levels

Q.4. How many type of Locks are there ?
A. There are three main types of locks that SQL Server uses
Shared locks
Update locks
Exclusive locks

Q.5. What is a Dead Lock ?
A . Deadlock occurs when two users have locks on separate objects and each user wants a lock on the other's object. For example, User1 has a lock on object "A" and wants a lock on object "B" and User2 has a lock on object "B" and wants a lock on object "A". In this case, SQL Server ends a deadlock by choosing the user, who will be a deadlock victim. After that, SQL Server rolls back the breaking user's transaction, sends message number 1205 to notify the user's application about breaking, and then allows the nonbreaking user's process to continue.
Note. To reduce the chance of a deadlock, you should minimize the size of transactions and transaction times.

Q.6. How many instances can be there on a single machine of SQL Server ?
A. On a Single machine there can be 16 Instances running at a time.

Q.7. How many Recovery model are in SQL Server ?
A. There are thre recovery model in MSSQL Server
• Simple Recovery Model .
• Full Recovery Model.
• Bulk Logged Recovery Model.

Q.8. what does the term Orphan user means ??
A.An orphan user is a user in a database with a SID that does not exist in the syslogins table in the master database. Also if the SID stored in the database sysusers table, differs from SID stored in the syslogin table for the matching database user, then the database user is also considered an orphan user.

Q.9 Whats is Log Shipping ?
A. Llog shipping is the process of backing up of database and transaction log files on a production SQL server, and then restoring them onto a standby server. The key feature of log shipping is that it will automatically backup transaction logs throughout the day (for whatever interval you specify) and automatically restore them on the standby server. This in effect keeps the two SQL Servers in "synch". Should the production server fail, all you have to do is point the users to the new server, and you are all set. Well, its not really that easy, but it comes close if you put enough effort into your log shipping setup.

Q.10. How many typf of backups are supported by MSSQL ?
A. there are three type of backup

* Full Database Backup.
* Transaction Log Backup
* Differential Backup

Q.11. What is replication ?
A. Replication is a process which enables data and database objects to be copied and modified from one database to another across different networks and platforms.the physical seperation of the databases and latency are integral part of the design process in replication. MSSQL Server permits 3 different kind of replication. they are snapshot,transactional and merge.

Q.12 What are the default databases in MSSQL Server Instance after Fresh installation
A. there are 4 default databases .

* Master
This is your most critical database, and is the core of your SQL Server implementation.
Limit changes and access to this database as much as possible. Without the master
database, your SQL Server instance will not start up.
This database contains tables that control and track the elements of the SQL Server
Agent, as well as replication, log shipping (Enterprise Edition), database backup and
restore history, database maintenance plan configurations and history, and a subset of
tables participating in replication status and jobs. DTS packages saved to SQL Server are also stored within this database.
* Model
The model database is a template database, which, when modified, will affect the default
values of databases that are created in the future. For example, if a table is added to the
model database, future databases created for the SQL Server instance will include this
* Tempdb
The tempdb database is used to hold all temporary tables and temporary stored
procedures. SQL Server also uses it for internal sorting and query operations (called
work tables, or spooling). This database is re-created each time SQL Server is started,
and cannot be used to store permanent tables.

Q.13. How many access mode of database are in MSSQL
A there are three access mode of a database SINGLE_USER , RESTRICTED_USER and MULTI_USER . SINGLE_USER mode restricts database access to one user Connection. RESTRICTED_USER mode restricts database access to db_owner, dbcreator, and sysadmin roles. All other users are disconnected.MULTI_USER mode lets all users with proper access connect to the database.

Q.14. What is mean by suspect status of a database ?
A. SQL Server gives a database 'suspect' status when it is unable to recover the database . reasons may be many , may be a torn page detection , may be datafile location no found.

Q.15. What is mean by Torn Page?
A. SQL Server pages are 8KB. Windows 2000 disk I/O operations, however, use 512 byte sectors. If an unexpected outage or power failure occurs, a 'torn' page can result, with the 8KB-page write not completely written. Enabling the TORN_PAGE_DETECTION option allows SQL Server to detect incomplete I/O operations. If a torn page occurs when the database is online, an I/O error is raised and the connection is killed;

Q.16. how you can rename a database ?
A. To rename a database first get the database into Single user mode . by using the query below ( run these query in query analyzer)


then rename the database using sp_rename

sp_renamedb 'DBMydb', 'DBmydb_new'

then change the access mode of the database to multiuser


No comments:

Post a Comment