Set - 5

Question 11 :

What does @@options return?

Answer :

The current SET options for the connection.
The @@options variable returns a list of the options set for the current connection. This is returned as integer and each bit in the integer represents an option.

Question 12 :

You have setup forwarding for your SQL Server 2005 Service Broker queues to another server. You edit a route to move a queue to a new server. What do you need to do now to ensure you can recover the routes in the event of a disaster?

Answer :

Back up the msdb database.
The routes for forwarding are stored in msdb.sys.routes. If you back up the msdb database, the routes will be captured.

Question 13 :

You are looking to setup a large Notification Services application and you are planning on a 40GB database. When installing Notification Services, the creation fails. What is the likely problem?

Answer :

Notification Services installation has a 10 minute timeout and the database creation probably took longer than that.
The Notification Services timeout for database creation is 10 minutes. If the database takes longer to create, the installation of the instance fails. You need to reduce the size of the database for installation and then increase it when you are done.

Question 14 :

You have enabled AWE for your SQL Server 2005 server and allocated 4GB or RAM on one of your servers. An ASP.NET application on the same server is feeling memory pressure and you want to release some memory and reduce SQL Server to 3GB or RAM. How can you do this?

Answer :

Change the max amount of memory allocated and shut down and restart SQL Server to have it take affect.
Once memory is allocated through AWE, it cannot be released unless the SQL Server is restarted.

Question 15 :

You are troubleshooting a Service Broker application and find that messages are remaining on the queue. You determine that there is no active queue monitor. What should you do?

Answer :

Use the ALTER QUEUE statement to turn activation on.
One of the troubleshooting steps if messages remain in the queue is to check for an active queue monitor. If one is not on, then it needs to be activated. The ALTER QUEUE statement is used to change this.