sp_iqversionuse procedure

The procedure produces a row for each user of a version. Run sp_iqversionuse first on the coordinator 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 coordinator. Version usage due to secondary servers is displayed as the secondary server name with connection ID 0.

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

Example 1

In this example, the oldest version 42648 is in use by connection 108 on the coordinator (mpxw). Committing or rolling back the transaction on connection 108 releases 7.9MB of space. Version 42686 is in use by secondary server (mpxq) according to output from the coordinator. Using the secondary 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 coordinator because these are new versions that have not yet been replicated by SQL Remote. Since version 42728 does not appear on the secondary server output, it has not yet been used by the secondary server.

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

call dbo.sp_iqversionuse

Example 2

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

call dbo.sp_iqversionuse

DBA authority required. Users without DBA authority must be granted EXECUTE permission in order to run the stored procedure.