How to define max session limit to individual sql users?

Question

Post reply

How to define max session limit to individual sql users?

cenkg

SSC-Addicted

Points: 475
More actions
April 4, 2011 at 4:13 am

#236193

Hi All,
We need to define max. session limit to individual sql users to prevent db stuck by lots of requests. This does not mean to limit concurrent sessions as some of the users (admin users and some app users etc...) have to be connected and should not be limited @ any time. With this we will have a chance to control number of sessions then it will help us to improve sql performance for the must users. Is it possible to do this with sql2005 or should we need to upgrade our system to sql2008?
Thanks to all
Cenk

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

C.K.Shaiju SSCarpal Tunnel Points: 4228 More actions · Answer 1

Please check the following link for Resource Governor.

http://www.sqlservercentral.com/articles/Resource+Governor/64034/

_____________________________________________
One ounce of practice is more important than tonnes of dreams

cenkg SSC-Addicted Points: 475 More actions · Answer 2

Hi Shaiju,

This would help us but i need to know is there any way to limit number of sessions by per user? For instance i want to give max. 2 sessions for some users if it is possible with sql2008.

Thanks

C.K.Shaiju SSCarpal Tunnel Points: 4228 More actions · Answer 3

Ok. 🙁

In that article there is a sentence "It allows the user to limit the CPU and memory usage by a specific application or user. It would help in preventing any particular user / application to eat up all the resources of the SQL Server.".

So I thought it will fulfill your requirement.

_____________________________________________
One ounce of practice is more important than tonnes of dreams

cenkg SSC-Addicted Points: 475 More actions · Answer 4

cenkg

SSC-Addicted

Points: 475

April 7, 2011 at 7:46 am

#1308529

Hi Shaiju,

Thanks for your help.

Cenk

HowardW One Orange Chip Points: 29109 More actions · Answer 5

Please note that Resource Governor is an Enterprise Edition feature only and not available on other Editions (with the exception of Developer Edition)

cenkg SSC-Addicted Points: 475 More actions · Answer 6

Hi Howard,

Thanks for the info. We will consider this point also.

Cenk

UMG Developer SSChampion Points: 13482 More actions · Answer 7

You could probably write a TRIGGER that checked how many sessions a user currently had, and ROLLBACK the current conection if they were over your limit.

cenkg SSC-Addicted Points: 475 More actions · Answer 8

Hi,

How and where can i create a trigger to check number of session that user have currently? As we know that trigger can be created into a table. So my question is, is there any table or field which stores such information like number of session?

Thanks in advance

C.K.Shaiju SSCarpal Tunnel Points: 4228 More actions · Answer 9

UMG might be saying about Logon "DDL" Trigger.

_____________________________________________
One ounce of practice is more important than tonnes of dreams

cenkg SSC-Addicted Points: 475 More actions · Answer 10

Hi Shaiju,

Sorry but i am not an sql admin. I am passing all the information to our sql team. Could you please elaborate what does UMG and DDL mean?

Thanks for your understanding.

Cenk

HowardW One Orange Chip Points: 29109 More actions · Answer 11

He's referring to a logon trigger not a DDL trigger.

Have a look here, which also has a handy example of exactly what you're wanting to do - please use caution if using logon triggers as a poorly implemented one can deny logon entirely (requiring you to use the dedicated administrator connection to fix the problem):

http://technet.microsoft.com/en-us/library/bb326598.aspx

C.K.Shaiju SSCarpal Tunnel Points: 4228 More actions · Answer 12

UMG is UMG Developer. User name of the previous post. DDL is Data Definition Language. HowardW corrected my mistake in the trigger type. Thanks HowardW.

_____________________________________________
One ounce of practice is more important than tonnes of dreams

cenkg SSC-Addicted Points: 475 More actions · Answer 13

cenkg

SSC-Addicted

Points: 475

April 8, 2011 at 2:34 am

#1309012

Thanks guys. Highly appreciated.

Chuck Hottle SSCarpal Tunnel Points: 4777 More actions · Answer 14

I had to do something similar to limit the number of open sessions, based on last batch data, for a particular application due to licensing limitations. I just wrote a query that checked for sessions specifically for that application that had a last batch older than the defined limit and killed the old ones. That ran every 30 minutes in a job.