Which performance object should she add to the counter log?
You are employed as a database administrator at Domain.com. The Domain.com network consists of a single Active Directory domain named Domain.com. Domain.com has a server named Certkiller -DB01 that is configured to run SQL Server 2005 Enterprise Edition.
Certkiller -DB01 has 2 GB of RAM. However, 1.6 GB is used by the default SQL Server database engine instance. 100 MB is the average data growth of all databases combined per month. You have received several reports from clients sating that the execution times are increasing.
A Domain.com employee named Kara Lang works in the Research and Development department. You instruct her to assess whether more RAM is required. Kara Lang needs to make use of the System Monitor to create a counter log. This counter log will aid her in deciding whether to add more RAM. Which performance object should she add to the counter log?
What should you do to achieve this objective?
You are the database administrator for a major electronics chain. You manage all the SQL Server 2008 databases of the company.
The data is stored in databases named Prod1 and Prod2.
These databases areaccessed by users in the company by using four different applications named Cust_sales , Cust_audit , Retail_sales , and Yearly_total.
Users of the Cust_sales and Cust_audit applications complain that either the transactions time out frequently ortake too long to complete.
You want to monitor the applications causing the transaction blocks by using the Activity Monitor.
exhibit What should you do to achieve this objective?
What else should you do to ensure that all SQL logins created for the research department employees meet thefo
You are the SQL administrator for your company. You have installed SQL Server 2008 on two Windows Server2008 computers.
You create a new database named Research for the research department on the SQL Server2008 instance named SQL_production.
You have been asked to ensure that all SQL logins created for the research department employees meet thefollowing criteria:
? The logins should be able to issue commands within the Research database without specifically includingthe database name in the command.
? The Windows Server 2008 password policies and password expiration should be enforced for the SQLlogins.
? The account should create a temporary password and force the user to reset the password.
You decide to create a template account to use to create all the research department accounts.
You plan tocreate a CREATE LOGIN script from the template account for future use.
You open the SQL Server Management Studio, and open the SQL_production instance.
Then you right-click the Security folder, point to New , and select Login.
On the General page, you type Template in the Login namebox.
exhibit What else should you do? (Choose all that apply. Each correct answer represents part of the solution.)
What should you do to automate these tasks to occur at the scheduled time on all weekdays?
You are a database administrator for your company. You are managing the production database Prod1 that resides on the SQL Server 2008 instance named Sql1.
You perform the following tasks on Prod1 as a part of your daily activities:
* Perform a differential database backup at 8:00 P.M.
* Rebuild indexes on database tables at 9:00 A.M.
* Perform a backup of the transaction log in the database at 12:00 P.M.
* Update the database statistics at 8:00 A.M.
You want to automate these tasks to occur at the scheduled time on all weekdays.
exhibit What should you do? (Choose two. Each correct answer represents part of the solution.)
What should you do to correct the following problem?
You work as a database administrator at Domain.com. Domain.com is using a SQL Server
2008 instance. The SQL Server 2008 instance contains a database named CK_DB that has a
stored procedure which is as follows:
1 CREATE PROCEDURE Products.TestProcedure
2 AS
3 IFOBJECT_ID(‘ Products. TableTest1’) IS NOT NULL
4 DROPTABLE Products.TableTest1;
5
6 CREATE TABLE Products. TableTest1 (
7 Idint PRIMARY KEY CLUSTERED,
8 Namevarchar(100);
9 );
10
11 …
12 GO
However, a Domain.com user has called the TestProcedure and received the following:
Msg 262, Level 14, State 1, Procedure TestProcedure, Line 15CREATE TABLE permission
permitted in database ‘ CK_DB ‘."
You need to correct the problem. What should you do?
Which Transact-SQL statement should you use?
You are the database administrator of your company. The network contains a SQL Server 2008 computer thathas two partitioned tables named Sales and Sales_Hy.
You want to switch a partition from the Sales table to the Sales_Hy table.
Which Transact-SQL statement should you use?
What should you do to achieve this?
You manage a SQL Server 2008 database for a banking firm.
You currently perform a full database backup of thedatabase by using the Full Recovery model with transaction log backups.
This ensures that there is no data lossin case of a database failure. You regularly receive large files from other trading and investment firms.
The data in the files is imported intotemporary tables.
After the import, you insert this data into the appropriate tables in your database by using BULK INSERT statements.
This import process exhausts all the log space assigned to the database.
This bulk insert operation is not critical because the bulk operation can be restarted without affecting the databaseoperations if it fails.
You must prevent the log file from growing. Data loss is unacceptable.
What should you do to achieve this?
Which event class should you monitor?
You are the database administrator of your company. You are creating a new trace on an instance of SQL Server 2008 named SQL1.
You want to monitor all connection events when a client requests a connection to SQL1.
You navigate to the Events Selection tab in the Trace Properties dialog box.
Which event class should you monitor?
Which performance monitor counters should you add in the counter log?
You are the database administrator for your company and manage all the company’s SQL Server 2008databases. Database users report that overall performance of the database has degraded. You suspect that lackof memory is the cause of the degrading performance. Before you add memory to the SQL server, you want to monitor the amount of memory used by SQL Server. Which performance monitor counters should you add in the counter log? (Choose three.)
Which Transact-SQL should you use?
You are the database administrator for a major shipping company. You manage all the SQL Server 2008 databases of the company.
You have created different jobs in the database to perform different administrativefunctions.
Due to recent scheduling changes, you must reschedule the jobs to be executed at a different time.
You want toidentify details of the last job that was successfully executed on the server.
Which Transact-SQL should you use?