We are currently using Great Plains. This accounting package runs on SQL Server and we have performance problems running integration manager. The result is that users can not get work done when integration manager is running.
Is there any way I can limit the user running integration manager to a certain amount of CPU time and allocate the rest to the other users?
In Oracle, this is easy - I could just use resource plans, but I don't know if there is a way to do this with SQL Server 2K.
TIA
No, SQL Server does not support the ability to limit the amount of =
resources an application or a connections uses.
--=20
Keith
"ramick" <anonymous@.discussions.microsoft.com> wrote in message =
news:9327DE01-32DA-47B7-917F-E2513C31CB5C@.microsoft.com...
> We are currently using Great Plains. This accounting package runs on =
SQL Server and we have performance problems running integration manager. =
The result is that users can not get work done when integration manager =
is running.
>=20
> Is there any way I can limit the user running integration manager to a =
certain amount of CPU time and allocate the rest to the other users?
>=20
> In Oracle, this is easy - I could just use resource plans, but I don't =
know if there is a way to do this with SQL Server 2K.
>=20
> TIA
Showing posts with label memory. Show all posts
Showing posts with label memory. Show all posts
Friday, March 9, 2012
Limiting a users resource ie CPU, disk and/or memory
We are currently using Great Plains. This accounting package runs on SQL Se
rver and we have performance problems running integration manager. The resu
lt is that users can not get work done when integration manager is running.
Is there any way I can limit the user running integration manager to a certa
in amount of CPU time and allocate the rest to the other users?
In Oracle, this is easy - I could just use resource plans, but I don't know
if there is a way to do this with SQL Server 2K.
TIANo, SQL Server does not support the ability to limit the amount of =
resources an application or a connections uses.
--=20
Keith
"ramick" <anonymous@.discussions.microsoft.com> wrote in message =
news:9327DE01-32DA-47B7-917F-E2513C31CB5C@.microsoft.com...
> We are currently using Great Plains. This accounting package runs on =
SQL Server and we have performance problems running integration manager. =
The result is that users can not get work done when integration manager =
is running.
>=20
> Is there any way I can limit the user running integration manager to a =
certain amount of CPU time and allocate the rest to the other users?
>=20
> In Oracle, this is easy - I could just use resource plans, but I don't =
know if there is a way to do this with SQL Server 2K.
>=20
> TIA
rver and we have performance problems running integration manager. The resu
lt is that users can not get work done when integration manager is running.
Is there any way I can limit the user running integration manager to a certa
in amount of CPU time and allocate the rest to the other users?
In Oracle, this is easy - I could just use resource plans, but I don't know
if there is a way to do this with SQL Server 2K.
TIANo, SQL Server does not support the ability to limit the amount of =
resources an application or a connections uses.
--=20
Keith
"ramick" <anonymous@.discussions.microsoft.com> wrote in message =
news:9327DE01-32DA-47B7-917F-E2513C31CB5C@.microsoft.com...
> We are currently using Great Plains. This accounting package runs on =
SQL Server and we have performance problems running integration manager. =
The result is that users can not get work done when integration manager =
is running.
>=20
> Is there any way I can limit the user running integration manager to a =
certain amount of CPU time and allocate the rest to the other users?
>=20
> In Oracle, this is easy - I could just use resource plans, but I don't =
know if there is a way to do this with SQL Server 2K.
>=20
> TIA
Limiting a users resource ie CPU, disk and/or memory
We are currently using Great Plains. This accounting package runs on SQL Server and we have performance problems running integration manager. The result is that users can not get work done when integration manager is running
Is there any way I can limit the user running integration manager to a certain amount of CPU time and allocate the rest to the other users
In Oracle, this is easy - I could just use resource plans, but I don't know if there is a way to do this with SQL Server 2K
TIANo, SQL Server does not support the ability to limit the amount of =resources an application or a connections uses.
-- Keith
"ramick" <anonymous@.discussions.microsoft.com> wrote in message =news:9327DE01-32DA-47B7-917F-E2513C31CB5C@.microsoft.com...
> We are currently using Great Plains. This accounting package runs on =SQL Server and we have performance problems running integration manager. = The result is that users can not get work done when integration manager =is running.
> > Is there any way I can limit the user running integration manager to a =certain amount of CPU time and allocate the rest to the other users?
> > In Oracle, this is easy - I could just use resource plans, but I don't =know if there is a way to do this with SQL Server 2K.
> > TIA
Is there any way I can limit the user running integration manager to a certain amount of CPU time and allocate the rest to the other users
In Oracle, this is easy - I could just use resource plans, but I don't know if there is a way to do this with SQL Server 2K
TIANo, SQL Server does not support the ability to limit the amount of =resources an application or a connections uses.
-- Keith
"ramick" <anonymous@.discussions.microsoft.com> wrote in message =news:9327DE01-32DA-47B7-917F-E2513C31CB5C@.microsoft.com...
> We are currently using Great Plains. This accounting package runs on =SQL Server and we have performance problems running integration manager. = The result is that users can not get work done when integration manager =is running.
> > Is there any way I can limit the user running integration manager to a =certain amount of CPU time and allocate the rest to the other users?
> > In Oracle, this is easy - I could just use resource plans, but I don't =know if there is a way to do this with SQL Server 2K.
> > TIA
Monday, February 20, 2012
limit the CPU usage
Hi all,
I know we can set limit to the memory used by SQL Server, but are there
any way to force only 30% of total CPU will used by the SQL server. ?
Thanks alot.You can specify how many processors to use, on a multi-proc box, but you
cannot specify the % usage.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Rena" <rena@.mail.hongkong.com> wrote in message
news:OIoOUJEjEHA.1344@.TK2MSFTNGP11.phx.gbl...
Hi all,
I know we can set limit to the memory used by SQL Server, but are there
any way to force only 30% of total CPU will used by the SQL server. ?
Thanks alot.|||That's not possible. You can only set the SQL Server CPU affinity on
multiprocessor systems i.e. which CPU's SQL Server can use.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Use MiniSQLBackup Lite, free!
"Rena" <rena@.mail.hongkong.com> wrote in message
news:OIoOUJEjEHA.1344@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> I know we can set limit to the memory used by SQL Server, but are
there
> any way to force only 30% of total CPU will used by the SQL server. ?
> Thanks alot.
>|||if you were running Windows Server 2003 , you could in theory use WSRM to
limit the processor util by SQLServr.exe as per link below. Pretty cool, you
can even have time based policies that change the percentage depending the
time of day - ie increase the percentage over night when batch processing.
Would do some research on supportability with SQL Server / best practices
before going for it and test test test !
http://www.microsoft.com/windowsser...loads/wsrm.mspx
"Rena" wrote:
> Hi all,
> I know we can set limit to the memory used by SQL Server, but are ther
e
> any way to force only 30% of total CPU will used by the SQL server. ?
> Thanks alot.
>
>|||Hi Rena,
It is not possible, with standard tools, to dictate the amount of CPU time
for SQLServer, but there is a Win2000 Server parameter, 'priority boost',
which dictates which process has priority over the CPU.
SQLServer runs at normal priority on Win2000 Server (priority base 7). This
can be boosted to priority base 13 by setting 'priority boost' option to 1
from the default of 0 using sp_configure. This should only be done if the
Win2000 Server is dedicated to SQLServer or there are more than one CPU.
Reducing the amount of CPU time for SQLServer is normally not necessary nor
desirable. If the CPU is running at or near 100% for consciderable periods
and this time is used by SQLServer, you could try boosting the priority for
other processes or conscider whether there exists a bottleneck.
Hope it helps.
James.
"Rena" <rena@.mail.hongkong.com> wrote in message
news:OIoOUJEjEHA.1344@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> I know we can set limit to the memory used by SQL Server, but are
there
> any way to force only 30% of total CPU will used by the SQL server. ?
> Thanks alot.
>
I know we can set limit to the memory used by SQL Server, but are there
any way to force only 30% of total CPU will used by the SQL server. ?
Thanks alot.You can specify how many processors to use, on a multi-proc box, but you
cannot specify the % usage.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Rena" <rena@.mail.hongkong.com> wrote in message
news:OIoOUJEjEHA.1344@.TK2MSFTNGP11.phx.gbl...
Hi all,
I know we can set limit to the memory used by SQL Server, but are there
any way to force only 30% of total CPU will used by the SQL server. ?
Thanks alot.|||That's not possible. You can only set the SQL Server CPU affinity on
multiprocessor systems i.e. which CPU's SQL Server can use.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Use MiniSQLBackup Lite, free!
"Rena" <rena@.mail.hongkong.com> wrote in message
news:OIoOUJEjEHA.1344@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> I know we can set limit to the memory used by SQL Server, but are
there
> any way to force only 30% of total CPU will used by the SQL server. ?
> Thanks alot.
>|||if you were running Windows Server 2003 , you could in theory use WSRM to
limit the processor util by SQLServr.exe as per link below. Pretty cool, you
can even have time based policies that change the percentage depending the
time of day - ie increase the percentage over night when batch processing.
Would do some research on supportability with SQL Server / best practices
before going for it and test test test !
http://www.microsoft.com/windowsser...loads/wsrm.mspx
"Rena" wrote:
> Hi all,
> I know we can set limit to the memory used by SQL Server, but are ther
e
> any way to force only 30% of total CPU will used by the SQL server. ?
> Thanks alot.
>
>|||Hi Rena,
It is not possible, with standard tools, to dictate the amount of CPU time
for SQLServer, but there is a Win2000 Server parameter, 'priority boost',
which dictates which process has priority over the CPU.
SQLServer runs at normal priority on Win2000 Server (priority base 7). This
can be boosted to priority base 13 by setting 'priority boost' option to 1
from the default of 0 using sp_configure. This should only be done if the
Win2000 Server is dedicated to SQLServer or there are more than one CPU.
Reducing the amount of CPU time for SQLServer is normally not necessary nor
desirable. If the CPU is running at or near 100% for consciderable periods
and this time is used by SQLServer, you could try boosting the priority for
other processes or conscider whether there exists a bottleneck.
Hope it helps.
James.
"Rena" <rena@.mail.hongkong.com> wrote in message
news:OIoOUJEjEHA.1344@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> I know we can set limit to the memory used by SQL Server, but are
there
> any way to force only 30% of total CPU will used by the SQL server. ?
> Thanks alot.
>
limit the CPU usage
Hi all,
I know we can set limit to the memory used by SQL Server, but are there
any way to force only 30% of total CPU will used by the SQL server. ?
Thanks alot.
You can specify how many processors to use, on a multi-proc box, but you
cannot specify the % usage.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Rena" <rena@.mail.hongkong.com> wrote in message
news:OIoOUJEjEHA.1344@.TK2MSFTNGP11.phx.gbl...
Hi all,
I know we can set limit to the memory used by SQL Server, but are there
any way to force only 30% of total CPU will used by the SQL server. ?
Thanks alot.
|||That's not possible. You can only set the SQL Server CPU affinity on
multiprocessor systems i.e. which CPU's SQL Server can use.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Use MiniSQLBackup Lite, free!
"Rena" <rena@.mail.hongkong.com> wrote in message
news:OIoOUJEjEHA.1344@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> I know we can set limit to the memory used by SQL Server, but are
there
> any way to force only 30% of total CPU will used by the SQL server. ?
> Thanks alot.
>
|||if you were running Windows Server 2003 , you could in theory use WSRM to
limit the processor util by SQLServr.exe as per link below. Pretty cool, you
can even have time based policies that change the percentage depending the
time of day - ie increase the percentage over night when batch processing.
Would do some research on supportability with SQL Server / best practices
before going for it and test test test !
http://www.microsoft.com/windowsserv...oads/wsrm.mspx
"Rena" wrote:
> Hi all,
> I know we can set limit to the memory used by SQL Server, but are there
> any way to force only 30% of total CPU will used by the SQL server. ?
> Thanks alot.
>
>
|||Hi Rena,
It is not possible, with standard tools, to dictate the amount of CPU time
for SQLServer, but there is a Win2000 Server parameter, 'priority boost',
which dictates which process has priority over the CPU.
SQLServer runs at normal priority on Win2000 Server (priority base 7). This
can be boosted to priority base 13 by setting 'priority boost' option to 1
from the default of 0 using sp_configure. This should only be done if the
Win2000 Server is dedicated to SQLServer or there are more than one CPU.
Reducing the amount of CPU time for SQLServer is normally not necessary nor
desirable. If the CPU is running at or near 100% for consciderable periods
and this time is used by SQLServer, you could try boosting the priority for
other processes or conscider whether there exists a bottleneck.
Hope it helps.
James.
"Rena" <rena@.mail.hongkong.com> wrote in message
news:OIoOUJEjEHA.1344@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> I know we can set limit to the memory used by SQL Server, but are
there
> any way to force only 30% of total CPU will used by the SQL server. ?
> Thanks alot.
>
I know we can set limit to the memory used by SQL Server, but are there
any way to force only 30% of total CPU will used by the SQL server. ?
Thanks alot.
You can specify how many processors to use, on a multi-proc box, but you
cannot specify the % usage.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Rena" <rena@.mail.hongkong.com> wrote in message
news:OIoOUJEjEHA.1344@.TK2MSFTNGP11.phx.gbl...
Hi all,
I know we can set limit to the memory used by SQL Server, but are there
any way to force only 30% of total CPU will used by the SQL server. ?
Thanks alot.
|||That's not possible. You can only set the SQL Server CPU affinity on
multiprocessor systems i.e. which CPU's SQL Server can use.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Use MiniSQLBackup Lite, free!
"Rena" <rena@.mail.hongkong.com> wrote in message
news:OIoOUJEjEHA.1344@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> I know we can set limit to the memory used by SQL Server, but are
there
> any way to force only 30% of total CPU will used by the SQL server. ?
> Thanks alot.
>
|||if you were running Windows Server 2003 , you could in theory use WSRM to
limit the processor util by SQLServr.exe as per link below. Pretty cool, you
can even have time based policies that change the percentage depending the
time of day - ie increase the percentage over night when batch processing.
Would do some research on supportability with SQL Server / best practices
before going for it and test test test !
http://www.microsoft.com/windowsserv...oads/wsrm.mspx
"Rena" wrote:
> Hi all,
> I know we can set limit to the memory used by SQL Server, but are there
> any way to force only 30% of total CPU will used by the SQL server. ?
> Thanks alot.
>
>
|||Hi Rena,
It is not possible, with standard tools, to dictate the amount of CPU time
for SQLServer, but there is a Win2000 Server parameter, 'priority boost',
which dictates which process has priority over the CPU.
SQLServer runs at normal priority on Win2000 Server (priority base 7). This
can be boosted to priority base 13 by setting 'priority boost' option to 1
from the default of 0 using sp_configure. This should only be done if the
Win2000 Server is dedicated to SQLServer or there are more than one CPU.
Reducing the amount of CPU time for SQLServer is normally not necessary nor
desirable. If the CPU is running at or near 100% for consciderable periods
and this time is used by SQLServer, you could try boosting the priority for
other processes or conscider whether there exists a bottleneck.
Hope it helps.
James.
"Rena" <rena@.mail.hongkong.com> wrote in message
news:OIoOUJEjEHA.1344@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> I know we can set limit to the memory used by SQL Server, but are
there
> any way to force only 30% of total CPU will used by the SQL server. ?
> Thanks alot.
>
limit the CPU usage
Hi all,
I know we can set limit to the memory used by SQL Server, but are there
any way to force only 30% of total CPU will used by the SQL server. ?
Thanks alot.You can specify how many processors to use, on a multi-proc box, but you
cannot specify the % usage.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Rena" <rena@.mail.hongkong.com> wrote in message
news:OIoOUJEjEHA.1344@.TK2MSFTNGP11.phx.gbl...
Hi all,
I know we can set limit to the memory used by SQL Server, but are there
any way to force only 30% of total CPU will used by the SQL server. ?
Thanks alot.|||That's not possible. You can only set the SQL Server CPU affinity on
multiprocessor systems i.e. which CPU's SQL Server can use.
--
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Use MiniSQLBackup Lite, free!
"Rena" <rena@.mail.hongkong.com> wrote in message
news:OIoOUJEjEHA.1344@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> I know we can set limit to the memory used by SQL Server, but are
there
> any way to force only 30% of total CPU will used by the SQL server. ?
> Thanks alot.
>|||Hi Rena,
It is not possible, with standard tools, to dictate the amount of CPU time
for SQLServer, but there is a Win2000 Server parameter, 'priority boost',
which dictates which process has priority over the CPU.
SQLServer runs at normal priority on Win2000 Server (priority base 7). This
can be boosted to priority base 13 by setting 'priority boost' option to 1
from the default of 0 using sp_configure. This should only be done if the
Win2000 Server is dedicated to SQLServer or there are more than one CPU.
Reducing the amount of CPU time for SQLServer is normally not necessary nor
desirable. If the CPU is running at or near 100% for consciderable periods
and this time is used by SQLServer, you could try boosting the priority for
other processes or conscider whether there exists a bottleneck.
Hope it helps.
James.
"Rena" <rena@.mail.hongkong.com> wrote in message
news:OIoOUJEjEHA.1344@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> I know we can set limit to the memory used by SQL Server, but are
there
> any way to force only 30% of total CPU will used by the SQL server. ?
> Thanks alot.
>
I know we can set limit to the memory used by SQL Server, but are there
any way to force only 30% of total CPU will used by the SQL server. ?
Thanks alot.You can specify how many processors to use, on a multi-proc box, but you
cannot specify the % usage.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Rena" <rena@.mail.hongkong.com> wrote in message
news:OIoOUJEjEHA.1344@.TK2MSFTNGP11.phx.gbl...
Hi all,
I know we can set limit to the memory used by SQL Server, but are there
any way to force only 30% of total CPU will used by the SQL server. ?
Thanks alot.|||That's not possible. You can only set the SQL Server CPU affinity on
multiprocessor systems i.e. which CPU's SQL Server can use.
--
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Use MiniSQLBackup Lite, free!
"Rena" <rena@.mail.hongkong.com> wrote in message
news:OIoOUJEjEHA.1344@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> I know we can set limit to the memory used by SQL Server, but are
there
> any way to force only 30% of total CPU will used by the SQL server. ?
> Thanks alot.
>|||Hi Rena,
It is not possible, with standard tools, to dictate the amount of CPU time
for SQLServer, but there is a Win2000 Server parameter, 'priority boost',
which dictates which process has priority over the CPU.
SQLServer runs at normal priority on Win2000 Server (priority base 7). This
can be boosted to priority base 13 by setting 'priority boost' option to 1
from the default of 0 using sp_configure. This should only be done if the
Win2000 Server is dedicated to SQLServer or there are more than one CPU.
Reducing the amount of CPU time for SQLServer is normally not necessary nor
desirable. If the CPU is running at or near 100% for consciderable periods
and this time is used by SQLServer, you could try boosting the priority for
other processes or conscider whether there exists a bottleneck.
Hope it helps.
James.
"Rena" <rena@.mail.hongkong.com> wrote in message
news:OIoOUJEjEHA.1344@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> I know we can set limit to the memory used by SQL Server, but are
there
> any way to force only 30% of total CPU will used by the SQL server. ?
> Thanks alot.
>
Limit SQLEXPRESS Memory?
Is there some way to limit the amount of memmory grabbed by SQLEXPRESS as there is with SQL 2000? TaskManager shows it taking 1.4 gig on my server. 2 gig total memory in the machine. I'd like to limit it to someting less.
This statement was captured from a trace when i lowered the max memory to 123MB for my development box which is running SQL2K.
exec sp_configure N'max server memory (MB)', 123
I would suspect it to behave similarly on sql express.
|||Thanks but that produces an error of
The configuration option 'max server memory (MB)' does not exist, or it may be an advanced option.
Any other ideas?
|||Advanced options are required. The following will set sqlexpress to 700 mb max server memory and then show all settings:
use master
Go
exec sp_configure 'show advanced options', 1;
Go
RECONFIGURE;
GO
exec sp_configure 'max server memory (MB)', 700;
GO
RECONFIGURE;
GO
SELECT * FROM sys.configurations
ORDER BY name ;
GO
limit per user/per db
Hello
I know that SQL 2000 has no way to set a limit for connections,
transactions memory or cpu per user or per db... but, there is a third
party software or any method that would allow a sysadmin to limit a
single db or a user?
ThanksWhat about ALTER DATABASE and the SINGLE_USER or RESTRICTED_USER options ?
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
I know that SQL 2000 has no way to set a limit for connections,
transactions memory or cpu per user or per db... but, there is a third
party software or any method that would allow a sysadmin to limit a
single db or a user?
ThanksWhat about ALTER DATABASE and the SINGLE_USER or RESTRICTED_USER options ?
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
limit per user /db on SQL 2005
Hello
is there any kind of limit (connections, transactions, memory, cpu)
that can be enforced per user or per db on sql 2005 ?
Thanks
Not really. If you have multiple SQL instances you can limit resources to
each instance (or app) via WSRM which is a windows 2003 feature.
Andrew J. Kelly SQL MVP
"xav" <xavierdaull@.gmail.com> wrote in message
news:1182700179.995006.253820@.n2g2000hse.googlegro ups.com...
> Hello
> is there any kind of limit (connections, transactions, memory, cpu)
> that can be enforced per user or per db on sql 2005 ?
> Thanks
>
|||Except for with sp2, you can limit number of connections using logon triggers.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OWy109ntHHA.3404@.TK2MSFTNGP03.phx.gbl...
> Not really. If you have multiple SQL instances you can limit resources to each instance (or app)
> via WSRM which is a windows 2003 feature.
> --
> Andrew J. Kelly SQL MVP
> "xav" <xavierdaull@.gmail.com> wrote in message
> news:1182700179.995006.253820@.n2g2000hse.googlegro ups.com...
>
|||> Except for with sp2, you can limit number of connections using logon triggers.
And how can I do it ?
|||By doing a ROLLBACK inside the trigger.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"xav" <xavierdaull@.gmail.com> wrote in message
news:1182862582.329249.214220@.w5g2000hsg.googlegro ups.com...
> And how can I do it ?
>
|||Thank you for your help.
Exemple found at: http://decipherinfosys.wordpress.com/2007/05/02/logon-triggers-in-sql-server-2005-sp2/
*Create a very simple login trigger */
create trigger AuditLogin_Demo
/* server means instance level*/
on all server
with execute as self
/* We specify the logon event at this stage
- If there are more than one connections,
- Issue a rollback*/
for logon
as begin
IF ORIGINAL_LOGIN()= 'AuditLogin' AND
(SELECT COUNT(*) FROM sys.dm_exec_sessions
WHERE is_user_process = 1 AND
original_login_name = 'AuditLogin') > 1
ROLLBACK;
end
go
is there any kind of limit (connections, transactions, memory, cpu)
that can be enforced per user or per db on sql 2005 ?
Thanks
Not really. If you have multiple SQL instances you can limit resources to
each instance (or app) via WSRM which is a windows 2003 feature.
Andrew J. Kelly SQL MVP
"xav" <xavierdaull@.gmail.com> wrote in message
news:1182700179.995006.253820@.n2g2000hse.googlegro ups.com...
> Hello
> is there any kind of limit (connections, transactions, memory, cpu)
> that can be enforced per user or per db on sql 2005 ?
> Thanks
>
|||Except for with sp2, you can limit number of connections using logon triggers.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OWy109ntHHA.3404@.TK2MSFTNGP03.phx.gbl...
> Not really. If you have multiple SQL instances you can limit resources to each instance (or app)
> via WSRM which is a windows 2003 feature.
> --
> Andrew J. Kelly SQL MVP
> "xav" <xavierdaull@.gmail.com> wrote in message
> news:1182700179.995006.253820@.n2g2000hse.googlegro ups.com...
>
|||> Except for with sp2, you can limit number of connections using logon triggers.
And how can I do it ?
|||By doing a ROLLBACK inside the trigger.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"xav" <xavierdaull@.gmail.com> wrote in message
news:1182862582.329249.214220@.w5g2000hsg.googlegro ups.com...
> And how can I do it ?
>
|||Thank you for your help.
Exemple found at: http://decipherinfosys.wordpress.com/2007/05/02/logon-triggers-in-sql-server-2005-sp2/
*Create a very simple login trigger */
create trigger AuditLogin_Demo
/* server means instance level*/
on all server
with execute as self
/* We specify the logon event at this stage
- If there are more than one connections,
- Issue a rollback*/
for logon
as begin
IF ORIGINAL_LOGIN()= 'AuditLogin' AND
(SELECT COUNT(*) FROM sys.dm_exec_sessions
WHERE is_user_process = 1 AND
original_login_name = 'AuditLogin') > 1
ROLLBACK;
end
go
limit per user /db on SQL 2005
Hello
is there any kind of limit (connections, transactions, memory, cpu)
that can be enforced per user or per db on sql 2005 ?
ThanksNot really. If you have multiple SQL instances you can limit resources to
each instance (or app) via WSRM which is a windows 2003 feature.
Andrew J. Kelly SQL MVP
"xav" <xavierdaull@.gmail.com> wrote in message
news:1182700179.995006.253820@.n2g2000hse.googlegroups.com...
> Hello
> is there any kind of limit (connections, transactions, memory, cpu)
> that can be enforced per user or per db on sql 2005 ?
> Thanks
>|||Except for with sp2, you can limit number of connections using logon trigger
s.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OWy109ntHHA.3404@.TK2MSFTNGP03.phx.gbl...
> Not really. If you have multiple SQL instances you can limit resources to
each instance (or app)
> via WSRM which is a windows 2003 feature.
> --
> Andrew J. Kelly SQL MVP
> "xav" <xavierdaull@.gmail.com> wrote in message
> news:1182700179.995006.253820@.n2g2000hse.googlegroups.com...
>|||> Except for with sp2, you can limit number of connections using logon trigg
ers.
And how can I do it ?|||By doing a ROLLBACK inside the trigger.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"xav" <xavierdaull@.gmail.com> wrote in message
news:1182862582.329249.214220@.w5g2000hsg.googlegroups.com...
> And how can I do it ?
>|||Thank you for your help.
Exemple found at: http://decipherinfosys.wordpress.co...erver-2005-sp2/
*Create a very simple login trigger */
create trigger AuditLogin_Demo
/* server means instance level*/
on all server
with execute as self
/* We specify the logon event at this stage
- If there are more than one connections,
- Issue a rollback*/
for logon
as begin
IF ORIGINAL_LOGIN()= 'AuditLogin' AND
(SELECT COUNT(*) FROM sys.dm_exec_sessions
WHERE is_user_process = 1 AND
original_login_name = 'AuditLogin') > 1
ROLLBACK;
end
go
is there any kind of limit (connections, transactions, memory, cpu)
that can be enforced per user or per db on sql 2005 ?
ThanksNot really. If you have multiple SQL instances you can limit resources to
each instance (or app) via WSRM which is a windows 2003 feature.
Andrew J. Kelly SQL MVP
"xav" <xavierdaull@.gmail.com> wrote in message
news:1182700179.995006.253820@.n2g2000hse.googlegroups.com...
> Hello
> is there any kind of limit (connections, transactions, memory, cpu)
> that can be enforced per user or per db on sql 2005 ?
> Thanks
>|||Except for with sp2, you can limit number of connections using logon trigger
s.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OWy109ntHHA.3404@.TK2MSFTNGP03.phx.gbl...
> Not really. If you have multiple SQL instances you can limit resources to
each instance (or app)
> via WSRM which is a windows 2003 feature.
> --
> Andrew J. Kelly SQL MVP
> "xav" <xavierdaull@.gmail.com> wrote in message
> news:1182700179.995006.253820@.n2g2000hse.googlegroups.com...
>|||> Except for with sp2, you can limit number of connections using logon trigg
ers.
And how can I do it ?|||By doing a ROLLBACK inside the trigger.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"xav" <xavierdaull@.gmail.com> wrote in message
news:1182862582.329249.214220@.w5g2000hsg.googlegroups.com...
> And how can I do it ?
>|||Thank you for your help.
Exemple found at: http://decipherinfosys.wordpress.co...erver-2005-sp2/
*Create a very simple login trigger */
create trigger AuditLogin_Demo
/* server means instance level*/
on all server
with execute as self
/* We specify the logon event at this stage
- If there are more than one connections,
- Issue a rollback*/
for logon
as begin
IF ORIGINAL_LOGIN()= 'AuditLogin' AND
(SELECT COUNT(*) FROM sys.dm_exec_sessions
WHERE is_user_process = 1 AND
original_login_name = 'AuditLogin') > 1
ROLLBACK;
end
go
limit per user /db on SQL 2005
Hello
is there any kind of limit (connections, transactions, memory, cpu)
that can be enforced per user or per db on sql 2005 ?
ThanksNot really. If you have multiple SQL instances you can limit resources to
each instance (or app) via WSRM which is a windows 2003 feature.
--
Andrew J. Kelly SQL MVP
"xav" <xavierdaull@.gmail.com> wrote in message
news:1182700179.995006.253820@.n2g2000hse.googlegroups.com...
> Hello
> is there any kind of limit (connections, transactions, memory, cpu)
> that can be enforced per user or per db on sql 2005 ?
> Thanks
>|||Except for with sp2, you can limit number of connections using logon triggers.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OWy109ntHHA.3404@.TK2MSFTNGP03.phx.gbl...
> Not really. If you have multiple SQL instances you can limit resources to each instance (or app)
> via WSRM which is a windows 2003 feature.
> --
> Andrew J. Kelly SQL MVP
> "xav" <xavierdaull@.gmail.com> wrote in message
> news:1182700179.995006.253820@.n2g2000hse.googlegroups.com...
>> Hello
>> is there any kind of limit (connections, transactions, memory, cpu)
>> that can be enforced per user or per db on sql 2005 ?
>> Thanks
>|||> Except for with sp2, you can limit number of connections using logon triggers.
And how can I do it ?|||By doing a ROLLBACK inside the trigger.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"xav" <xavierdaull@.gmail.com> wrote in message
news:1182862582.329249.214220@.w5g2000hsg.googlegroups.com...
>> Except for with sp2, you can limit number of connections using logon triggers.
> And how can I do it ?
>|||Thank you for your help.
Exemple found at: http://decipherinfosys.wordpress.com/2007/05/02/logon-triggers-in-sql-server-2005-sp2/
*Create a very simple login trigger */
create trigger AuditLogin_Demo
/* server means instance level*/
on all server
with execute as self
/* We specify the logon event at this stage
- If there are more than one connections,
- Issue a rollback*/
for logon
as begin
IF ORIGINAL_LOGIN()= 'AuditLogin' AND
(SELECT COUNT(*) FROM sys.dm_exec_sessions
WHERE is_user_process = 1 AND
original_login_name = 'AuditLogin') > 1
ROLLBACK;
end
go
is there any kind of limit (connections, transactions, memory, cpu)
that can be enforced per user or per db on sql 2005 ?
ThanksNot really. If you have multiple SQL instances you can limit resources to
each instance (or app) via WSRM which is a windows 2003 feature.
--
Andrew J. Kelly SQL MVP
"xav" <xavierdaull@.gmail.com> wrote in message
news:1182700179.995006.253820@.n2g2000hse.googlegroups.com...
> Hello
> is there any kind of limit (connections, transactions, memory, cpu)
> that can be enforced per user or per db on sql 2005 ?
> Thanks
>|||Except for with sp2, you can limit number of connections using logon triggers.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OWy109ntHHA.3404@.TK2MSFTNGP03.phx.gbl...
> Not really. If you have multiple SQL instances you can limit resources to each instance (or app)
> via WSRM which is a windows 2003 feature.
> --
> Andrew J. Kelly SQL MVP
> "xav" <xavierdaull@.gmail.com> wrote in message
> news:1182700179.995006.253820@.n2g2000hse.googlegroups.com...
>> Hello
>> is there any kind of limit (connections, transactions, memory, cpu)
>> that can be enforced per user or per db on sql 2005 ?
>> Thanks
>|||> Except for with sp2, you can limit number of connections using logon triggers.
And how can I do it ?|||By doing a ROLLBACK inside the trigger.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"xav" <xavierdaull@.gmail.com> wrote in message
news:1182862582.329249.214220@.w5g2000hsg.googlegroups.com...
>> Except for with sp2, you can limit number of connections using logon triggers.
> And how can I do it ?
>|||Thank you for your help.
Exemple found at: http://decipherinfosys.wordpress.com/2007/05/02/logon-triggers-in-sql-server-2005-sp2/
*Create a very simple login trigger */
create trigger AuditLogin_Demo
/* server means instance level*/
on all server
with execute as self
/* We specify the logon event at this stage
- If there are more than one connections,
- Issue a rollback*/
for logon
as begin
IF ORIGINAL_LOGIN()= 'AuditLogin' AND
(SELECT COUNT(*) FROM sys.dm_exec_sessions
WHERE is_user_process = 1 AND
original_login_name = 'AuditLogin') > 1
ROLLBACK;
end
go
Subscribe to:
Posts (Atom)