Microsoft SQL Server 2000 Enterprise Edition

Posted on
Share on Google+Share on LinkedInShare on FacebookShare on RedditTweet about this on TwitterEmail this to someone

Objective : Creating SQL Server 2000 Databases
SubObjective : Attach and detach databases.
(Single Answer Multiple Choice)

 

You are the SQL administrator for TriState Communications (TSC). TSC maintains a very large technical support database, named TechSupport, which is used to store all technical support calls and solutions. TSC’s call center is being moved from the Charlotte office to the Atlanta office. All servers for TSC are members of the same Windows 2000 domain named TSCDom.

 

Currently, the database resides on a SQL Server 2000 computer in Charlotte, named CharSQL. However, due to the slow communication connection between Charlotte and Atlanta, you decide to move the database to a SQL Server 2000 computer in Atlanta, named AtlSQL. The TechSupport database uses Windows Authentication.

 

You detach the database using the sp_detach_db system stored procedure. You move the primary file, the 20 secondary files, and the transaction log file to AtlSQL.

 

What should you do next?

 

 

  1. Rebuild the registry on AtlSQL.
  2. Rebuild the master database on CharSQL.
  3. Rebuild the registry on AtlSQL and CharSQL.
  4. Rebuild the master database on AtlSQL and CharSQL.
  5. Attach the database to AtlSQL using the sp_attach_db system stored procedure.
  6. Attach the database to AtlSQL using a CREATE DATABASE statement with the FOR ATTACH clause.

 

Answer:
F. Attach the database to AtlSQL using a CREATE DATABASE statement with the FOR ATTACH clause.

Tutorial:
You should attach the database to AtlSQL using a CREATE DATABASE statement with the FOR ATTACH clause. If more than 16 files must be specified, you should use a CREATE DATABASE statement with the FOR ATTACH clause.

You should not rebuild the registry on either of the computers. This solution is drastic. If proper backups were not complete, this solution could actually cause damage to the database. This utility should only be used when absolutely necessary. If the proper detachment and attachment process is followed, any changes to the registry will be properly implemented.

You should not rebuild the master database on either of the computers. This solution is drastic and could actually cause damage to databases or database access if backups were not just completed. This utility should only be used when absolutely necessary. If the proper detachment and attachment process is followed, any changes to the master database will be properly implemented.

You should not attach the database to AtlSQL using the sp_attach_db system stored procedure. This is the normal procedure in most instances. However, when there are more than 16 files to be specified in the attachment, you should use the CREATE DATABASE statement using the FOR ATTACH clause.

Reference:
1. SQL Server Books Online – SQL Server Architecture
– Databases – Modifying a Database – Attaching and Detaching a Database

2. SQL Server Books Online – Transact-SQL Reference
– CREATE DATABASE

 


Objective : Managing, Monitoring, and Troubleshooting SQL Server 2000 Databases
SubObjective : Optimize database performance.
(Single Answer Multiple Choice)

 

You are the SQL administrator for your company. You have a database named Personnel that you monitor on a weekly basis. This database has a primary filegroup and two secondary filegroups.

 

Recently, you have noticed that the Page Splits/sec counter of the SQL Server: Access Methods object has been increasing steadily and has now reached an alarming level.

 

What should you do?

 

 

  1. Add more RAM to the SQL server.
  2. Create another filegroup for Personnel.
  3. Increase the size of the paging file on the SQL server.
  4. Move the Personnel database to a hard disk with more free disk space.
  5. Rebuild the indexes of the Personnel database with a fill factor of 60.
  6. Rebuild the indexes of the Personnel database with the default fill factor.

 

Answer:
E. Rebuild the indexes of the Personnel database with a fill factor of 60.

Tutorial:
You should rebuild the indexes of the Personnel database with a fill factor of 60. Page splits occur when an index page is full. When full pages exist, an update to the index will cause a change in the page because a new index page must be inserted. One way to reduce page splitting and fragmentation is to tune the fill factor of the index nodes. Rebuilding the indexes will defragment the index pages and should decrease the Page Splits/sec counter.

You should not add more RAM to the SQL server, create another filegroup for Personnel, or increase the size of the paging file on the SQL server. This may help improve overall performance, but by itself would not affect the Page Splits/sec counter.

You should not move the Personnel database to a hard disk with more free disk space. This may improve database performance by giving the database more room to grow. However, index pages will still be full and will continue to split.

You should not rebuild the indexes of the Personnel database with the default fill factor. With the default fill factor of zero, the pages are completely filled. With this solution, you would still have a lot of page splits.

Reference:
1. Microsoft SQL Server 2000 Administrator’s Companion – Creating and Using Indexes
– Creating Indexes – Using Fill Factor to Avoid Page Splits

 


Objective : Managing, Monitoring, and Troubleshooting SQL Server 2000 Databases
SubObjective : Optimize database performance.
(Single Answer Multiple Choice)

 

You administer your company’s SQL Server 2000 computer named SQL2K. A database named Contacts resides on SQL2K. SQL2K contains one physical disk divided into two partitions. The primary and secondary filegroups reside on the first partition. The transaction log file resides on the second partition.

 

A full database backup completes at 1:00 p.m. The following sequence of events occurs:

 

1:12 p.m. – Transaction 145 starts.
1:15 p.m. – Differential backup begins.
1:16 p.m. – Transaction 146 starts.
1:17 p.m. – Transaction 147 starts.
1:18 p.m. – Transacti

Share on Google+Share on LinkedInShare on FacebookShare on RedditTweet about this on TwitterEmail this to someone
cmadmin

ABOUT THE AUTHOR

Posted in Archive|

Comment:

Leave a comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>