Limiting User Resources

I need to find a way to limit the resources that a user can hog on a SQL mac
hine. Is there any way to do this? I know that you can set priority level
to the SQL process, but we are actually wanting to limit a single user that
does mass updates periodica
lly throughout the day so that other users still get decent, if not stellar,
performance. Any ideas?
Thanks,
JoshJosh,
No, you can not throttle a user.
Perform the updates in smaller chucks by using SET ROWCOUNT and performing t
he update in a loop. Do not forget to use a unique where clause so that it s
kips the rows that were already updated in the next iteration.
Here is one article of many out on the web...
http://www.tek-tips.com/gfaqs.cfm/l...id/183/fid/3141
Norman
Norman

Limiting user records

Hi,
What is the best way to limit the number of records a user can insert
into a table. Basically, I want to set something like 50 records. If
the user tries to insert the 11th record, I want the action to FAIL,
therefore firing some SQL Error. I want to be able to customize this
error too.
So, what is the best way? Is it possible to get an example please?
Tascien
Your specs are inconsistent. You say you want to limit it to 50 rows but it
should fail on the 11th row. Could you post your DDL, sample data and
expected results?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
<tascienu@.ecoaches.com> wrote in message
news:1136420757.396966.83240@.o13g2000cwo.googlegro ups.com...
Hi,
What is the best way to limit the number of records a user can insert
into a table. Basically, I want to set something like 50 records. If
the user tries to insert the 11th record, I want the action to FAIL,
therefore firing some SQL Error. I want to be able to customize this
error too.
So, what is the best way? Is it possible to get an example please?
Tascien
|||tascienu@.ecoaches.com wrote:
> Hi,
> What is the best way to limit the number of records a user can insert
> into a table. Basically, I want to set something like 50 records. If
> the user tries to insert the 11th record, I want the action to FAIL,
> therefore firing some SQL Error. I want to be able to customize this
> error too.
> So, what is the best way? Is it possible to get an example please?
> Tascien
Do you mean total rows in a table or the number of rows executed in a
single insert statement? If you want total rows in a table then you'll
have to tag each row with the user name. You can use an instead of
insert trigger to first query the underlying table for the number of
rows owned by the user and add this to the number of rows in the
inserted table. If you want to limit the number in a single insert (as
in INSERT INTO... SELECT FROM) you can also use an instead of insert
trigger, but this time check COUNT(*) from inserted to make sure it's
not over the limit. Or maybe you want something else altogether...
David Gugick
Quest Software
www.quest.com
|||Sorry, I was giving an example... Let's say, i limit to 3 records...
1 First
2 Second
3 Third
4 Raise Error "You have reached the maximum allowed rows"
Tascien
|||create table test
(value char(100))
go
create trigger testrig
on test
instead of
insert
as
begin
if (select count(*) from test) >= 3
begin
rollback
raiserror ('Number of rows exceeds 3',16,1)
end
else
insert into test select value from inserted
end
go
insert into test values('first')
insert into test values('second')
insert into test values('third')
insert into test values('fourth')
go
select * from test
|||Wonderful. That is what i was looking for. By the way, adding a
Select Count(*) trigger for each insert... is it a performance issue
when the table gets big?
Tascien
|||tascienu@.ecoaches.com wrote:
> Wonderful. That is what i was looking for. By the way, adding a
> Select Count(*) trigger for each insert... is it a performance issue
> when the table gets big?
> Tascien
Yes. You might want to consider creating a clustered index on the user
column. But it is a concern you should account for before the table gets
too large.
David Gugick
Quest Software
www.quest.com
|||Yes you need to create an clustered index on it.
One more thing that I found about my last post is,
Suppose two users want to insert record and number of records are 2 in
the table when both of you checked it.so both of you will issue insert
commands. So first of them two should be succeeded. You have to put a
lock on the table.
so while you check you have to give lock hint of holdlock.
select count(*) from test (holdlock)
But then at a time only one user can insert into the table.
What is the reason for a table to limmit its number of records to some
number of rows.
Instead you can create a view to filter number of rows.
Regards
Amish
*** Sent via Developersdex http://www.codecomments.com ***
|||The reason I am limiting is not a critical one. I just don't want some
of my customers to exceed a number of records I assign to them. If they
exceed by 1 or 2, it should be fine...
Once again, thanks for help.
Tascien
|||Ok in that case you dont need to put hold lock is you are ready to
accept 1 or 2 more records then decided. It will not create locks on
the table.
Regards
Amish

Limiting user records

Hi,
What is the best way to limit the number of records a user can insert
into a table. Basically, I want to set something like 50 records. If
the user tries to insert the 11th record, I want the action to FAIL,
therefore firing some SQL Error. I want to be able to customize this
error too.
So, what is the best way? Is it possible to get an example please?
TascienYour specs are inconsistent. You say you want to limit it to 50 rows but it
should fail on the 11th row. Could you post your DDL, sample data and
expected results?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
<tascienu@.ecoaches.com> wrote in message
news:1136420757.396966.83240@.o13g2000cwo.googlegroups.com...
Hi,
What is the best way to limit the number of records a user can insert
into a table. Basically, I want to set something like 50 records. If
the user tries to insert the 11th record, I want the action to FAIL,
therefore firing some SQL Error. I want to be able to customize this
error too.
So, what is the best way? Is it possible to get an example please?
Tascien|||tascienu@.ecoaches.com wrote:
> Hi,
> What is the best way to limit the number of records a user can insert
> into a table. Basically, I want to set something like 50 records. If
> the user tries to insert the 11th record, I want the action to FAIL,
> therefore firing some SQL Error. I want to be able to customize this
> error too.
> So, what is the best way? Is it possible to get an example please?
> Tascien
Do you mean total rows in a table or the number of rows executed in a
single insert statement? If you want total rows in a table then you'll
have to tag each row with the user name. You can use an instead of
insert trigger to first query the underlying table for the number of
rows owned by the user and add this to the number of rows in the
inserted table. If you want to limit the number in a single insert (as
in INSERT INTO... SELECT FROM) you can also use an instead of insert
trigger, but this time check COUNT(*) from inserted to make sure it's
not over the limit. Or maybe you want something else altogether...
David Gugick
Quest Software
www.quest.com|||Sorry, I was giving an example... Let's say, i limit to 3 records...
1 First
2 Second
3 Third
4 Raise Error "You have reached the maximum allowed rows"
Tascien|||create table test
(value char(100))
go
create trigger testrig
on test
instead of
insert
as
begin
if (select count(*) from test) >= 3
begin
rollback
raiserror ('Number of rows exceeds 3',16,1)
end
else
insert into test select value from inserted
end
go
insert into test values('first')
insert into test values('second')
insert into test values('third')
insert into test values('fourth')
go
select * from test|||Wonderful. That is what i was looking for. By the way, adding a
Select Count(*) trigger for each insert... is it a performance issue
when the table gets big?
Tascien|||tascienu@.ecoaches.com wrote:
> Wonderful. That is what i was looking for. By the way, adding a
> Select Count(*) trigger for each insert... is it a performance issue
> when the table gets big?
> Tascien
Yes. You might want to consider creating a clustered index on the user
column. But it is a concern you should account for before the table gets
too large.
David Gugick
Quest Software
www.quest.com|||Yes you need to create an clustered index on it.
One more thing that I found about my last post is,
Suppose two users want to insert record and number of records are 2 in
the table when both of you checked it.so both of you will issue insert
commands. So first of them two should be succeeded. You have to put a
lock on the table.
so while you check you have to give lock hint of holdlock.
select count(*) from test (holdlock)
But then at a time only one user can insert into the table.
What is the reason for a table to limmit its number of records to some
number of rows.
Instead you can create a view to filter number of rows.
Regards
Amish
*** Sent via Developersdex http://www.codecomments.com ***|||The reason I am limiting is not a critical one. I just don't want some
of my customers to exceed a number of records I assign to them. If they
exceed by 1 or 2, it should be fine...
Once again, thanks for help.
Tascien|||Ok in that case you dont need to put hold lock is you are ready to
accept 1 or 2 more records then decided. It will not create locks on
the table.
Regards
Amish

