Quiz help required

1) What is the commonly fixed database role of a db_datawriter?

a)Add, change or delete data from all the tables
b)Assign statement and object permissions
c)Backup and restore databases
d)Read data from any table

2) ). How would you add a country field to your database to ensure that your Argentinean subsidiary does business only with other Argentinean companies?

a)CHECK constraint
b)PRIMARY KEY constraint
c)FOREIGN KEY constraint
d)DEFAULT constraint

3) You want to set up replication between two databases, so the financial data and the sales data will be the same. You want the data to replicate at 1:00 a.m. every morning. You would like to completely remove all data from the financial database each night and overwrite data from the sales database. Which database replication model would you choose?

a)Transactional replication
b)DTC replication
c)Subscriber replication
d)Snap shot replication
e)Merge replication

4) You start SQL-Server with the -f option. Unfortunately now you can't establish a connection to your SQL-Server. What should you do?

a)Edit regsitry
b)Restore registry from backup
c)Rebuild master database
d)Reinstall SQL -Server
e)Run regrebuld.exe

5). You define full-text indexing on the ProductName column in the Products table. You then execute a full-text query on the column. You specify a word that you know is present in the column, but the result set is empty. What is the most likely cause?

a)The Microsoft Service is not running
b)The SQL ServerAgent Service is not running
c)The catalog is not populated
d)You did not create a unique SQL Server index on the ProductName column

6) Exchange and SQL 7.0 are running on the same server. You notice the performance in exchange is degraded. The Min server memory, Maximum server memory and set working area are set as they were automatically in the installation. What do you do to free memory for exchange?

a)Increase Min server memory
b)Set working area to 0
c)Set working area to 1
d)Increase memory allocated to the procedure cache option
e)Reduce Min server memory

7) What functions are performed by the SQL Server Agents?
(Choose all that apply)

a)Notification
b)Job execution
c)User security managment
d)Replication management
e)Alert management.

8) The SQL server that Michael manages crashed. The disk drives were not damaged but there was data that had not been written to some databases. Which transactions will be rolled forward in each database when his SQL server starts the automatic recovery process?

a)All committed transactions that are in the transaction log between the last checkpoint and the failure
b)All committed transactions that are in the transaction log
c)All committed transactions that are in the transaction log between the last two checkpoints
d)All uncommitted transactions that are in the transaction logPlease do not post this kind of questions here .|||Check in your courseware, i believe the answer is hiding somewhere. Good luck & Take care.

Quick question on Cascade Delete

Is cascade delete and cascade update new to 2000 or did it exist in 7?
Thank you,
MikeNew for 2000
"Mike Hildner" <mhildner@.afweb.com> wrote in message
news:ORKfTeV$DHA.3804@.TK2MSFTNGP09.phx.gbl...
> Is cascade delete and cascade update new to 2000 or did it exist in 7?
> Thank you,
> Mike
>|||Thank you.
"Scott Morris" <bogus@.bogus.com> wrote in message
news:%23Jc8kqV$DHA.712@.tk2msftngp13.phx.gbl...
> New for 2000
> "Mike Hildner" <mhildner@.afweb.com> wrote in message
> news:ORKfTeV$DHA.3804@.TK2MSFTNGP09.phx.gbl...
>

Quick question on Cascade Delete

Is cascade delete and cascade update new to 2000 or did it exist in 7?
Thank you,
MikeNew for 2000
"Mike Hildner" <mhildner@.afweb.com> wrote in message
news:ORKfTeV$DHA.3804@.TK2MSFTNGP09.phx.gbl...
> Is cascade delete and cascade update new to 2000 or did it exist in 7?
> Thank you,
> Mike
>|||Thank you.
"Scott Morris" <bogus@.bogus.com> wrote in message
news:%23Jc8kqV$DHA.712@.tk2msftngp13.phx.gbl...
> New for 2000
> "Mike Hildner" <mhildner@.afweb.com> wrote in message
> news:ORKfTeV$DHA.3804@.TK2MSFTNGP09.phx.gbl...
> > Is cascade delete and cascade update new to 2000 or did it exist in 7?
> >
> > Thank you,
> > Mike
> >
> >
>

Quick Method to delete from Two Tables

I have two tables ( a & b ) Both are linked by a ledgerref field. table what
would be the quickest and easiest way to delete records from both when
a.textStatus = 1the only way.. the usual way
delete from b from a,b
where b.ledgerref = a.ledgerref
and a.textStatus = 1
delete from a where textStatus = 1
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||and of course enclose it with a transaction :)
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||If the two tables are PK-FK linked, you could use CASCADE DELETE.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:1523DBF8-B27F-4351-970C-AF93A4316705@.microsoft.com...
>I have two tables ( a & b ) Both are linked by a ledgerref field. table
>what
> would be the quickest and easiest way to delete records from both when
> a.textStatus = 1|||Or getting it out of dialect, and correcting the "textStatus" data
element name (test and status are both suffixes to an attribute in
ISO-11179). I will not comment on the practice of using flags in SQL
to mimic an assembly language programming, or redundant tables to mimic
scratch tapes.
DELETE FROM Beta
WHERE EXISTS
(SELECT *
FROM Alpha
WHERE Beta.ledger_ref = Alpha.ledger_ref
AND Alpha.foobar_status = 1);
DRI action would be better. The best solution would be a proper
relational design.

