Question No: 12
You are the administrator of Microsoft Windows 2000 Advanced Server computer. The server is also
running SQL Server 2000 and contains a database named Sales. The server is configured as shown in
the exhibit.
The sales database is configured by using the default options and increases by 50MB per day. You
want an operator to be notified automatically before the database runs out of disk space. You also
want to minimize the amount of administrative overhead necessary for this database.
What should you do?
A. Configure systems monitor to log an alert if the hard disk database contains less than 600MB of disk
space. Configure SQL server agent to send an e-mail message notifying the operator of the system
monitor alert.
B. Create a SQL server agent job that uses the sp_spaceused stored procedure each morning. Place the
results of the stored procedure in the e-mail message and use SQL mail to send the results to the
operator.
C. Set the Maxsize property of sales_data.mdf to 7.5GB and the maxsize property of sales_log.idf to
1.5GB. Configure SQL server agent to send an e-mail message to an operator when the data file and
the transaction log have reached these limits.
D. Use Microsoft Windows explorer to verify the available space on the hard disk each morning.
Execute the sp_spaceused stored procedure each morning.

Answer: A.
Explanation: The solution with least administrative effort is to set up a system monitor alert to log when
disk space is below a certain threshold, here 600MB. Then configure the SQL Server agent to trap this event
and send an e-mail notifying the operator when this occurs.

Note: Using SQL Server Agent, you can define alerts and configure responses to alerts.
SQL Server Agent monitors the Windows application log and compares each SQL Server event logged with
the alerts that have been defined. If a match is found, an event alert fires. SQL Server Agent can also
monitor specific SQL Server performance object counters and fire a performance condition alert when the value for the counter is less than, equal to, or greater than a defined threshold. The Create Alert Wizard can
be used to configure the SQL Server agent for alerts.

Incorrect Answers:
B: Getting the size of the database in an e-mail every morning would require some effort of the
operators – they would have to read the e-mail. It would be better just to get an e-mail when disk
space is starting to run out.
Note: The sp_spaceused command displays the number of rows, disk space reserved, and disk space
used by a table in the current database, or displays the disk space reserved and used by the entire
database.
C: We would like to have an alert before, not when, the database runs out of space. Setting the Maxsize
property would not be a good idea when the database increases in size every day.
D: Manually checking the available disk space each morning would require effort, time and cost. It’s
not solution with minimized administrative effort.

============================================================
Дан правильный ответ - A. Подскажите, как это реализовать. Я не нашел в Perfomance Monitor счетчиков для свободного дискового пространства.