Validating Current Thresholds

Use the check parameter to validate the current settings of various thresholds.

For instance, check warns you if multiple segments share the same set of devices and both segments are set for automatic expansion, or if the threshold currently set to trigger automatic expansion on the logsegment is too close to the current last-chance threshold for the logsegment. In this situation, the automatic threshold does not fire, and check reports a warning.

sp_dbextend includes a powerful simulation mode that any user with sa_role permission can use to simulate the execution of the top-level threshold action procedure.

To define expansion policies for the logsegment in the pubs2 database:

sp_dbextend ’set’, ’database’, pubs2, logsegment,’3M’
sp_dbextend ’set’, ’threshold’, pubs2, logsegment, ’1M’

To simulate expansion for these policies:

sp_dbextend ’simulate’, pubs2, logsegment
------------------------------

Messages from the server follow this input.

The following examples show the series of database and disk expansions that would occur if the threshold on database pubs2 segment logsegment fired once:

sp_dbextend 'simulate', pubs2, logsegment
---------------
NO REAL WORK WILL BE DONE. 
Simulate database / device expansion in a dry-run mode 1 time(s).
These are the series of database/device expansions that would have
happened if the threshold on database'pubs2', 
segment 'logsegment' were to fire 1 time(s).

Threshold fires: Iteration: 1.
=============================

Threshold action procedure 'sp_dbxt_extend_db' fired in db 'pubs2' on segment 'logsegment'.

Space left: 512 logical pages ('1M').ALTER DATABASE pubs2 log on pubs2_data = '3.0M' 
-- Segment: logsegmentDatabase 'pubs2' was altered by total size '3M' for
segment 'logsegment'.
Summary of device/database sizes after 1 simulated extensions: 
==================================================
devicename initial size final size 
---------- ------------ ---------- 
pubs2_data 20.0M        20.0M 

(1 row affected)

Database 'pubs2', segment 'logsegment' would be altered from an initial
size of '4M' by '3M' for a resultant total size of '7M'.

To actually expand the database manually for this threshold, issue:
sp_dbextend 'execute', 'pubs2','logsegment', '1'

(return status = 0)

To expand the database manually for this threshold, execute:

sp_dbextend ’execute’, ’pubs2’, ’logsegment’
--------------

This example shows that if the threshold fires at this level, an alter database command operates on the pubs2_data device for the logsegment:

sp_dbextend 'execute', pubs2, logsegmentThreshold fires: Iteration: 1.
================================
Threshold action procedure 'sp_dbxt_extend_db' fired in db 'pubs2' on
segment 'logsegment'. Space left: 512 logical pages ('1M').

ALTER DATABASE pubs2 log on pubs2_data = '3.0M' -- Segment: logsegment
Extending database by 1536 pages (3.0 megabytes) on disk pubs2_data
Warning: The database 'pubs2' is using an unsafe virtual device
'pubs2_data'. The recovery of this database can not be guaranteed.

Warning: Using ALTER DATABASE to extend the log segment will cause user
thresholds on the log segment within 128 pages of the last chance
threshold to be disabled.

Database 'pubs2' was altered by total size '3M' for segment 'logsegment'.

(return status = 0)

To simulate what would actually happen if the threshold fired <n> times in succession on a particular segment, issue the same command, specifying the number of iterations:
```
sp_dbextend ’simulate’, pubs2, logsegment, 5
-----------------
```
To expand this database five times, enter:
```
sp_dbextend ’execute’, ’pubs2’, ’logsegment’, 5
------------------
```
If you execute this command, you see in the output that firing the threshold five times in succession puts the database through a series of alter database operations, followed by one or more disk resize operations and, finally, an alter database on the specified device.