Top 37 Sql Server 2000 Interview Questions You Must Prepare 26.May.2022

RAID stands for redundant array of inexpensive disks, used to provide fault tolerance to database servers. There are six RAID levels 0 through 5 offering different levels of performance, fault tolerance.

Lock escalation is the process of converting a lot of low level locks (like row locks, page locks) into higher level locks (like table locks).Every lock is a memory structure too many locks would mean, more memory being occupied by locks.

In logshipping the transactional log file from one server is automatically updated in backup database on the other server and in the case when one server fails the other server will have the same DB and we can use this as the DDR (disaster recovery) plan.

Dirty pages are data pages that have been entered into the buffer cache and modified, but not yet written to the disk.

Check point writes all dirty pages for the current database to disk.

There are 3 database files in sql server 2000.
They are: 

  • Primary file
  • Secondary file
  • Log file.

No, Log file is a never part of file group.

The recovery models in sql server 2000 are:

  • Simple recovery model
  • Full recovery model
  • Bulk-Logged recovery model.

Microsoft SQL Server is a relational model database server produced by Microsoft. Its primary query languages are T-SQL and ANSI SQL.

Extent is a collection of 8 sequential pages.
There are 2 types of extents.

  • Uniform extent :- Uniform means when extent is own by a single object means all collection of 8 pages hold by a single extend is called uniform.
  • Mixed extent :- Mixed means when more than one object is comes in extents is known as mixed extents.

Snapshot replication distributes data exactly as it appears at a specific moment in time and does not monitor for updates to the data. Snapshot replication is best used as a method for replicating data that changes infrequently or where the most up-to-date values (low latency) are not a requirement. When synchronization occurs, the entire snapshot is generated and sent to Subscribers.
Transactional replication, an initial snapshot of data is applied at Subscribers, and then when data modifications are made at the Publisher, the individual transactions are captured and propagated to Subscribers.
Merge replication is the process of distributing data from Publisher to Subscribers, allowing the Publisher and Subscribers to make updates while connected or disconnected, and then merging the updates between sites when they are connected.

A transaction is a logical unit of work in which, all the steps must be performed or none.

The default system databases in sql server 2000 are:

  • Master
  • Model
  • Tempdb
  • Msdb.

You can find out the install paths from the registry key corresponding to each instance. The following registry key contains the required information:

HKEY_LOCAL_MACHINE /Software /Microsoft SQL Server /{Instance Name} /MSSQLServer /Setup /SQLpath.

There are two ways to install the Client tools:

  • Use the Installation Definition screen to select the components to include in this installation of SQL Server 200@If you select Client Tools Only or Connectivity Only, setup proceeds and no additional choices are required, unless you select components when installing client tools.
  • Use the Custom Setup option, and in the Select Components screen, you can choose to install individual subcomponents of Client Management Tools.

In this situation, minimize the Setup window (and other windows if necessary) to see whether there are any message boxes relating to File Copy or File Sharing. You must respond to any such messages before Setup can proceed.

If you are planning to install identical installations of SQL Server 2000 on several computers, or if you need to install SQL Server 2000 on computers that you cannot administer remotely, you can use the unattended install option. The most important component of an unattended install is the setup initialization file. The setup initialization file can be obtained in any of the following ways:

  • The SQL Server 2000 CD contains several sample .iss files (Sqlins.iss, Sqlcli.iss, Sqlcst.iss) for the different types of installations.
  • Each time that you perform an interactive SQL Server 2000 setup, it records all of the selections you make and saves them in the Setup.iss file found in the system root folder.
  • You can use the Record Unattended .ISS file option in the Advanced Options screen to create a completely customized Setup.iss file.

After you have the .iss file, you can perform the unattended install by starting the Setupsql.exe program with the appropriate parameters.

Alter database [Database name] set recovery full
Go.

SQL Server 2000 clustering does not provide load balancing; it provides failover support. To achieve load balancing, you need software that balances the load between clusters, not between servers within a cluster.

A collation specifies the bit patterns that represent each character and the rules by which characters are stored and compared.

Address Windowing Extensions API is commonly known as AWE. AWE is used by SQL Server when it has to support very large amounts of physical memory. AWE feature is only available in SQL Server Enterprise, Standard, and Developer editions with of SQL Server 32 bit version.

ACID (an acronym for Atomicity Consistency Isolation Durability) is a concept that Database Professionals generally look for when evaluating databases and application architectures. For a reliable database all this four attributes should be achieved.

  • Atomicity is an all-or-none proposition.
  • Consistency guarantees that a transaction never leaves your database in a half-finished state.
  • Isolation keeps transactions separated from each other until they’re finished.
  • Durability guarantees that the database will keep track of pending changes in such a way that the server can recover from an abnormal termination.

File group is a group of files logically grouped together.

There are two types of filegroups:

  • Primary :- The primary file group contains the primary data file and any other files not specifically assigned to another file group. All pages for the system tables are allocated in the primary file group.
  • User-defined :- User-defined file groups are any file groups specified using the FILEGROUP keyword in a CREATE DATABASE or ALTER DATABASE statement.

The names of all SQL Server instances on a computer can be found from the Installed Instances value which is located under the following registry key:

HKEY_LOCAL_MACHINE/SOFTWARE/Microsoft/Microsoft SQL Server.

Once data is stores in memory user can make changes to that a log file is generated. This log file is generated in every five minutes of transaction is done. After this sql server writes changes to database with the help of transaction log files. This is called Write-ahead log.

To determine the service pack that installed on your sql server ,connect to your server ,execute the command:

  Select @@version  Go.

sp_renamedb ‘oldname’, ‘newname’.

SQL Server 2000 is available in seven different editions. They are:

  • Enterprise Edition
  • Standard Edition
  • Personal Edition
  • Developer Edition
  • Microsoft Windows CE edition
  • Enterprise Evaluation Edition
  • Desktop Engine.

Yes, For a successful installation, follow these steps:

  1. Log on to the computer using a windows user account which is part of the local administrator’s user group.
  2. Close all applications and stop the services that use ODBC.
  3. Close event viewer and registry editor.

Workload governor limits the performance of SQL SERVER Desktop engine (MSDE). Workload governor sits between the client and the database engine and counts the number of connections per database instance. If Workload governor finds that the number of connections exceeds eight connections, it starts stalling the connections and slowing down the database engine.