Limiting user records

Hi,
What is the best way to limit the number of records a user can insert
into a table. Basically, I want to set something like 50 records. If
the user tries to insert the 11th record, I want the action to FAIL,
therefore firing some SQL Error. I want to be able to customize this
error too.
So, what is the best way? Is it possible to get an example please?
TascienYour specs are inconsistent. You say you want to limit it to 50 rows but it
should fail on the 11th row. Could you post your DDL, sample data and
expected results?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
<tascienu@.ecoaches.com> wrote in message
news:1136420757.396966.83240@.o13g2000cwo.googlegroups.com...
Hi,
What is the best way to limit the number of records a user can insert
into a table. Basically, I want to set something like 50 records. If
the user tries to insert the 11th record, I want the action to FAIL,
therefore firing some SQL Error. I want to be able to customize this
error too.
So, what is the best way? Is it possible to get an example please?
Tascien|||tascienu@.ecoaches.com wrote:
> Hi,
> What is the best way to limit the number of records a user can insert
> into a table. Basically, I want to set something like 50 records. If
> the user tries to insert the 11th record, I want the action to FAIL,
> therefore firing some SQL Error. I want to be able to customize this
> error too.
> So, what is the best way? Is it possible to get an example please?
> Tascien
Do you mean total rows in a table or the number of rows executed in a
single insert statement? If you want total rows in a table then you'll
have to tag each row with the user name. You can use an instead of
insert trigger to first query the underlying table for the number of
rows owned by the user and add this to the number of rows in the
inserted table. If you want to limit the number in a single insert (as
in INSERT INTO... SELECT FROM) you can also use an instead of insert
trigger, but this time check COUNT(*) from inserted to make sure it's
not over the limit. Or maybe you want something else altogether...
--
David Gugick
Quest Software
www.quest.com|||Sorry, I was giving an example... Let's say, i limit to 3 records...
1 First
2 Second
3 Third
4 Raise Error "You have reached the maximum allowed rows"
Tascien|||create table test
(value char(100))
go
create trigger testrig
on test
instead of
insert
as
begin
if (select count(*) from test) >= 3
begin
rollback
raiserror ('Number of rows exceeds 3',16,1)
end
else
insert into test select value from inserted
end
go
insert into test values('first')
insert into test values('second')
insert into test values('third')
insert into test values('fourth')
go
select * from test|||Wonderful. That is what i was looking for. By the way, adding a
Select Count(*) trigger for each insert... is it a performance issue
when the table gets big?
Tascien|||tascienu@.ecoaches.com wrote:
> Wonderful. That is what i was looking for. By the way, adding a
> Select Count(*) trigger for each insert... is it a performance issue
> when the table gets big?
> Tascien
Yes. You might want to consider creating a clustered index on the user
column. But it is a concern you should account for before the table gets
too large.
--
David Gugick
Quest Software
www.quest.com

Limiting traces in Profiler

I've opened a trace in Profile, and now want to limit the results based on
the TextData field. I've setup a filter for "textdata not like exec
some_existing_sp" and that worked fine.
How is a "or" added here, if possible?
I'd like a filter something like "textdata not like (exec some_existing_sp)
or (exec some_existing_sp2)".
Or and "()" don't seem to work though.
Marco
Close and expand the 'NOT LIKE' and you should get another input box.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Marco Shaw" <marco@.Znbnet.nb.ca> wrote in message
news:uQLNEDl4GHA.1248@.TK2MSFTNGP03.phx.gbl...
> I've opened a trace in Profile, and now want to limit the results based on
> the TextData field. I've setup a filter for "textdata not like exec
> some_existing_sp" and that worked fine.
> How is a "or" added here, if possible?
> I'd like a filter something like "textdata not like (exec
> some_existing_sp)
> or (exec some_existing_sp2)".
> Or and "()" don't seem to work though.
> Marco
>

Limiting traces in Profiler

I've opened a trace in Profile, and now want to limit the results based on
the TextData field. I've setup a filter for "textdata not like exec
some_existing_sp" and that worked fine.
How is a "or" added here, if possible?
I'd like a filter something like "textdata not like (exec some_existing_sp)
or (exec some_existing_sp2)".
Or and "()" don't seem to work though.
MarcoClose and expand the 'NOT LIKE' and you should get another input box.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Marco Shaw" <marco@.Znbnet.nb.ca> wrote in message
news:uQLNEDl4GHA.1248@.TK2MSFTNGP03.phx.gbl...
> I've opened a trace in Profile, and now want to limit the results based on
> the TextData field. I've setup a filter for "textdata not like exec
> some_existing_sp" and that worked fine.
> How is a "or" added here, if possible?
> I'd like a filter something like "textdata not like (exec
> some_existing_sp)
> or (exec some_existing_sp2)".
> Or and "()" don't seem to work though.
> Marco
>

Limiting traces in Profiler

I've opened a trace in Profile, and now want to limit the results based on
the TextData field. I've setup a filter for "textdata not like exec
some_existing_sp" and that worked fine.
How is a "or" added here, if possible?
I'd like a filter something like "textdata not like (exec some_existing_sp)
or (exec some_existing_sp2)".
Or and "()" don't seem to work though.
MarcoClose and expand the 'NOT LIKE' and you should get another input box.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Marco Shaw" <marco@.Znbnet.nb.ca> wrote in message
news:uQLNEDl4GHA.1248@.TK2MSFTNGP03.phx.gbl...
> I've opened a trace in Profile, and now want to limit the results based on
> the TextData field. I've setup a filter for "textdata not like exec
> some_existing_sp" and that worked fine.
> How is a "or" added here, if possible?
> I'd like a filter something like "textdata not like (exec
> some_existing_sp)
> or (exec some_existing_sp2)".
> Or and "()" don't seem to work though.
> Marco
>

limiting the resources

Hi
I have a problem on microsoft sql server 2000.
I need to limit the amount of resources(eg cpu, mem..) a query uses
Is there a way to do this in SQL server 2000
Thks in advance
:eek:for the server u can limit the max mem usage. u can also limit the max query time for the server as a whole or for a connection. check
SET QUERY_GOVERNOR_COST_LIMIT at BOL.

Limiting the Records in SELECT Statement

Hi..

