sp_iqversionuse procedure added [CR 401646]

Displays version usage for the IQ Main store.

call dbo.sp_iqversionuse ( )

The sp_iqversionuse system stored procedure helps troubleshoot situations where the databases uses excessive storage space due to multiple table versions.

If out-of-space conditions occur or sp_iqstatus shows a high percentage of main blocks in use, run sp_iqversionuse to find out which versions are being used and the amount of space that can be recovered by releasing versions.

Produces a row for each user of a version. Run sp_iqversionuse first on the write server to determine which versions should be released and the amount of space in KB to be released when the version is no longer in use. Connection IDs are displayed in the IQConn column for users connected to the write server. Version usage due to query servers is displayed as the query server name with connection ID 0.

Run sp_iqversionuse on multiplex query servers to determine individual connections to query servers. Users from other servers are not displayed on a query server.

The amount of space is expressed as a range because the actual amount typically depends on which other versions are released. The actual amount of space released can be anywhere between the values of MinKBRelease and MaxKBRelease. The oldest version will always have MinKBRelease equal to MaxKBRelease.

WasReported indicates if version usage information has been has been sent from the query server to the write server. WasReported will be 0 initially on a write server for new versions. WasReported will change to 1 once SQL Remote replicates version usage information back to the write server. If WasReported is 0 for an extended period, SQL Remote might be stopped.

Example 1

In this example, the oldest version 42648 is in use by connection 108 on the write server (mpxw). Committing or rolling back the transaction on connection 108 will release 7.9MB of space. Version 42686 is in use by query server (mpxq) according to output from the write server. Using the query server output, the actual connection is connection 31. The actual amount of space returned from releasing version 42686 will depend on whether 42648 is released first.

WasReported is 0 for versions 42715 and 42728 on the write server because these are new versions that have not yet been replicated by SQL Remote. Since version 42728 does not appear on the query server output, it has not yet been used by the query server.

The following output is returned when sp_iqversionuse executes on the write server mpxw:

call dbo.sp_iqversionuse

Example 2

The following output is returned when sp_iqversionuse executes on the query server (mpxq):

call dbo.sp_iqversionuse