Queue DELETE time

Hello!

In running some performance tests on a Queue using a message size of ~5KB, we found that we can process (SEND and RECEIVE) on the order of 600 - 800 messages / second. However, we have found that INSERTs of new messages to the Queue appear to take great precedence over DELETEs of received messages from the queue. In particular, we found that during heavy use the total size of the Queue (as determined using the sp_spaceused procedure) equals about the number of total messages processed, not the number of messages on the queue.

When we stop sending messages, the overall size of the Queue table appears to decrease slowly, so there is a background process that is obviously doing some work there to clean up the received messages from the Queue. What I would like to know is if we can affect that background process in any way so that the messages are cleared out more quickly. The performance has been determined to suffer appreciably once the Queue size grows to greater than about 3GB in size. We also notice timeouts on the RECEIVE statements when the Queue size is that large.

Thanks for any help --

Robert

There is no background deletion from queues.
If retention is OFF, the RECEIVE is a DELETE WITH OUTPUT and messages are deleted immedeately.
If retention is ON, RECEIVE is an UPDATE WITH OUTPUT and the messages are deleted when the END CONVERSATION is run.

HTH,
~ Remus

|||

Remus --

Thanks for the reply. What we have noticed is that if we SEND a lot of messages in a short period of time, the overall size of the Queue will grow regardless of the actual number of messages on the Queue. Maybe what it is is actually a background shrinking of the TABLE; I don't know. I did find that if the throughput was ~300 messages / sec or less, the shrinking kept up with the INSERTs and the size of the Queue did not increase.

Is there no way, then, to affect the shrinking of the Queue?

Thanks --

Robert

|||

Doesn't this means that your sending faster than the attached procedure can process? I have a blog on how to process faster at http://blogs.msdn.com/remusrusanu/archive/2006/10/14/writing-service-broker-procedures.aspx , but as a general rule, you will always be able to send faster than the service can process. You must tune your system to be able to process as many messages as you need as average over some period of time. You do not need to be able to keep up with spikes of messages, they can queue up and be processed later, but you need to be able to keep up with the incomming rate over time.

|||

Remus Rusanu wrote:

Doesn't this means that your sending faster than the attached procedure can process?

I don't know if I would call this SENDing faster than being able to process the messages. As a general rule, the number of items on the Queue (as told by the ROWS column from sp_spaceused) doesn't appear to grow that large.

Here is an example: we would SEND 5KB messages at a rate of ~600 - 800 / sec. We were able to process the messages at about the same rate, give or take a little. Over the course of half an hour, the ROWS column said that there were maybe 10000 items on the Queue, but the RESERVED column would show a TABLE size of ~5GB. Obviously, the size of 10000 5KB messages is not 5GB, but the total number of items processed (~1.1 million) * 5KB is ~5GB.

What that indicates to me is that either the process that DELETEs the rows from the TABLE or the process that shrinks the TABLE after items are DELETEd operates more slowly or less frequently than the process that INSERTs the rows into the TABLE. If I throttle the SEND process down to ~300 messages / sec, it appears that the two processes -- growing to add the new messages and shrinking after delivering the received messages -- are close to equilibrium.

In talking through this, I guess we've more or less answered the question, which was whether I could make the TABLE shrink occur any more quickly -- and the answer is no, we need to tune the system for the greatest throughput. Thanks very much for your time!

Robert

|||

Use this query to get the number of rows in the queue:

select p.rows

from sys.objects as o

join sys.partitions as p on p.object_id = o.object_id

join sys.objects as q on o.parent_object_id = q.object_id

where q.name = '<queuename>'

and p.index_id = 1

What you see is probably the ghost writer cleanning up pages in the database, has no relation with SSB per say, is the normal process you would see with any table that has a large number of inserts (message enqueues) followed by a large number of deleted (message dequeues). sys.dm_db_index_physical_stats (http://msdn2.microsoft.com/en-us/library/ms188917.aspx) shows the actual number of ghosted records and you may correlate this with your observations.

HTH,

~ Remus

Questions regarding Reporting Service SOAP

Hi,
I using reporting service SOAP in my application. I would like to know..

- what is the method for delete a report
- there is list children method for listing reports from certain folder but how to get list of catalog or list of folders from reporting service, but method to use.

Thanks and best regards!

Delete a report: DeleteItem( itempath )

List folders: Call ListChildren(parentpath) and filter the resulting CatalogItem collection for folders. There is no way to only return folders from ListChildren.

List catalog: This is not meaningful because any given Report Server has one and only one catalog.