As everyone knows that there's a keyword in MySQL that is used to limit the records in SELECT statement as

SELECT * FROM Table LIMIT 40,10

means that select from record 40 to onward 10 records.

In SQL Server it is a long way with nested select ... can someone provide an alternate or we have to use the Nested SELECT?

Thanks

Yes

U can specify

select top n * from tablename

similarly u can also specify top n percentage in a select statement.

Check for Keyword top in sql Help

|||

Nested select (technically called a derived table) is the best way to go in SQL. Here are a few articles that cover the subject.

http://www.sqlmag.com/Article/ArticleID/43922/sql_server_43922.html

http://sqljunkies.com/WebLog/amachanic/archive/2004/11/03/4945.aspx

http://davidhayden.com/blog/dave/archive/2005/12/30/2652.aspx

limiting the range of select result

Hi,

i'm searching how to tell to mysql server to limit
the range of a result select.
I would like to have the equivalent of the LIMIT clause in MYSQL.

I know there is TOP but it is not sufficient because it returns
only the first n lines.
I would like to tell sqlserver to begin at a certain offset.

Any idea ?

Thxyou can use a derived table

select top 10 *
from (select top 20 * from tbl order by id) as a
order by id desc

Will get the second 10 recs.

Limiting Rows in a Report

Hello,
i am in a problem that i am having a table with 100 rows while i am
presenting in report i want to limit rows to be 10 in a page in the
report like that i want to get 10 pages .Please say me the procedure
for this.
Thanks,
Baba.lucky (j.v.s.s.baba@.gmail.com) writes:
> i am in a problem that i am having a table with 100 rows while i am
> presenting in report i want to limit rows to be 10 in a page in the
> report like that i want to get 10 pages .Please say me the procedure
> for this.

I would suggest that you are better off asking this in a forum for the
report tool you are using. Surely you do not intend to requery the
database foe each page? Surely the report tool must permit you to define
a page size?

If the reporting tool is Reporting Services, tryu
microsoft.public.sqlserver.reportingsvcs.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Limiting number of merge processes

If with a merge publication we limit the number of simultaneous merge
processes say to 5 for 100 subscribers, do the 'waiting' merge agents
complete their tasks in an orderly queued fashion or is it just pot luck in
what order they complete? Is it possible that certain agents might be
'unlucky' and only infrequently complete the task.?
Its a fifo type process. The first 10 merge agents connect and
download/upload changes. The next ones wait until the first set complete.
The merge agents in the queue wait a predefined time - set by the
StartQueueTimeout parameter. If one or more of the first 10 agents complete
and the StartQueueTimeout parameter has not exceeded for the waiting agents,
these agents will then start processing. If the StartQueueTimeout parameter
has exceeded the agents themselves will timeout and try again.
"Tony Toker" <xyzzy@.identic.co.uk> wrote in message
news:cqblv5$eeo$1$8300dec7@.news.demon.co.uk...
> If with a merge publication we limit the number of simultaneous merge
> processes say to 5 for 100 subscribers, do the 'waiting' merge agents
> complete their tasks in an orderly queued fashion or is it just pot luck
in
> what order they complete? Is it possible that certain agents might be
> 'unlucky' and only infrequently complete the task.?
>

Limiting Number of Items Displayed in Report

ey there, quick question...

is there any way to limit the number of items displayed in a single page?

say i want to display up to 10 items, and then the rest of the items are then displayed on the next page?

any help and suggestions would be greatly appreciated.

thanks!

Tim

It's possible if you group on an expression rather than a field:

nest your table inside a list control|||thanks for replying sir

i got it already. thanks very much for your help|||I posted a working example in the other thread http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=157571&SiteID=1

Limiting number of database connections per user

Hello,
I would like to find out if it is possible in SQL Server 2000 to limit the
number of concurrent database connections per user. I am new to SQL Server,
and not sure if it is possible to limit it through some sort of parameter.
Any help would be greatly appreciated.
Thank you,
DmitriyNo way to limit the number of concurrent database connections PER USER.
Use the user connections option to specify the maximum number of
simultaneous user connections allowed on Microsoft? SQL Server
use SELECT @.@.MAX_CONNECTIONS to determine the maximum number of user
connections that your system allows
see "Setting Configuration Options" in BOL
Aleksandar Grbic
MCDBA, Senior Database Administrator
"Dmitriy" wrote:

> Hello,
> I would like to find out if it is possible in SQL Server 2000 to limit the
> number of concurrent database connections per user. I am new to SQL Server
,
> and not sure if it is possible to limit it through some sort of parameter.
> Any help would be greatly appreciated.
> Thank you,
> Dmitriy|||Thank you for your quick response.
Spasibo,
Dima
"Aleksandar Grbic" wrote:
> No way to limit the number of concurrent database connections PER USER.
> Use the user connections option to specify the maximum number of
> simultaneous user connections allowed on Microsoft? SQL Server
> use SELECT @.@.MAX_CONNECTIONS to determine the maximum number of user
> connections that your system allows
> see "Setting Configuration Options" in BOL
>
>
> --
> Aleksandar Grbic
> MCDBA, Senior Database Administrator
>
> "Dmitriy" wrote:
>|||Hi
you can use the table "sysprocesses" to get the information abt the
connections to the database , and there by u can restrict each users
programatically
Renjith
"Dmitriy" wrote:

> Hello,
> I would like to find out if it is possible in SQL Server 2000 to limit the
> number of concurrent database connections per user. I am new to SQL Server
,
> and not sure if it is possible to limit it through some sort of parameter.
> Any help would be greatly appreciated.
> Thank you,
> Dmitriy

Limiting large queries in AS2005?

Does anyone know of a way to stop large queries from running on an AS2005 instance, similar to the SQL2005 'query governor cost limit' configuration option? I want to stop users running large queries from apps like Excel, and bringing the server to a grinding halt.

Thanks

The only thing that I am aware of that comes close to this is the query timeout parameter see: http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!932.entry unfortunately it is retrospective in that it will not evaluate the cost of the query before it runs, rather if the query has been running for too long it will kill it.

Limiting job history in SQL2005

