Term
| Error numbers returned when a db runs out of disk space |
|
Definition
|
|
Term
| Error number returned when the transactions log runs out of disk space |
|
Definition
|
|
Term
| Actions to resolve a transaction log out of space error |
|
Definition
- Back up the transaction log.
- Add disk space to the volume that the transaction log file is on.
- Move the log to a volume with more space.
- Increase the size of the transaction log file.
- Add another log file on the disk volume that has space.
|
|
|
Term
| What are the main places to find error and informational messages about the database engine? |
|
Definition
DB Engine: SQL Server error log and Windows Application Event log.
Logins/Logouts: SQL Server error log and Windows Security log.
Hardware/OS errors/info: Windows System Event log. |
|
|
Term
| What are two utilities you can use to view the state and configuration of SQL Server services? |
|
Definition
| The Windows Services console and SQL Server Configuration Manager. |
|
|
Term
| What should the start mode be for all SQL Servers services in a clustered environment? |
|
Definition
|
|
Term
| What are the four system databases on every instance? |
|
Definition
|
|
Term
| Errors in which three databases prevent SQL Server from starting? |
|
Definition
- master
- tempdb
- mssqlsystemresource
|
|
|
Term
| What errors do you see if there is a fault in either the memory or processor? |
|
Definition
| Memory or processess problems, you see STOP errors. A memory error when the computer is booting results in a POST error. Both POST and STOP are accompanied by a blue screen. |
|
|
Term
| What are the three lock types? |
|
Definition
|
|
Term
| What are the three most common locking modes? |
|
Definition
|
|
Term
| Can a row lock be escalated to a page lock? |
|
Definition
| No. A row lock can be promoted to a table lock or a page lock to a table lock. |
|
|
Term
| Describe the two percent rule used by the Lock Manager. |
|
Definition
| If SQL Server determines that more than two percent of the rows on a page will need to be accessed, a page lock is acquired. Likewise, if more than two percent of the pages in a table need to be accessed, a table lock is acquired. |
|
|
Term
| What are the five isolation levels? |
|
Definition
- Read Uncommitted
- Read Committed (default)
- Repeatable Read
- Read Serialize
- Snapshot
|
|
|
Term
| What is the error code for a deadlock? |
|
Definition
|
|
Term
| How does a deadlock occur? |
|
Definition
| A deadlock requires at least two processes that are both modifying data. Each process acquires an exclusive lock on a resource and then attempts to acquire a shared lock on the same resource exclusively locked by the other process. |
|
|
Term
| What recommendations will the Database Engine Tuning Advisor (DTA) make? |
|
Definition
- Adding indexes
- Dropping indexes
- Partitioning tables
- Storage aligning tables
|
|
|
Term
| What resources can be managed by the Resource Governor? |
|
Definition
|
|
Term
| What is a classifier function? |
|
Definition
| A function that you create in the master database. Only one classifier function can be active for Resource Governor at a time. |
|
|
Term
| What are the steps to implement Resource Governor? |
|
Definition
- Enable Resource Governor.
- Create one or more resource pools.
- Create one or more workload groups.
- Associate each workload group to a resource pool.
- Create and test a classifier function.
- Associate the classifier function to Resource Governor.
|
|
|
Term
| Explain the collation precedence rules. |
|
Definition
Explicit collation takes precedence over implicit collation.
Implicit collation takes precendence over coercible-default collation. |
|
|
Term
| At what fragmentation threshold is it recommended to rebuild an index instead of reorganizing it? |
|
Definition
|
|
Term
| What editions of SQL Server 2008 can be used to upgrade a 32-bit version of SQL Server 2005 Express edition to SQL Server 2008? |
|
Definition
| SQL Server 2008 Express or SQL Server 2008 Express Advanced. |
|
|
Term
| What are the two basic components of Database Mail? |
|
Definition
| Mail profiles which can contain one or more mail accounts. |
|
|
Term
| You configure your instance to use the -T1222 and -T1204 trace flags during startup. How do you ensure that your failure recovery plan performs backup of the use of the trace flags? |
|
Definition
| You backup the SQL Server registry hive. |
|
|
Term
| An IIS application will use anonymous access to an SSAS instance. What should you do? |
|
Definition
| The Security/RequireClientAuthentication server configuration should be set to False. |
|
|
Term
| You get a report that says a database is having deadlock problems. What should you do to capture the deadlock information. |
|
Definition
| You should set the appropriate trace flags as a startup parameter, and restart the instance. |
|
|
Term
| An application is having poor performance. Which performance object should you use to capture the appropriate information? |
|
Definition
| SQL Server:Access Methods |
|
|
Term
| When upgrading SQL Server from one version to another, how do you monitor the instance being upgraded and record the use of features that will be discontinued? |
|
Definition
| You should use the SQL server-side trace. The trace captures the Deprecation Announcement and Deprecation Final Support event classes. |
|
|
Term
| You are designing a consolidated repository of performance data. Four requirements need to be met: the data collector is used to gather performance information; a single database stores performance information for all instances; performance information that is older than 15 days is deleted; reduce the administrative effort to manage performance. What action should you perform? |
|
Definition
| Configure a management data warehouse process on each instance, then use this process to store and delete performance data in a single database for all instances. |
|
|
Term
| A report that uses a stored procedure as a data source is returning inconsistent data. You find that phantom reads are causing the problem. How do you make sure that the data being returned is consistent? |
|
Definition
| Modify the stored procedure to use the Snapshot isolation level. |
|
|
Term
| On two cluster-ready nodes, you intend to perform a SQL Server 2008 mission-critical installation. Constant availability is required for the cluster. You have to configure the cluster to failover and reduce the service disruption. Which failover option shoul you use? |
|
Definition
| You should prevent automatic failback. |
|
|
Term
| Clients can access their profile data by using a web application. You must ensure the security of the customer data, you must ensure that even if the backup media is lost, data files, log files and subsequent backups are safe. What should you do to achieve this goal? |
|
Definition
| For the database, enable Transparent Database Encryption and back up the transaction log. |
|
|
Term
| After discovering the SQL Server Agent service does not run and the SQL Server Agent job no longer functions, which log should you check? |
|
Definition
|
|
Term
| Your company security rules forbid users to access tables directly. The following error is raised when Procedure01 is executed by users: "The SELECT permission was denied on the object ..." What action should you take? |
|
Definition
| You should alter Procedure01 and add the WITH EXECUTE AS OWNER option to its header. |
|
|
Term
| With SSRS, how do you ensure version histories of all reports can be kept? |
|
Definition
| Configure SSRS database, make it use SharePoint integrated mode. |
|
|
Term
| What database permissions does a user need to log in and maintain database snapshots? |
|
Definition
|
|
Term
| How do you make sure that a job named Job4 doesn't run Wednesday? |
|
Definition
| You should remove the schedule for Wednesday from Job5. |
|
|
Term
| You have to defragment indexes, making sure that you reduce the effect on database availability. In the ALTER INDEX statement, which option should be included? |
|
Definition
|
|
Term
| You create a package definition in the SQL Server Import and Export Wizard and now you have to reexecute the package from the command line. Which utility should you use? |
|
Definition
|
|
Term
| A table contains redundant data and you must minimize the disk space used to store the table. Which compression technology should you use? |
|
Definition
|
|
Term
| Users must be able to use the OPENROWSET() function to query remote data source. Which sp_configure option should you configure? |
|
Definition
| Ad Hoc Distributed Queries |
|
|
Term
| Which statement would move the Service.Table table to the Costs schema? |
|
Definition
| ALTER SCHEMA costs TRANSFER Service.Table |
|
|
Term
| How would you test the failover response of a cluster manually? |
|
Definition
1. Get rid of the shared array from the active node.
2. Unplug the network cable(s) from the active node. |
|
|
Term
| Every day, you create a read-only copy of the database for reporting purposes and you need to make sure you use as little disk space as possible. What strategy should you use? |
|
Definition
|
|
