shrinkdb_status

Syntax

 shrinkdb_status(database_name, query)   

Parameters

• database_name – is the name of the database you are checking.
• query – is one of:

  • in_progress – determines if a shrink database is in progress on this database. Returns a value of 0 for no, a value of 1 for yes.

  • owner_instance – determines which instance in a cluster is running a shrink operation. Returns:

    • 0 – if no shrink is in progress.
    • The owning instance ID – if an instance has a shrink operation running. For a nonclustered server, the "owning instance” is always 1.

  • au_total – returns the total number of allocation units (that is, groups of 256 pages) the shrink operation affects.

  • au_current – returns the total number of allocation units processed by the shrink operation.

  • pages_moved – returns the number of index or data pages moved during the current shrink operation. pages_moved does not include empty pages that were released during the shrink operation.

  • begin_date – the date and time the current shrink operation began, returned as an unsigned bigint.

  • end_date – returns the date and time the shrink operation ended. Returns 0 when the shrink operation is ongoing or completed but not waiting for a restart.
  • requested_end_date – returns the date and time the active shrink operation is requested to end.

  • time_move – returns the amount of time, in microseconds, spent moving pages. time_move includes the time spent updating page references to the moved pages, but does not include the time spent performing administrative tasks that happen at the end of individual move blocks.

  • time_repair – returns the amount of time, in microseconds, spent on administrative tasks for moving blocks. time_repair plus the value for time_move indicates the approximate amount of time Adaptive Server spent working on the current shrink operation.

  • last_error – returns the error the shrink operation encountered when it came to abnormal stop.

Examples

• Example 1 – checks the progress of the pubs2 database shrink operation:

  shrinkdb_status("pubs2", "in_progress")

• Example 2 – returns the amount of time Adaptive Server spent moving the pages of the pubs2 database:

  shrinkdb_status("pubs2", "time_move")

• Example 3 – returns the amount of time Adaptive Server spent shinking the pubs2 database:

  shrinkdb_status("pubs2", "time_move")

Usage

shrinkdb_status returns 0 if no shrink operations are currently running on the database.