Hi,
Under SQL Agent properties/History there are 2 limiting columns for Limit
size of job history log.
We have Maximum job history set to 12000 and maximum job history rows per
job at 150 but we only seem to be able to keep 2 or 3 days worth of
jobhistory even though looking in sysjobhistory I can only return 7300 rows.
I thought that it would keep a maximum of 150 per job to a total of 12000.
Is something else limiting the data in sysjobhistory?
Thanks
ChrisHi,
We found the problem. If you run a maintenance plan that has a History
Cleanup Task and its run by an admin level account then the
sp_purge_jobhistory does not include a @.job_name parameter. This combination
means that all job history gets cleaned up rather than just the job that has
the History Cleanup Task problem raised on Connect as bug 322320.
Chris
"Chris Wood" <anonymous@.microsoft.com> wrote in message
news:O9nm5EQPIHA.4272@.TK2MSFTNGP06.phx.gbl...
> Hi,
> Under SQL Agent properties/History there are 2 limiting columns for Limit
> size of job history log.
> We have Maximum job history set to 12000 and maximum job history rows per
> job at 150 but we only seem to be able to keep 2 or 3 days worth of
> jobhistory even though looking in sysjobhistory I can only return 7300
> rows.
> I thought that it would keep a maximum of 150 per job to a total of 12000.
> Is something else limiting the data in sysjobhistory?
> Thanks
> Chris
>|||Very interesting Chris. Thanks for the update.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Chris Wood" <anonymous@.microsoft.com> wrote in message
news:eGw2yxcYIHA.4208@.TK2MSFTNGP04.phx.gbl...
> Hi,
> We found the problem. If you run a maintenance plan that has a History
> Cleanup Task and its run by an admin level account then the
> sp_purge_jobhistory does not include a @.job_name parameter. This
> combination means that all job history gets cleaned up rather than just
> the job that has the History Cleanup Task problem raised on Connect as bug
> 322320.
> Chris
> "Chris Wood" <anonymous@.microsoft.com> wrote in message
> news:O9nm5EQPIHA.4272@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> Under SQL Agent properties/History there are 2 limiting columns for Limit
>> size of job history log.
>> We have Maximum job history set to 12000 and maximum job history rows per
>> job at 150 but we only seem to be able to keep 2 or 3 days worth of
>> jobhistory even though looking in sysjobhistory I can only return 7300
>> rows.
>> I thought that it would keep a maximum of 150 per job to a total of
>> 12000.
>> Is something else limiting the data in sysjobhistory?
>> Thanks
>> Chris
>|||Unfortunately Andrew I have not seen any reposnse to my Connect post.
Thanks
Chris
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:Or3yu6cYIHA.4808@.TK2MSFTNGP05.phx.gbl...
> Very interesting Chris. Thanks for the update.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Chris Wood" <anonymous@.microsoft.com> wrote in message
> news:eGw2yxcYIHA.4208@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> We found the problem. If you run a maintenance plan that has a History
>> Cleanup Task and its run by an admin level account then the
>> sp_purge_jobhistory does not include a @.job_name parameter. This
>> combination means that all job history gets cleaned up rather than just
>> the job that has the History Cleanup Task problem raised on Connect as
>> bug 322320.
>> Chris
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:O9nm5EQPIHA.4272@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> Under SQL Agent properties/History there are 2 limiting columns for
>> Limit size of job history log.
>> We have Maximum job history set to 12000 and maximum job history rows
>> per job at 150 but we only seem to be able to keep 2 or 3 days worth of
>> jobhistory even though looking in sysjobhistory I can only return 7300
>> rows.
>> I thought that it would keep a maximum of 150 per job to a total of
>> 12000.
>> Is something else limiting the data in sysjobhistory?
>> Thanks
>> Chris
>>
>|||Actually I just had a look at the sp_purge_jobhistory sp and the bottom line
is that if you don't specify a job ID or name it will delete all history
beyond the date specified. It makes no difference how many rows you have set
in SQL Agent as it does not even check for that. So if you want to keep x
many days worth you need to ensure that the date specified in the Maint Plan
for the history cleanup is far enough out so the history is maintained.
There is no link between the two. The Agent behavior was that way long
before the maint plan had the hist cleanup task and they simply didn't
factor in agents settings when they wrote the task for the maint plans. They
should have have had you specify a minimum # of rows deform they deleted
them by date only.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Chris Wood" <anonymous@.microsoft.com> wrote in message
news:OoK7jCdYIHA.1208@.TK2MSFTNGP03.phx.gbl...
> Unfortunately Andrew I have not seen any reposnse to my Connect post.
> Thanks
> Chris
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:Or3yu6cYIHA.4808@.TK2MSFTNGP05.phx.gbl...
>> Very interesting Chris. Thanks for the update.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:eGw2yxcYIHA.4208@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> We found the problem. If you run a maintenance plan that has a History
>> Cleanup Task and its run by an admin level account then the
>> sp_purge_jobhistory does not include a @.job_name parameter. This
>> combination means that all job history gets cleaned up rather than just
>> the job that has the History Cleanup Task problem raised on Connect as
>> bug 322320.
>> Chris
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:O9nm5EQPIHA.4272@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> Under SQL Agent properties/History there are 2 limiting columns for
>> Limit size of job history log.
>> We have Maximum job history set to 12000 and maximum job history rows
>> per job at 150 but we only seem to be able to keep 2 or 3 days worth of
>> jobhistory even though looking in sysjobhistory I can only return 7300
>> rows.
>> I thought that it would keep a maximum of 150 per job to a total of
>> 12000.
>> Is something else limiting the data in sysjobhistory?
>> Thanks
>> Chris
>>
>>
>|||Andrew,
That's part of the problem. BOL states that if you are an admin then a
straight sp_purge_jobhistory, without the @.job_name will purge ALL
jobhistory. This is my problem. The maintenance plan does not include the
@.job_name parameter otherwise this might have worked fine. We had to look at
all our jobs that do a cleanup and are run by an admin account to make sure
that we have made the cleanup period long enough.
Chris
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uW1Q5tdYIHA.5984@.TK2MSFTNGP06.phx.gbl...
> Actually I just had a look at the sp_purge_jobhistory sp and the bottom
> line is that if you don't specify a job ID or name it will delete all
> history beyond the date specified. It makes no difference how many rows
> you have set in SQL Agent as it does not even check for that. So if you
> want to keep x many days worth you need to ensure that the date specified
> in the Maint Plan for the history cleanup is far enough out so the history
> is maintained. There is no link between the two. The Agent behavior was
> that way long before the maint plan had the hist cleanup task and they
> simply didn't factor in agents settings when they wrote the task for the
> maint plans. They should have have had you specify a minimum # of rows
> deform they deleted them by date only.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Chris Wood" <anonymous@.microsoft.com> wrote in message
> news:OoK7jCdYIHA.1208@.TK2MSFTNGP03.phx.gbl...
>> Unfortunately Andrew I have not seen any reposnse to my Connect post.
>> Thanks
>> Chris
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:Or3yu6cYIHA.4808@.TK2MSFTNGP05.phx.gbl...
>> Very interesting Chris. Thanks for the update.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:eGw2yxcYIHA.4208@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> We found the problem. If you run a maintenance plan that has a History
>> Cleanup Task and its run by an admin level account then the
>> sp_purge_jobhistory does not include a @.job_name parameter. This
>> combination means that all job history gets cleaned up rather than just
>> the job that has the History Cleanup Task problem raised on Connect as
>> bug 322320.
>> Chris
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:O9nm5EQPIHA.4272@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> Under SQL Agent properties/History there are 2 limiting columns for
>> Limit size of job history log.
>> We have Maximum job history set to 12000 and maximum job history rows
>> per job at 150 but we only seem to be able to keep 2 or 3 days worth
>> of jobhistory even though looking in sysjobhistory I can only return
>> 7300 rows.
>> I thought that it would keep a maximum of 150 per job to a total of
>> 12000.
>> Is something else limiting the data in sysjobhistory?
>> Thanks
>> Chris
>>
>>
>>
>|||Chris,
That only means that you need it to be in the sa or SQLAgentOperatorRole in
order to modify any jobs that are not you own. Since you don't specify a job
ID it has to assume you need to be in one of those two roles. If not it
throws an exception and does nothing. So if you want to keep a certain
amount of history for a particular job I suggest you create your won
scheduled job that calls sp_purge_jobhistory with the proper Job names and
dates for each. You may need to set up a table with any jobs that need a
date other than the default you pass. It's basically do it for every job by
date or one job at a time by date and job id or name. Unfortunately there is
no in between.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Chris Wood" <anonymous@.microsoft.com> wrote in message
news:%232tTjgeYIHA.3652@.TK2MSFTNGP02.phx.gbl...
> Andrew,
> That's part of the problem. BOL states that if you are an admin then a
> straight sp_purge_jobhistory, without the @.job_name will purge ALL
> jobhistory. This is my problem. The maintenance plan does not include the
> @.job_name parameter otherwise this might have worked fine. We had to look
> at all our jobs that do a cleanup and are run by an admin account to make
> sure that we have made the cleanup period long enough.
> Chris
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uW1Q5tdYIHA.5984@.TK2MSFTNGP06.phx.gbl...
>> Actually I just had a look at the sp_purge_jobhistory sp and the bottom
>> line is that if you don't specify a job ID or name it will delete all
>> history beyond the date specified. It makes no difference how many rows
>> you have set in SQL Agent as it does not even check for that. So if you
>> want to keep x many days worth you need to ensure that the date specified
>> in the Maint Plan for the history cleanup is far enough out so the
>> history is maintained. There is no link between the two. The Agent
>> behavior was that way long before the maint plan had the hist cleanup
>> task and they simply didn't factor in agents settings when they wrote the
>> task for the maint plans. They should have have had you specify a minimum
>> # of rows deform they deleted them by date only.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:OoK7jCdYIHA.1208@.TK2MSFTNGP03.phx.gbl...
>> Unfortunately Andrew I have not seen any reposnse to my Connect post.
>> Thanks
>> Chris
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:Or3yu6cYIHA.4808@.TK2MSFTNGP05.phx.gbl...
>> Very interesting Chris. Thanks for the update.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:eGw2yxcYIHA.4208@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> We found the problem. If you run a maintenance plan that has a History
>> Cleanup Task and its run by an admin level account then the
>> sp_purge_jobhistory does not include a @.job_name parameter. This
>> combination means that all job history gets cleaned up rather than
>> just the job that has the History Cleanup Task problem raised on
>> Connect as bug 322320.
>> Chris
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:O9nm5EQPIHA.4272@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> Under SQL Agent properties/History there are 2 limiting columns for
>> Limit size of job history log.
>> We have Maximum job history set to 12000 and maximum job history rows
>> per job at 150 but we only seem to be able to keep 2 or 3 days worth
>> of jobhistory even though looking in sysjobhistory I can only return
>> 7300 rows.
>> I thought that it would keep a maximum of 150 per job to a total of
>> 12000.
>> Is something else limiting the data in sysjobhistory?
>> Thanks
>> Chris
>>
>>
>>
>|||Thanks again Andrew. MS could always fix the bug and include the @.job_name
in the script they produce.
I don't see us changing our backups as you have to be an admin to run the
xp_delete_file that comes from the Maintenance Cleanup Task (See bug 247463
in Connect).
Chris
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eJzpwveYIHA.4272@.TK2MSFTNGP05.phx.gbl...
> Chris,
> That only means that you need it to be in the sa or SQLAgentOperatorRole
> in order to modify any jobs that are not you own. Since you don't specify
> a job ID it has to assume you need to be in one of those two roles. If not
> it throws an exception and does nothing. So if you want to keep a certain
> amount of history for a particular job I suggest you create your won
> scheduled job that calls sp_purge_jobhistory with the proper Job names and
> dates for each. You may need to set up a table with any jobs that need a
> date other than the default you pass. It's basically do it for every job
> by date or one job at a time by date and job id or name. Unfortunately
> there is no in between.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Chris Wood" <anonymous@.microsoft.com> wrote in message
> news:%232tTjgeYIHA.3652@.TK2MSFTNGP02.phx.gbl...
>> Andrew,
>> That's part of the problem. BOL states that if you are an admin then a
>> straight sp_purge_jobhistory, without the @.job_name will purge ALL
>> jobhistory. This is my problem. The maintenance plan does not include the
>> @.job_name parameter otherwise this might have worked fine. We had to look
>> at all our jobs that do a cleanup and are run by an admin account to make
>> sure that we have made the cleanup period long enough.
>> Chris
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:uW1Q5tdYIHA.5984@.TK2MSFTNGP06.phx.gbl...
>> Actually I just had a look at the sp_purge_jobhistory sp and the bottom
>> line is that if you don't specify a job ID or name it will delete all
>> history beyond the date specified. It makes no difference how many rows
>> you have set in SQL Agent as it does not even check for that. So if you
>> want to keep x many days worth you need to ensure that the date
>> specified in the Maint Plan for the history cleanup is far enough out so
>> the history is maintained. There is no link between the two. The Agent
>> behavior was that way long before the maint plan had the hist cleanup
>> task and they simply didn't factor in agents settings when they wrote
>> the task for the maint plans. They should have have had you specify a
>> minimum # of rows deform they deleted them by date only.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:OoK7jCdYIHA.1208@.TK2MSFTNGP03.phx.gbl...
>> Unfortunately Andrew I have not seen any reposnse to my Connect post.
>> Thanks
>> Chris
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:Or3yu6cYIHA.4808@.TK2MSFTNGP05.phx.gbl...
>> Very interesting Chris. Thanks for the update.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:eGw2yxcYIHA.4208@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> We found the problem. If you run a maintenance plan that has a
>> History Cleanup Task and its run by an admin level account then the
>> sp_purge_jobhistory does not include a @.job_name parameter. This
>> combination means that all job history gets cleaned up rather than
>> just the job that has the History Cleanup Task problem raised on
>> Connect as bug 322320.
>> Chris
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:O9nm5EQPIHA.4272@.TK2MSFTNGP06.phx.gbl...
>>> Hi,
>>>
>>> Under SQL Agent properties/History there are 2 limiting columns for
>>> Limit size of job history log.
>>> We have Maximum job history set to 12000 and maximum job history
>>> rows per job at 150 but we only seem to be able to keep 2 or 3 days
>>> worth of jobhistory even though looking in sysjobhistory I can only
>>> return 7300 rows.
>>>
>>> I thought that it would keep a maximum of 150 per job to a total of
>>> 12000.
>>>
>>> Is something else limiting the data in sysjobhistory?
>>>
>>> Thanks
>>>
>>> Chris
>>>
>>
>>
>>
>>
>|||> Thanks again Andrew. MS could always fix the bug and include the @.job_name
> in the script they produce.
I think you are missing my point. The task in the Maint Plan does not have a
way to specify the job only a date. This isn't a bug it is simply how they
created the task. How do you expect them to specify the job name? Which job
name are they supposed to use? Like I said the task is not in any way shape
or form tied to SQL Agent. It simply deletes rows from MSDB that happen to
be the Agent history. But it knows nothing of the jobs themselves. If it
looped thru each job one by one and specified the name it would still delete
all the rows for each job that were after the date you specified. You end up
with the same result. So this isn't a bug and I am sure they will not fix it
as such. If you want anything other than that behavior you must create it
your self. The Maint Plans are extremely limiting in what they can do and
how they do it. And it is not always clear on what it actually does unless
you trace the plans actions. One of the main reasons why I almost never
recommend using Maint Plans in the first place.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Chris Wood" <anonymous@.microsoft.com> wrote in message
news:um0fh8eYIHA.5132@.TK2MSFTNGP02.phx.gbl...
> Thanks again Andrew. MS could always fix the bug and include the @.job_name
> in the script they produce.
> I don't see us changing our backups as you have to be an admin to run the
> xp_delete_file that comes from the Maintenance Cleanup Task (See bug
> 247463 in Connect).
> Chris
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eJzpwveYIHA.4272@.TK2MSFTNGP05.phx.gbl...
>> Chris,
>> That only means that you need it to be in the sa or SQLAgentOperatorRole
>> in order to modify any jobs that are not you own. Since you don't specify
>> a job ID it has to assume you need to be in one of those two roles. If
>> not it throws an exception and does nothing. So if you want to keep a
>> certain amount of history for a particular job I suggest you create your
>> won scheduled job that calls sp_purge_jobhistory with the proper Job
>> names and dates for each. You may need to set up a table with any jobs
>> that need a date other than the default you pass. It's basically do it
>> for every job by date or one job at a time by date and job id or name.
>> Unfortunately there is no in between.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:%232tTjgeYIHA.3652@.TK2MSFTNGP02.phx.gbl...
>> Andrew,
>> That's part of the problem. BOL states that if you are an admin then a
>> straight sp_purge_jobhistory, without the @.job_name will purge ALL
>> jobhistory. This is my problem. The maintenance plan does not include
>> the @.job_name parameter otherwise this might have worked fine. We had to
>> look at all our jobs that do a cleanup and are run by an admin account
>> to make sure that we have made the cleanup period long enough.
>> Chris
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:uW1Q5tdYIHA.5984@.TK2MSFTNGP06.phx.gbl...
>> Actually I just had a look at the sp_purge_jobhistory sp and the bottom
>> line is that if you don't specify a job ID or name it will delete all
>> history beyond the date specified. It makes no difference how many rows
>> you have set in SQL Agent as it does not even check for that. So if
>> you want to keep x many days worth you need to ensure that the date
>> specified in the Maint Plan for the history cleanup is far enough out
>> so the history is maintained. There is no link between the two. The
>> Agent behavior was that way long before the maint plan had the hist
>> cleanup task and they simply didn't factor in agents settings when they
>> wrote the task for the maint plans. They should have have had you
>> specify a minimum # of rows deform they deleted them by date only.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:OoK7jCdYIHA.1208@.TK2MSFTNGP03.phx.gbl...
>> Unfortunately Andrew I have not seen any reposnse to my Connect post.
>> Thanks
>> Chris
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:Or3yu6cYIHA.4808@.TK2MSFTNGP05.phx.gbl...
>> Very interesting Chris. Thanks for the update.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:eGw2yxcYIHA.4208@.TK2MSFTNGP04.phx.gbl...
>>> Hi,
>>>
>>> We found the problem. If you run a maintenance plan that has a
>>> History Cleanup Task and its run by an admin level account then the
>>> sp_purge_jobhistory does not include a @.job_name parameter. This
>>> combination means that all job history gets cleaned up rather than
>>> just the job that has the History Cleanup Task problem raised on
>>> Connect as bug 322320.
>>>
>>> Chris
>>>
>>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>>> news:O9nm5EQPIHA.4272@.TK2MSFTNGP06.phx.gbl...
>>> Hi,
>>>
>>> Under SQL Agent properties/History there are 2 limiting columns for
>>> Limit size of job history log.
>>> We have Maximum job history set to 12000 and maximum job history
>>> rows per job at 150 but we only seem to be able to keep 2 or 3 days
>>> worth of jobhistory even though looking in sysjobhistory I can only
>>> return 7300 rows.
>>>
>>> I thought that it would keep a maximum of 150 per job to a total of
>>> 12000.
>>>
>>> Is something else limiting the data in sysjobhistory?
>>>
>>> Thanks
>>>
>>> Chris
>>>
>>>
>>>
>>
>>
>>
>|||I see your point Andrew. I hadn't thought about how the Maint Plan would use
the Maintenance Cleanup Task. I am not a Visual Studio user so I don't
always see how the toolset gets used in Management Studio.
It definitely sounds as though taking this task out of the maintenance plans
and creating a nightly job could be the best option.
Chris
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23I2X1bfYIHA.4172@.TK2MSFTNGP02.phx.gbl...
>> Thanks again Andrew. MS could always fix the bug and include the
>> @.job_name in the script they produce.
> I think you are missing my point. The task in the Maint Plan does not have
> a way to specify the job only a date. This isn't a bug it is simply how
> they created the task. How do you expect them to specify the job name?
> Which job name are they supposed to use? Like I said the task is not in
> any way shape or form tied to SQL Agent. It simply deletes rows from MSDB
> that happen to be the Agent history. But it knows nothing of the jobs
> themselves. If it looped thru each job one by one and specified the name
> it would still delete all the rows for each job that were after the date
> you specified. You end up with the same result. So this isn't a bug and I
> am sure they will not fix it as such. If you want anything other than that
> behavior you must create it your self. The Maint Plans are extremely
> limiting in what they can do and how they do it. And it is not always
> clear on what it actually does unless you trace the plans actions. One of
> the main reasons why I almost never recommend using Maint Plans in the
> first place.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Chris Wood" <anonymous@.microsoft.com> wrote in message
> news:um0fh8eYIHA.5132@.TK2MSFTNGP02.phx.gbl...
>> Thanks again Andrew. MS could always fix the bug and include the
>> @.job_name in the script they produce.
>> I don't see us changing our backups as you have to be an admin to run the
>> xp_delete_file that comes from the Maintenance Cleanup Task (See bug
>> 247463 in Connect).
>> Chris
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:eJzpwveYIHA.4272@.TK2MSFTNGP05.phx.gbl...
>> Chris,
>> That only means that you need it to be in the sa or SQLAgentOperatorRole
>> in order to modify any jobs that are not you own. Since you don't
>> specify a job ID it has to assume you need to be in one of those two
>> roles. If not it throws an exception and does nothing. So if you want to
>> keep a certain amount of history for a particular job I suggest you
>> create your won scheduled job that calls sp_purge_jobhistory with the
>> proper Job names and dates for each. You may need to set up a table with
>> any jobs that need a date other than the default you pass. It's
>> basically do it for every job by date or one job at a time by date and
>> job id or name. Unfortunately there is no in between.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:%232tTjgeYIHA.3652@.TK2MSFTNGP02.phx.gbl...
>> Andrew,
>> That's part of the problem. BOL states that if you are an admin then a
>> straight sp_purge_jobhistory, without the @.job_name will purge ALL
>> jobhistory. This is my problem. The maintenance plan does not include
>> the @.job_name parameter otherwise this might have worked fine. We had
>> to look at all our jobs that do a cleanup and are run by an admin
>> account to make sure that we have made the cleanup period long enough.
>> Chris
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:uW1Q5tdYIHA.5984@.TK2MSFTNGP06.phx.gbl...
>> Actually I just had a look at the sp_purge_jobhistory sp and the
>> bottom line is that if you don't specify a job ID or name it will
>> delete all history beyond the date specified. It makes no difference
>> how many rows you have set in SQL Agent as it does not even check for
>> that. So if you want to keep x many days worth you need to ensure
>> that the date specified in the Maint Plan for the history cleanup is
>> far enough out so the history is maintained. There is no link between
>> the two. The Agent behavior was that way long before the maint plan
>> had the hist cleanup task and they simply didn't factor in agents
>> settings when they wrote the task for the maint plans. They should
>> have have had you specify a minimum # of rows deform they deleted them
>> by date only.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:OoK7jCdYIHA.1208@.TK2MSFTNGP03.phx.gbl...
>> Unfortunately Andrew I have not seen any reposnse to my Connect post.
>> Thanks
>> Chris
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:Or3yu6cYIHA.4808@.TK2MSFTNGP05.phx.gbl...
>>> Very interesting Chris. Thanks for the update.
>>>
>>> --
>>> Andrew J. Kelly SQL MVP
>>> Solid Quality Mentors
>>>
>>>
>>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>>> news:eGw2yxcYIHA.4208@.TK2MSFTNGP04.phx.gbl...
>>> Hi,
>>>
>>> We found the problem. If you run a maintenance plan that has a
>>> History Cleanup Task and its run by an admin level account then the
>>> sp_purge_jobhistory does not include a @.job_name parameter. This
>>> combination means that all job history gets cleaned up rather than
>>> just the job that has the History Cleanup Task problem raised on
>>> Connect as bug 322320.
>>>
>>> Chris
>>>
>>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>>> news:O9nm5EQPIHA.4272@.TK2MSFTNGP06.phx.gbl...
>>> Hi,
>>>
>>> Under SQL Agent properties/History there are 2 limiting columns
>>> for Limit size of job history log.
>>> We have Maximum job history set to 12000 and maximum job history
>>> rows per job at 150 but we only seem to be able to keep 2 or 3
>>> days worth of jobhistory even though looking in sysjobhistory I
>>> can only return 7300 rows.
>>>
>>> I thought that it would keep a maximum of 150 per job to a total
>>> of 12000.
>>>
>>> Is something else limiting the data in sysjobhistory?
>>>
>>> Thanks
>>>
>>> Chris
>>>
>>>
>>>
>>>
>>
>>
>>
>>
>

