Chapter 2: Transact-SQL Functions

curunreservedpgs

Description

Returns the number of free pages in the specified disk piece.

Syntax

curunreservedpgs (dbid, lstart, unreservedpgs)

Parameters

dbid: is the ID for a database. These are stored in the db_id column of sysdatabases.

lstart: is a page within the disk piece for which pages are to be returned.

unreservedpgs: is the default value to return if the dbtable is presently unavailable for the requested database.

Examples

Example 1

Returns the database name, device name, and the number of unreserved pages for each device fragment

If a database is open, curunreservedpgs takes the value from memory. If it is not in use, the value is taken from the third parameter you specify in curunreservedpgs. In this example, the value comes from the unreservedpgs column in the sysusages table.

select db_name(dbid), d.name,
    curunreservedpgs(dbid, lstart, unreservedpgs)
    from sysusages u, sysdevices d
    where d.low <= u.size + vstart
      and d.high >= u.size + vstart -1
      and d.status &2 = 2

                                name
 ------------------------------ -------------------------- -----------
master                          master                            1634
tempdb                          master                             423
model                           master                             423
pubs2                           master                              72
sybsystemdb                     master                             399
sybsystemprocs                  master                            6577
sybsyntax                       master                             359

(7 rows affected)

Example 2

Displays the number of free pages on the segment for dbid starting on sysusages.lstart:

select curunreservedpgs (dbid, sysusages.lstart, 0)

Usage

curunreservedpgs, a system function, returns the number of free pages in a disk piece. For general information about system functions, see “System functions”.
If a database is open, the value returned by curunreservedpgs is taken from memory. If it is not in use, the value is taken from the third parameter you specify in curunreservedpgs.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

Any user can execute curunreservedpgs.