Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Finding the number of SQL Licenses in use

Licensing

Finding the number of SQL Licenses in use

by  mrdenny  Posted    (Edited  )
[color red]This relates to Per Device CALs and Per User CALs only.[/color]

There is no easy way within Microsoft SQL Server to find the number of CALs that are currently in use. What I will attempt to provide here is a fairly easy way to get a high watermark of the number of CALs that are being used by the system based on the number of people who are currently logged in. If you are using connection pooling or some sort of middleware this will not work. (If you are using connection pooling or some sort of middleware you probably should be using processor licensing anyway. See faq962-5153 for more information on selecting a licensing model.)

First Create a table in the master database, or any database you wish. I'm using master for this example.
Code: 
create table _LicenseCheck
(SampleTaken datetime,
LicensesUsed int)
Now schedule one of these queries depending on if you are using User CALs or Device CALs.
Code: 
/*This code is for Device CALs*/
insert into _LicenseCheck
select getdate(), count(distinct hostname)
from sysprocesses
Code: 
/*This code is for User CALs*/
insert into _LicenseCheck
select getdate(), count(distinct Loginame)
from sysprocesses
You will want to schedule this query to run every couple of minutes through out the day for a week or so.

In order to find the high watermark simply query the table.
Code: 
select *
from _LicenseCheck
where LicensesUsed = (select max(LicensesUsed) from _LicenseCheck)
Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top