Limiting Export Rendering Options

I have a request to limit the number of rendering options available for
exporting. I know I can limit the options for all reports by modifying the
RSReportServer.config file. But is there a way programmatically to set the
available rendering options for a single report?The config file setting affects all reports. In order to limit the export
for certain reports, you would need to implement your own front end
application for the report server (similar to report manager) and manage the
restrictions on the report level yourself.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Greg Larsen" <GregLarsen@.discussions.microsoft.com> wrote in message
news:A440907D-0EAB-465E-BA5A-73ECA1EFDD72@.microsoft.com...
>I have a request to limit the number of rendering options available for
> exporting. I know I can limit the options for all reports by modifying
> the
> RSReportServer.config file. But is there a way programmatically to set
> the
> available rendering options for a single report?|||where might I find out some information in how to build my own front end?
"Robert Bruckner [MSFT]" wrote:
> The config file setting affects all reports. In order to limit the export
> for certain reports, you would need to implement your own front end
> application for the report server (similar to report manager) and manage the
> restrictions on the report level yourself.
>
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Greg Larsen" <GregLarsen@.discussions.microsoft.com> wrote in message
> news:A440907D-0EAB-465E-BA5A-73ECA1EFDD72@.microsoft.com...
> >I have a request to limit the number of rendering options available for
> > exporting. I know I can limit the options for all reports by modifying
> > the
> > RSReportServer.config file. But is there a way programmatically to set
> > the
> > available rendering options for a single report?
>
>

