sp_iqversionuse procedure

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 on a multiplex server, run sp_iqversionuse to find out which versions are being used and the amount of space that can be recovered by releasing versions.

The procedure 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 always has MinKBRelease equal to MaxKBRelease.

WasReported indicates whether version usage information has been sent from the query server to the write server. WasReported is 0 initially on a write server for new versions. WasReported changes 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 10

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 releases 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 depends 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 11

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

call dbo.sp_iqversionuse