Limiting Domain Admin access to SQL Server

How do I limit Domain Admins from administering SQL Server?
In my orginization we have multiple Domain Admins and we
need to limit the number of user ID's that can administer
SQL Server to as few as possible.
There are many legal issues that brought this forward.
Mainly the need to limit the number of people that have
the ability to "change" data that resides on this server.
Thanks.
MattAdd only the trusted individuals to the sysadmin role, then remove
BUILTIN\Administrators from the sysadmin role.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Matt Troska" <matt.troska@.co.mcleod.mn.us> wrote in message
news:01a701c3dae0$59f035f0$a601280a@.phx.gbl...
How do I limit Domain Admins from administering SQL Server?
In my orginization we have multiple Domain Admins and we
need to limit the number of user ID's that can administer
SQL Server to as few as possible.
There are many legal issues that brought this forward.
Mainly the need to limit the number of people that have
the ability to "change" data that resides on this server.
Thanks.
Matt

Limiting Demensions

Hello,

while creating a demension, how can you limit data. For example, I have a product table that has products broke up by product lines, I would like to create a demension for each product line due to the large amount of products and the fact that we do not report off all of the product lines as much as others.

any hints?Howdy

Copy the specific data into some new tables then work on those.

Cheers

SG|||yeah, I figured that as a possible solution, but wanted to know if there was another way. Just because we are talking about some 20 plus tables and then would have to do the same for some other tables, so they are multipling quickly if I do that.

Thanks for the post|||Howdy,

Let sql do the grunt work by using **scripts**, thats why you have got it. You probably only use 50% of its capacity anyway....

The better you can separate stuff out through using a disciplined approach, it makes problem solving & management a whole lot easier.

Its from experience.....

Cheers,

SG

Limiting Dataset for Chart but not Table

I have a report that has a single dataset that is used to populate both a
chart and a table. I want to limit the rows used by the chart but I want
the table to display all rows. I'm having difficulty determining how to
apply some sort of "rowcount <=10" condition to the chart's dataset. I
would like to avoid executing the same query twice.
I have been unable to find any references to this searching through this
group or the web (via google). Nor could I find anything in the Books
Online.
Is this possible or must I create two datasets, one that limits the number
of rows returned and another that returns all rows? If this can be done,
can someone please point me to an article that explains how?
Thanks,
ChrisChristopher,
SQL Server 2000 Reporting Services supports filtering on data regions
(table, matrix, list, and chart). Please see Reporting Services Books On
Line (BOL) for more information.
To access the filtering option for charts:
* Open the chart properties dialog
* Click on the Data tab
* Add or Edit a Category group (same applies to Series groups)
* Click on the Filtering tab
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Christopher Walls" <msnews@.travantsolutions.com> wrote in message
news:uYHUnKUVEHA.1652@.TK2MSFTNGP09.phx.gbl...
> I have a report that has a single dataset that is used to populate both a
> chart and a table. I want to limit the rows used by the chart but I want
> the table to display all rows. I'm having difficulty determining how to
> apply some sort of "rowcount <=10" condition to the chart's dataset. I
> would like to avoid executing the same query twice.
> I have been unable to find any references to this searching through this
> group or the web (via google). Nor could I find anything in the Books
> Online.
> Is this possible or must I create two datasets, one that limits the number
> of rows returned and another that returns all rows? If this can be done,
> can someone please point me to an article that explains how?
> Thanks,
> Chris
>|||My category is on a field named "TargetOpenDate", so how do I setup the
filter? I cannot find any guidance either in the BOL or searching on Google
(web or groups).
I just want to limit the number of rows to the Top 5. I cannot leave the
Expression field blank. It seems no matter which field I select (either the
field I'm using for the category or some other arbitrary field), I get an
error "Fail to evaluate FilterExpression/FilterValues".
Expression: =Fields!TargetOpenDate.Value
Operator: Top N
Value: 3
Thanks,
Chris
"Bruce Johnson [MSFT]" <brucejoh@.online.microsoft.com> wrote in message
news:u7%23BJyVVEHA.4048@.TK2MSFTNGP12.phx.gbl...
> Christopher,
> SQL Server 2000 Reporting Services supports filtering on data regions
> (table, matrix, list, and chart). Please see Reporting Services Books On
> Line (BOL) for more information.
> To access the filtering option for charts:
> * Open the chart properties dialog
> * Click on the Data tab
> * Add or Edit a Category group (same applies to Series groups)
> * Click on the Filtering tab
> --
> Bruce Johnson [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Christopher Walls" <msnews@.travantsolutions.com> wrote in message
> news:uYHUnKUVEHA.1652@.TK2MSFTNGP09.phx.gbl...
> > I have a report that has a single dataset that is used to populate both
a
> > chart and a table. I want to limit the rows used by the chart but I
want
> > the table to display all rows. I'm having difficulty determining how to
> > apply some sort of "rowcount <=10" condition to the chart's dataset. I
> > would like to avoid executing the same query twice.
> >
> > I have been unable to find any references to this searching through this
> > group or the web (via google). Nor could I find anything in the Books
> > Online.
> >
> > Is this possible or must I create two datasets, one that limits the
number
> > of rows returned and another that returns all rows? If this can be
done,
> > can someone please point me to an article that explains how?
> >
> > Thanks,
> > Chris
> >
> >
>|||There is a trick. You need the value to be =3 instead of just 3. The
expression evaluator guesses that 3 is a string.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Christopher Walls" <msnews@.travantsolutions.com> wrote in message
news:Or7f3JwVEHA.644@.tk2msftngp13.phx.gbl...
> My category is on a field named "TargetOpenDate", so how do I setup the
> filter? I cannot find any guidance either in the BOL or searching on
Google
> (web or groups).
> I just want to limit the number of rows to the Top 5. I cannot leave the
> Expression field blank. It seems no matter which field I select (either
the
> field I'm using for the category or some other arbitrary field), I get an
> error "Fail to evaluate FilterExpression/FilterValues".
> Expression: =Fields!TargetOpenDate.Value
> Operator: Top N
> Value: 3
>
> Thanks,
> Chris
>
> "Bruce Johnson [MSFT]" <brucejoh@.online.microsoft.com> wrote in message
> news:u7%23BJyVVEHA.4048@.TK2MSFTNGP12.phx.gbl...
> > Christopher,
> >
> > SQL Server 2000 Reporting Services supports filtering on data regions
> > (table, matrix, list, and chart). Please see Reporting Services Books On
> > Line (BOL) for more information.
> >
> > To access the filtering option for charts:
> >
> > * Open the chart properties dialog
> > * Click on the Data tab
> > * Add or Edit a Category group (same applies to Series groups)
> > * Click on the Filtering tab
> >
> > --
> > Bruce Johnson [MSFT]
> > Microsoft SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> >
> > "Christopher Walls" <msnews@.travantsolutions.com> wrote in message
> > news:uYHUnKUVEHA.1652@.TK2MSFTNGP09.phx.gbl...
> > > I have a report that has a single dataset that is used to populate
both
> a
> > > chart and a table. I want to limit the rows used by the chart but I
> want
> > > the table to display all rows. I'm having difficulty determining how
to
> > > apply some sort of "rowcount <=10" condition to the chart's dataset.
I
> > > would like to avoid executing the same query twice.
> > >
> > > I have been unable to find any references to this searching through
this
> > > group or the web (via google). Nor could I find anything in the Books
> > > Online.
> > >
> > > Is this possible or must I create two datasets, one that limits the
> number
> > > of rows returned and another that returns all rows? If this can be
> done,
> > > can someone please point me to an article that explains how?
> > >
> > > Thanks,
> > > Chris
> > >
> > >
> >
> >
>|||Thanks, that worked.
"Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
news:ub6EY%23wVEHA.1656@.TK2MSFTNGP09.phx.gbl...
> There is a trick. You need the value to be =3 instead of just 3. The
> expression evaluator guesses that 3 is a string.
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Christopher Walls" <msnews@.travantsolutions.com> wrote in message
> news:Or7f3JwVEHA.644@.tk2msftngp13.phx.gbl...
> > My category is on a field named "TargetOpenDate", so how do I setup the
> > filter? I cannot find any guidance either in the BOL or searching on
> Google
> > (web or groups).
> >
> > I just want to limit the number of rows to the Top 5. I cannot leave
the
> > Expression field blank. It seems no matter which field I select (either
> the
> > field I'm using for the category or some other arbitrary field), I get
an
> > error "Fail to evaluate FilterExpression/FilterValues".
> >
> > Expression: =Fields!TargetOpenDate.Value
> > Operator: Top N
> > Value: 3
> >
> >
> > Thanks,
> > Chris
> >
> >
> > "Bruce Johnson [MSFT]" <brucejoh@.online.microsoft.com> wrote in message
> > news:u7%23BJyVVEHA.4048@.TK2MSFTNGP12.phx.gbl...
> > > Christopher,
> > >
> > > SQL Server 2000 Reporting Services supports filtering on data regions
> > > (table, matrix, list, and chart). Please see Reporting Services Books
On
> > > Line (BOL) for more information.
> > >
> > > To access the filtering option for charts:
> > >
> > > * Open the chart properties dialog
> > > * Click on the Data tab
> > > * Add or Edit a Category group (same applies to Series groups)
> > > * Click on the Filtering tab
> > >
> > > --
> > > Bruce Johnson [MSFT]
> > > Microsoft SQL Server Reporting Services
> > >
> > > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > >
> > >
> > > "Christopher Walls" <msnews@.travantsolutions.com> wrote in message
> > > news:uYHUnKUVEHA.1652@.TK2MSFTNGP09.phx.gbl...
> > > > I have a report that has a single dataset that is used to populate
> both
> > a
> > > > chart and a table. I want to limit the rows used by the chart but I
> > want
> > > > the table to display all rows. I'm having difficulty determining
how
> to
> > > > apply some sort of "rowcount <=10" condition to the chart's dataset.
> I
> > > > would like to avoid executing the same query twice.
> > > >
> > > > I have been unable to find any references to this searching through
> this
> > > > group or the web (via google). Nor could I find anything in the
Books
> > > > Online.
> > > >
> > > > Is this possible or must I create two datasets, one that limits the
> > number
> > > > of rows returned and another that returns all rows? If this can be
> > done,
> > > > can someone please point me to an article that explains how?
> > > >
> > > > Thanks,
> > > > Chris
> > > >
> > > >
> > >
> > >
> >
> >
>