Monday, March 12, 2012
good statistic for blocking
process will not stop blocking.
We are trying to trace the problem, but in the interim,
I would like to set up an alert that notifies me when a
process has been blocking for too long.
Are any of the lock wait times good statistics to use
for such an alert? If not, is there anything else I could
look at from the alert level?
If I had to, I could periodically create a table of
sysprocess spids that are at the top of blocking chains,
then test for a spid that lingers. I'm hoping I can
avoid this and use the built-in monitoring instead, though.
Thanks!
GeoffWe're also monitoring open transactions kept for a specific threshold, depending on an app. In addition, look at sp_monitor system stored procedure and you may get some ideas on what and how it can be accomplished.|||What information do you have so far concerning the locking issue - how long is it blocking, how did you discover that blocking was occuring ... ?|||Thanks, rdjabarov. Active Transactions is a good idea, since they
don't ever seem to build up higher than 4 or 5, if today's performance
monitor results can be trusted.
rnealjr:
When we encounter the problem, the blocking processes seem like
they would continue blocking perpetually. The only thing I can do to
solve the situation is to go in and kill the offending process. I am
trying to address the reason this occurs, but in the meantime I need
to fix it by brute force.
We are running a Java application that accesses the data using
a CORBA-based middle tier (Versata, in case you've heard of it).
The CORBA objects use the MS JDBC driver to hit the database,
which is MS SQL 2000.
I just tried upgrading the driver to sp2 and SQL to sp3a last night.
Things were going well this morning, but I think I caused some
trouble when I ran DBCC SHOWCONTIG while following a performance
tuning doc.
Anyway, I figured I'd show an example of an offending process,
obtained by using sp_blocker_pss80:
========================================
SPID: 144
STATUS: sleeping
BOLCKED: 0
OPEN_TRAN: 1
WAITRESOURCE: 8:1:269596
WAITTYPE: 0x0000
WAITTIME: 0
CMD: AWAITING COMMAND
LATWAITTYPE: PAGEIOLATCH_SH
CPU: 30
PHYSICAL_IO: 26
MEMUSAGE: 26
LASTBATCH: 2004-01-14 08:48:28.003
LOGINTIME: 2004-01-14 08:41:00.490
NETADDRESS 000000000000
NET_LIBRARY: TCP/IP
DBID: 8
ECID: 0
KPID: 0
HOSTNAME: spnvls00.na01.crl.com
HOSTPROCESS: 0
LOGINNAME: ilims
PRGRAMNAME:
NT_DOMAIN:
NT_USERNAME:
UID: 1
SID: 0xCDEB103BDA944A418B51092B9253F78D...
SQL_HANDLE: 0x0000000000000000000000000000000000000000
STMT_START: 0
STMT_END: 0
========================================
The thing I find weird, although a more experienced eye may not, is
that it's sleeping and "AWAITING COMMAND", yet it still has 1 open
transaction. It seems to me like this kind of situation could cause
a lingering block.
Thanks again!|||99% of the time it IS the main reason for blocking, along with long running transactions.|||When this happens again, you can use enterprise manager. Go to management -> current activity -> locks/processid - this will help you locate the process that is blocking and will show all the lock information associated with that process. Open that process - that will show up to 255 characters what action is causing the block which will help you locate what/where the problem is. Always make sure that you refresh the "current activity" first - otherwise, you might be looking at old process info. Once you have the information you need, then kill the process.|||Below is the script I've been using since 6.5 version to identify the blocking chain:
set statistics time off
set nocount on
set statistics io off
if charindex('6.5', @.@.version) = 0 begin
exec ('set statistics profile off')
end
--go
select
spid,
BlockedBy=blocked,
Status=convert(char(10), status),
DBName=convert(varchar(20), db_name(dbid)),
Host=convert(varchar(20), hostname),
Program=convert(varchar(40), program_name),
LoggedIn=convert(char(10), login_time, 101) + ' ' + convert(varchar(12), login_time, 114),
LastBatch=convert(char(10), last_batch, 101) + ' ' + convert(varchar(12), last_batch, 114)
from master.dbo.sysprocesses blocker (NOLOCK)
WHERE Exists (
select
spid,
blocked,
status,
DBName=convert(varchar(20), db_name(dbid)),
Host=convert(varchar(20), hostname),
Program=convert(varchar(40), program_name),
LoggedIn=convert(char(10), login_time, 101) + ' ' + convert(varchar(12), login_time, 114),
LastBatch=convert(char(10), last_batch, 101) + ' ' + convert(varchar(12), last_batch, 114)
from master.dbo.sysprocesses victim (NOLOCK)
where blocker.spid = victim.blocked)
UNION
select
spid,
blocked,
Status=convert(char(10), status),
DBName=convert(varchar(20), db_name(dbid)),
Host=convert(varchar(20), hostname),
Program=convert(varchar(40), program_name),
LoggedIn=convert(char(10), login_time, 101) + ' ' + convert(varchar(12), login_time, 114),
LastBatch=convert(char(10), last_batch, 101) + ' ' + convert(varchar(12), last_batch, 114)
from master.dbo.sysprocesses blocker (NOLOCK)
where blocked != 0
ORDER BY blocked, spid
begin tran
create table #tmp (spid int null, BlockedBy int null)
commit tran
insert #tmp (spid, BlockedBy) select spid, BlockedBy from (
select
spid,
BlockedBy=blocked,
DBName=convert(varchar(30), db_name(dbid)),
Host=convert(varchar(30), hostname),
Program=convert(varchar(50), program_name),
LoggedIn=convert(char(10), login_time, 101) + ' ' + convert(varchar(12), login_time, 114),
LastBatch=convert(char(10), last_batch, 101) + ' ' + convert(varchar(12), last_batch, 114)
from master.dbo.sysprocesses blocker (NOLOCK)
WHERE Exists (
select
spid,
blocked,
DBName=convert(varchar(30), db_name(dbid)),
Host=convert(varchar(30), hostname),
Program=convert(varchar(50), program_name),
LoggedIn=convert(char(10), login_time, 101) + ' ' + convert(varchar(12), login_time, 114),
LastBatch=convert(char(10), last_batch, 101) + ' ' + convert(varchar(12), last_batch, 114)
from master.dbo.sysprocesses victim (NOLOCK)
where blocker.spid = victim.blocked)
UNION
select
spid,
blocked,
DBName=convert(varchar(30), db_name(dbid)),
Host=convert(varchar(30), hostname),
Program=convert(varchar(50), program_name),
LoggedIn=convert(char(10), login_time, 101) + ' ' + convert(varchar(12), login_time, 114),
LastBatch=convert(char(10), last_batch, 101) + ' ' + convert(varchar(12), last_batch, 114)
from master.dbo.sysprocesses blocker (NOLOCK)
where blocked != 0 ) x
ORDER BY blockedby, spid
if @.@.rowcount > 0 begin
set nocount on
declare @.spid int, @.cmd varchar(255), @.cmd1 varchar(255)
select @.spid = min(spid) from #tmp where BlockedBy = 0
while @.spid is not null begin
select @.cmd = 'dbcc inputbuffer(' + convert(varchar(25), @.spid) + ')'
set @.cmd1= '!!!!!!!!!!!!!!!!!INPUTBUFFER information for blocking SPID ' + convert(varchar(25), @.spid) + ':'
print ''
print '************************************************* ***************************************'
print @.cmd1
print '************************************************* ***************************************'
exec (@.cmd)
-- print '************************************************* ***************************************'
print ''
select @.spid = min(spid) from #tmp where BlockedBy = 0 and spid > @.spid
end
print '************************************************* ****************************************'
print ''
print 'LIST OF AFFECTED PROCESSES'
print '**************************'
select @.spid = min(spid) from #tmp
while @.spid is not null begin
select @.cmd = 'dbcc inputbuffer(' + convert(varchar(25), @.spid) + ')'
select @.cmd1= 'INPUTBUFFER informtion for SPID ' + convert(varchar(25), @.spid) + ':'
print @.cmd1
exec (@.cmd)
print ''
select @.spid = min(spid) from #tmp where spid > @.spid
end
end else
print 'No blocking detected!'
go
drop table #tmp
go
Good SQL Server Agent book?
like to use SQL Server Agent. I'm finding it difficult to learn how
Agent works. The help files are skimpy and scattered and
leave many questions unanswered.
One thing I've noticed is that Agent does a lot of things behind
the curtain that the user did not request. If I write a script using
TSQL commands like sp_add_job, sp_add_job_step, etc., and
then invoke it via Agent, Agent does things that aren't in my
script. If I ask Agent to generate a script for my job it spits
out a script that includes most of what I ordered, but with
a lot of other boilerplate that I didn't order and some things
like transaction management, that may not have been what
I wanted.
Is all of this documented somewhere? Has someone written
a good book that covers this and gives recommendations on
the best ways to use Agent?
Thanks.
Alan<ameyer2@.yahoo.com> wrote in message
news:1153251901.175708.34630@.s13g2000cwa.googlegroups.com...
>I plan to automate a process using about 20 steps and would
> like to use SQL Server Agent. I'm finding it difficult to learn how
> Agent works. The help files are skimpy and scattered and
> leave many questions unanswered.
> One thing I've noticed is that Agent does a lot of things behind
> the curtain that the user did not request. If I write a script using
> TSQL commands like sp_add_job, sp_add_job_step, etc., and
> then invoke it via Agent, Agent does things that aren't in my
> script. If I ask Agent to generate a script for my job it spits
> out a script that includes most of what I ordered, but with
> a lot of other boilerplate that I didn't order and some things
> like transaction management, that may not have been what
> I wanted.
> Is all of this documented somewhere? Has someone written
> a good book that covers this and gives recommendations on
> the best ways to use Agent?
>
I would build your 20-step process in an SSIS package. Then deploy that
package as a 1-step SQL Agent job.
SSIS has a graphical workflow designer, integrated debugger, rich logging,
etc.
David|||David Browne wrote:
> ...
> I would build your 20-step process in an SSIS package. Then deploy that
> package as a 1-step SQL Agent job.
> SSIS has a graphical workflow designer, integrated debugger, rich logging,
> etc.
> David
Thanks David.
Unfortunately, we're still running SQLServer 2000, and it looks
like we'd have to upgrade our servers to SQL Server 2005 to
use SSIS. That may be more expensive and time consuming
than management would support just for this task.
Agent also has a graphical user interface, though I'm not sure
I wouldn't prefer submitting a script. The big advantage of a
script over a GUI, in my view, is that you can see everything
that happens in one listing, and if you're running multiple servers
you can easily move it to all of them. You can also document
a script with extensive comments.
However it looks like Microsoft really doesn't want you using
scripts for this purpose, so we'll probably use the GUI and have
it generate a script for us to load on multiple servers and to
store in our documentation safe.
Regards,
Alan|||Its not really documented but you can get some guidance by click on help in
the job help dialog. Right click on your job and select help is a
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<ameyer2@.yahoo.com> wrote in message
news:1153261730.359715.126050@.i42g2000cwa.googlegroups.com...
> David Browne wrote:
> Thanks David.
> Unfortunately, we're still running SQLServer 2000, and it looks
> like we'd have to upgrade our servers to SQL Server 2005 to
> use SSIS. That may be more expensive and time consuming
> than management would support just for this task.
> Agent also has a graphical user interface, though I'm not sure
> I wouldn't prefer submitting a script. The big advantage of a
> script over a GUI, in my view, is that you can see everything
> that happens in one listing, and if you're running multiple servers
> you can easily move it to all of them. You can also document
> a script with extensive comments.
> However it looks like Microsoft really doesn't want you using
> scripts for this purpose, so we'll probably use the GUI and have
> it generate a script for us to load on multiple servers and to
> store in our documentation safe.
> Regards,
> Alan
>|||Hilary Cotter wrote:
> Its not really documented but you can get some guidance by click on help i
n
> the job help dialog. Right click on your job and select help is a
> ...
Thanks Hilary.
I also just found this:
http://www.microsoft.com/technet/pr...s/c12ppcsq.mspx
It seems to give more information than is in the help file.
Alan|||ameyer2@.yahoo.com wrote:
> David Browne wrote:
>
> Thanks David.
> Unfortunately, we're still running SQLServer 2000, and it looks
> like we'd have to upgrade our servers to SQL Server 2005 to
> use SSIS. That may be more expensive and time consuming
> than management would support just for this task.
> Agent also has a graphical user interface, though I'm not sure
> I wouldn't prefer submitting a script. The big advantage of a
> script over a GUI, in my view, is that you can see everything
> that happens in one listing, and if you're running multiple servers
> you can easily move it to all of them. You can also document
> a script with extensive comments.
> However it looks like Microsoft really doesn't want you using
> scripts for this purpose, so we'll probably use the GUI and have
> it generate a script for us to load on multiple servers and to
> store in our documentation safe.
> Regards,
> Alan
>
Hi Alan
Why don't you just create a DTS package and then run this package from
SQL server agent?
I must admit that I'm not quite sure what your problem is because I've
used SQL agent for years for many different purposes and I haven't
really seen that it add things I haven't asked for.
Regards
Steen Schlter Persson
Databaseadministrator / Systemadministrator|||Hello Steen. Thank you for the reply
Steen Persson (DK) wrote:
> Why don't you just create a DTS package and then run this package from
> SQL server agent?
Thanks for the suggestion. I will look into that.
> I must admit that I'm not quite sure what your problem is because I've
> used SQL agent for years for many different purposes and I haven't
> really seen that it add things I haven't asked for.
I presume you have mainly used the Agent GUI.
To see what I'm talking about, pick one of your Agent jobs
and export it as a text script, e.g.,
Open Agent.
Select any job
Right click on the job and select "All Tasks" / "Generate SQL
Script..."
Then examine what you get.
If you write your own script and submit it to SQL Server without
using the GUI front end, you can get your new job to show up in
the GUI list of jobs. However if you then use the GUI to generate
a script from it and compare what was generated to what you
input, you might be surprised. I was.
Alan|||ameyer2@.yahoo.com wrote:
> Hello Steen. Thank you for the reply
> Steen Persson (DK) wrote:
>
> Thanks for the suggestion. I will look into that.
>
> I presume you have mainly used the Agent GUI.
> To see what I'm talking about, pick one of your Agent jobs
> and export it as a text script, e.g.,
> Open Agent.
> Select any job
> Right click on the job and select "All Tasks" / "Generate SQL
> Script..."
> Then examine what you get.
> If you write your own script and submit it to SQL Server without
> using the GUI front end, you can get your new job to show up in
> the GUI list of jobs. However if you then use the GUI to generate
> a script from it and compare what was generated to what you
> input, you might be surprised. I was.
> Alan
>
Hi Alan
I still don't quite understand what your issue is. It's true that if I
generate a sql script from an existing job, there are more code in it,
but there's nothing I don't expect to be there. There are number of
checks if the job already exist etc. and that's all right for me. If you
don't check for this in your "manual" script you'll get an error if you
try to add a job that already exists so why shouldn't the check be there
when you generate the script?
Regards
Steen Schlter Persson
Databaseadministrator / Systemadministrator|||ameyer2@.yahoo.com wrote:
> Hello Steen. Thank you for the reply
> Steen Persson (DK) wrote:
>
> Thanks for the suggestion. I will look into that.
>
> I presume you have mainly used the Agent GUI.
> To see what I'm talking about, pick one of your Agent jobs
> and export it as a text script, e.g.,
> Open Agent.
> Select any job
> Right click on the job and select "All Tasks" / "Generate SQL
> Script..."
> Then examine what you get.
> If you write your own script and submit it to SQL Server without
> using the GUI front end, you can get your new job to show up in
> the GUI list of jobs. However if you then use the GUI to generate
> a script from it and compare what was generated to what you
> input, you might be surprised. I was.
> Alan
>
Perhaps you should analyze that additional code to determine what it's
doing? Maybe you're missing a step in your own scripts?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Tracy McKibben wrote:
> Perhaps you should analyze that additional code to determine what it's
> doing? Maybe you're missing a step in your own scripts?
>
I have indeed analyzed it. Some of what is there is stuff that either
I don't need because I've done the same thing another way, or
because I just don't need it.
Some is questionable. I'm not in front of SQL Server right now,
but as I recall, it wraps my entire job step in a transaction. That
may be right in the average case but wrong in particular cases
where it could defeat the intent of the programmer.
I can see why this approach by Microsoft has its fans. I'm
just not one of them.
But I didn't create this thread for the purpose of flaming
Microsoft. I recognize the MS has produced many fine
programs, SQL Server among them. I just wanted to know
if anyone knew of any documentation of the things MS does
under the covers of the Agent GUI.
I did find one source of information. See my first posting on
July 19 for the reference. If anyone knows of any other
documentation I'd love to see it.
Thanks.
Alan
Good SQL Server Agent book?
like to use SQL Server Agent. I'm finding it difficult to learn how
Agent works. The help files are skimpy and scattered and
leave many questions unanswered.
One thing I've noticed is that Agent does a lot of things behind
the curtain that the user did not request. If I write a script using
TSQL commands like sp_add_job, sp_add_job_step, etc., and
then invoke it via Agent, Agent does things that aren't in my
script. If I ask Agent to generate a script for my job it spits
out a script that includes most of what I ordered, but with
a lot of other boilerplate that I didn't order and some things
like transaction management, that may not have been what
I wanted.
Is all of this documented somewhere? Has someone written
a good book that covers this and gives recommendations on
the best ways to use Agent?
Thanks.
Alan<ameyer2@.yahoo.com> wrote in message
news:1153251901.175708.34630@.s13g2000cwa.googlegroups.com...
>I plan to automate a process using about 20 steps and would
> like to use SQL Server Agent. I'm finding it difficult to learn how
> Agent works. The help files are skimpy and scattered and
> leave many questions unanswered.
> One thing I've noticed is that Agent does a lot of things behind
> the curtain that the user did not request. If I write a script using
> TSQL commands like sp_add_job, sp_add_job_step, etc., and
> then invoke it via Agent, Agent does things that aren't in my
> script. If I ask Agent to generate a script for my job it spits
> out a script that includes most of what I ordered, but with
> a lot of other boilerplate that I didn't order and some things
> like transaction management, that may not have been what
> I wanted.
> Is all of this documented somewhere? Has someone written
> a good book that covers this and gives recommendations on
> the best ways to use Agent?
>
I would build your 20-step process in an SSIS package. Then deploy that
package as a 1-step SQL Agent job.
SSIS has a graphical workflow designer, integrated debugger, rich logging,
etc.
David|||David Browne wrote:
> ...
> I would build your 20-step process in an SSIS package. Then deploy that
> package as a 1-step SQL Agent job.
> SSIS has a graphical workflow designer, integrated debugger, rich logging,
> etc.
> David
Thanks David.
Unfortunately, we're still running SQLServer 2000, and it looks
like we'd have to upgrade our servers to SQL Server 2005 to
use SSIS. That may be more expensive and time consuming
than management would support just for this task.
Agent also has a graphical user interface, though I'm not sure
I wouldn't prefer submitting a script. The big advantage of a
script over a GUI, in my view, is that you can see everything
that happens in one listing, and if you're running multiple servers
you can easily move it to all of them. You can also document
a script with extensive comments.
However it looks like Microsoft really doesn't want you using
scripts for this purpose, so we'll probably use the GUI and have
it generate a script for us to load on multiple servers and to
store in our documentation safe.
Regards,
Alan|||Its not really documented but you can get some guidance by click on help in
the job help dialog. Right click on your job and select help is a
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<ameyer2@.yahoo.com> wrote in message
news:1153261730.359715.126050@.i42g2000cwa.googlegroups.com...
> David Browne wrote:
>> ...
>> I would build your 20-step process in an SSIS package. Then deploy that
>> package as a 1-step SQL Agent job.
>> SSIS has a graphical workflow designer, integrated debugger, rich
>> logging,
>> etc.
>> David
> Thanks David.
> Unfortunately, we're still running SQLServer 2000, and it looks
> like we'd have to upgrade our servers to SQL Server 2005 to
> use SSIS. That may be more expensive and time consuming
> than management would support just for this task.
> Agent also has a graphical user interface, though I'm not sure
> I wouldn't prefer submitting a script. The big advantage of a
> script over a GUI, in my view, is that you can see everything
> that happens in one listing, and if you're running multiple servers
> you can easily move it to all of them. You can also document
> a script with extensive comments.
> However it looks like Microsoft really doesn't want you using
> scripts for this purpose, so we'll probably use the GUI and have
> it generate a script for us to load on multiple servers and to
> store in our documentation safe.
> Regards,
> Alan
>|||Hilary Cotter wrote:
> Its not really documented but you can get some guidance by click on help in
> the job help dialog. Right click on your job and select help is a
> ...
Thanks Hilary.
I also just found this:
http://www.microsoft.com/technet/prodtechnol/sql/2000/books/c12ppcsq.mspx
It seems to give more information than is in the help file.
Alan|||This is a multi-part message in MIME format.
--000609000409020907060701
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
ameyer2@.yahoo.com wrote:
> David Browne wrote:
>> ...
>> I would build your 20-step process in an SSIS package. Then deploy that
>> package as a 1-step SQL Agent job.
>> SSIS has a graphical workflow designer, integrated debugger, rich logging,
>> etc.
>> David
> Thanks David.
> Unfortunately, we're still running SQLServer 2000, and it looks
> like we'd have to upgrade our servers to SQL Server 2005 to
> use SSIS. That may be more expensive and time consuming
> than management would support just for this task.
> Agent also has a graphical user interface, though I'm not sure
> I wouldn't prefer submitting a script. The big advantage of a
> script over a GUI, in my view, is that you can see everything
> that happens in one listing, and if you're running multiple servers
> you can easily move it to all of them. You can also document
> a script with extensive comments.
> However it looks like Microsoft really doesn't want you using
> scripts for this purpose, so we'll probably use the GUI and have
> it generate a script for us to load on multiple servers and to
> store in our documentation safe.
> Regards,
> Alan
>
Hi Alan
Why don't you just create a DTS package and then run this package from
SQL server agent?
I must admit that I'm not quite sure what your problem is because I've
used SQL agent for years for many different purposes and I haven't
really seen that it add things I haven't asked for.
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator
--000609000409020907060701
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=mailto:ameyer2@.yahoo.com">ameyer2@.yahoo.com</a> wrote:
<blockquote
cite="mid1153261730.359715.126050@.i42g2000cwa.googlegroups.com"
type="cite">
<pre wrap="">David Browne wrote:
</pre>
<blockquote type="cite">
<pre wrap="">...
I would build your 20-step process in an SSIS package. Then deploy that
package as a 1-step SQL Agent job.
SSIS has a graphical workflow designer, integrated debugger, rich logging,
etc.
David
</pre>
</blockquote>
<pre wrap=""><!-->
Thanks David.
Unfortunately, we're still running SQLServer 2000, and it looks
like we'd have to upgrade our servers to SQL Server 2005 to
use SSIS. That may be more expensive and time consuming
than management would support just for this task.
Agent also has a graphical user interface, though I'm not sure
I wouldn't prefer submitting a script. The big advantage of a
script over a GUI, in my view, is that you can see everything
that happens in one listing, and if you're running multiple servers
you can easily move it to all of them. You can also document
a script with extensive comments.
However it looks like Microsoft really doesn't want you using
scripts for this purpose, so we'll probably use the GUI and have
it generate a script for us to load on multiple servers and to
store in our documentation safe.
Regards,
Alan
</pre>
</blockquote>
<font size="-1"><font face="Arial">Hi Alan<br>
<br>
Why don't you just create a DTS package and then run this package from
SQL server agent? <br>
I must admit that I'm not quite sure what your problem is because I've
used SQL agent for years for many different purposes and I haven't
really seen that it add things I haven't asked for.<br>
<br>
<br>
-- <br>
Regards<br>
Steen Schlüter Persson<br>
Databaseadministrator / Systemadministrator<br>
</font></font>
</body>
</html>
--000609000409020907060701--|||Hello Steen. Thank you for the reply
Steen Persson (DK) wrote:
> Why don't you just create a DTS package and then run this package from
> SQL server agent?
Thanks for the suggestion. I will look into that.
> I must admit that I'm not quite sure what your problem is because I've
> used SQL agent for years for many different purposes and I haven't
> really seen that it add things I haven't asked for.
I presume you have mainly used the Agent GUI.
To see what I'm talking about, pick one of your Agent jobs
and export it as a text script, e.g.,
Open Agent.
Select any job
Right click on the job and select "All Tasks" / "Generate SQL
Script..."
Then examine what you get.
If you write your own script and submit it to SQL Server without
using the GUI front end, you can get your new job to show up in
the GUI list of jobs. However if you then use the GUI to generate
a script from it and compare what was generated to what you
input, you might be surprised. I was.
Alan|||This is a multi-part message in MIME format.
--090000090502030800020104
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
ameyer2@.yahoo.com wrote:
> Hello Steen. Thank you for the reply
> Steen Persson (DK) wrote:
>
>> Why don't you just create a DTS package and then run this package from
>> SQL server agent?
> Thanks for the suggestion. I will look into that.
>
>> I must admit that I'm not quite sure what your problem is because I've
>> used SQL agent for years for many different purposes and I haven't
>> really seen that it add things I haven't asked for.
> I presume you have mainly used the Agent GUI.
> To see what I'm talking about, pick one of your Agent jobs
> and export it as a text script, e.g.,
> Open Agent.
> Select any job
> Right click on the job and select "All Tasks" / "Generate SQL
> Script..."
> Then examine what you get.
> If you write your own script and submit it to SQL Server without
> using the GUI front end, you can get your new job to show up in
> the GUI list of jobs. However if you then use the GUI to generate
> a script from it and compare what was generated to what you
> input, you might be surprised. I was.
> Alan
>
Hi Alan
I still don't quite understand what your issue is. It's true that if I
generate a sql script from an existing job, there are more code in it,
but there's nothing I don't expect to be there. There are number of
checks if the job already exist etc. and that's all right for me. If you
don't check for this in your "manual" script you'll get an error if you
try to add a job that already exists so why shouldn't the check be there
when you generate the script?
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator
--090000090502030800020104
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=mailto:ameyer2@.yahoo.com">ameyer2@.yahoo.com</a> wrote:
<blockquote
cite="mid1153434437.302932.299920@.i42g2000cwa.googlegroups.com"
type="cite">
<pre wrap="">Hello Steen. Thank you for the reply
Steen Persson (DK) wrote:
</pre>
<blockquote type="cite">
<pre wrap="">Why don't you just create a DTS package and then run this package from
SQL server agent?
</pre>
</blockquote>
<pre wrap=""><!-->
Thanks for the suggestion. I will look into that.
</pre>
<blockquote type="cite">
<pre wrap="">I must admit that I'm not quite sure what your problem is because I've
used SQL agent for years for many different purposes and I haven't
really seen that it add things I haven't asked for.
</pre>
</blockquote>
<pre wrap=""><!-->
I presume you have mainly used the Agent GUI.
To see what I'm talking about, pick one of your Agent jobs
and export it as a text script, e.g.,
Open Agent.
Select any job
Right click on the job and select "All Tasks" / "Generate SQL
Script..."
Then examine what you get.
If you write your own script and submit it to SQL Server without
using the GUI front end, you can get your new job to show up in
the GUI list of jobs. However if you then use the GUI to generate
a script from it and compare what was generated to what you
input, you might be surprised. I was.
Alan
</pre>
</blockquote>
<font size="-1"><font face="Arial">Hi Alan<br>
<br>
I still don't quite understand what your issue is. It's true that if I
generate a sql script from an existing job, there are more code in it,
but there's nothing I don't expect to be there. There are number of
checks if the job already exist etc. and that's all right for me. If
you don't check for this in your "manual" script you'll get an error if
you try to add a job that already exists so why shouldn't the check be
there when you generate the script?<br>
<br>
<br>
-- <br>
Regards<br>
Steen Schlüter Persson<br>
Databaseadministrator / Systemadministrator<br>
</font></font>
</body>
</html>
--090000090502030800020104--|||ameyer2@.yahoo.com wrote:
> Hello Steen. Thank you for the reply
> Steen Persson (DK) wrote:
>> Why don't you just create a DTS package and then run this package from
>> SQL server agent?
> Thanks for the suggestion. I will look into that.
>> I must admit that I'm not quite sure what your problem is because I've
>> used SQL agent for years for many different purposes and I haven't
>> really seen that it add things I haven't asked for.
> I presume you have mainly used the Agent GUI.
> To see what I'm talking about, pick one of your Agent jobs
> and export it as a text script, e.g.,
> Open Agent.
> Select any job
> Right click on the job and select "All Tasks" / "Generate SQL
> Script..."
> Then examine what you get.
> If you write your own script and submit it to SQL Server without
> using the GUI front end, you can get your new job to show up in
> the GUI list of jobs. However if you then use the GUI to generate
> a script from it and compare what was generated to what you
> input, you might be surprised. I was.
> Alan
>
Perhaps you should analyze that additional code to determine what it's
doing? Maybe you're missing a step in your own scripts?
--
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Tracy McKibben wrote:
> Perhaps you should analyze that additional code to determine what it's
> doing? Maybe you're missing a step in your own scripts?
>
I have indeed analyzed it. Some of what is there is stuff that either
I don't need because I've done the same thing another way, or
because I just don't need it.
Some is questionable. I'm not in front of SQL Server right now,
but as I recall, it wraps my entire job step in a transaction. That
may be right in the average case but wrong in particular cases
where it could defeat the intent of the programmer.
I can see why this approach by Microsoft has its fans. I'm
just not one of them.
But I didn't create this thread for the purpose of flaming
Microsoft. I recognize the MS has produced many fine
programs, SQL Server among them. I just wanted to know
if anyone knew of any documentation of the things MS does
under the covers of the Agent GUI.
I did find one source of information. See my first posting on
July 19 for the reference. If anyone knows of any other
documentation I'd love to see it.
Thanks.
Alan|||This is a multi-part message in MIME format.
--060304080400040803020408
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
ameyer2@.yahoo.com wrote:
> Tracy McKibben wrote:
>
>> Perhaps you should analyze that additional code to determine what it's
>> doing? Maybe you're missing a step in your own scripts?
>>
> I have indeed analyzed it. Some of what is there is stuff that either
> I don't need because I've done the same thing another way, or
> because I just don't need it.
> Some is questionable. I'm not in front of SQL Server right now,
> but as I recall, it wraps my entire job step in a transaction. That
> may be right in the average case but wrong in particular cases
> where it could defeat the intent of the programmer.
> I can see why this approach by Microsoft has its fans. I'm
> just not one of them.
> But I didn't create this thread for the purpose of flaming
> Microsoft. I recognize the MS has produced many fine
> programs, SQL Server among them. I just wanted to know
> if anyone knew of any documentation of the things MS does
> under the covers of the Agent GUI.
> I did find one source of information. See my first posting on
> July 19 for the reference. If anyone knows of any other
> documentation I'd love to see it.
> Thanks.
> Alan
>
You don't honestly expect that SQL server will produce a script that you
can just dump into some of you own code where you already has done a lot
of the checking that the script include?
When you right click on a job and script it out, SQL server can't do
anything else than produce a "stand-alone" script that can be run like
it is on it's own and then create the job you're asking for. For that
purpose I think it do the job very well and it include the code
necessary to do the job. This will (of course) also include a number of
checks and therefore also wrap it into a transaction that can be rolled
back in case of a failure.
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator
--060304080400040803020408
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=mailto:ameyer2@.yahoo.com">ameyer2@.yahoo.com</a> wrote:
<blockquote
cite="mid1153622611.152439.140660@.75g2000cwc.googlegroups.com"
type="cite">
<pre wrap="">Tracy McKibben wrote:
</pre>
<blockquote type="cite">
<pre wrap="">Perhaps you should analyze that additional code to determine what it's
doing? Maybe you're missing a step in your own scripts?
</pre>
</blockquote>
<pre wrap=""><!-->I have indeed analyzed it. Some of what is there is stuff that either
I don't need because I've done the same thing another way, or
because I just don't need it.
Some is questionable. I'm not in front of SQL Server right now,
but as I recall, it wraps my entire job step in a transaction. That
may be right in the average case but wrong in particular cases
where it could defeat the intent of the programmer.
I can see why this approach by Microsoft has its fans. I'm
just not one of them.
But I didn't create this thread for the purpose of flaming
Microsoft. I recognize the MS has produced many fine
programs, SQL Server among them. I just wanted to know
if anyone knew of any documentation of the things MS does
under the covers of the Agent GUI.
I did find one source of information. See my first posting on
July 19 for the reference. If anyone knows of any other
documentation I'd love to see it.
Thanks.
Alan
</pre>
</blockquote>
<font size="-1"><font face="Arial">You don't honestly expect that SQL
server will produce a script that you can just dump into some of you
own code where you already has done a lot of the checking that the
script include? <br>
When you right click on a job and script it out, SQL server can't do
anything else than produce a "stand-alone" script that can be run like
it is on it's own and then create the job you're asking for. For that
purpose I think it do the job very well and it include the code
necessary to do the job. This will (of course) also include a number of
checks and therefore also wrap it into a transaction that can be rolled
back in case of a failure.<br>
<br>
<br>
-- <br>
Regards<br>
Steen Schlüter Persson<br>
Databaseadministrator / Systemadministrator<br>
<br>
</font></font>
</body>
</html>
--060304080400040803020408--|||Steen Persson (DK) wrote:
...
> You don't honestly expect that SQL server will produce a script that you
> can just dump into some of you own code where you already has done a lot
> of the checking that the script include?
...
Well, like I say, I'm not here to flame Microsoft or say that what
they've done doesn't meet most people's needs.
For myself, I would like to be able to submit a script to Agent and
have some assurance that what I submitted is exactly what gets
done. If the script has detectable errors, I'd like Agent to report
them to me rather than modify the script.
It may be that Agent does exactly what I tell it to do. Or it may be
that it reads my script, extracts things from it, adds whatever it
wants, and throws my original script away.
Even though I wouldn't like that behavior, it would be fine if
it were documented. My problem, the one that I posted
to start this thread, is that I couldn't find any documentation.
I don't even know whether the script Agent
generates is what it executes, or whether it executes my
script just as I submitted it and generates the script it
generates just for my benefit if I want to use it as the basis
for some other job.
I'm just looking for serious documentation - something beyond
"to do this - click that".
Alan
Friday, February 24, 2012
Global ODBC Settings
Thanks
TKE402
Sunday, February 19, 2012
Giving users specific DDL permissions
At the beginning of the process the triggers and indexes on the
tables whose data is moved are dropped, the data is moved and then the
triggers and indexes are recreated at the end. This produces a
massive improvement in performance.
The problem is the process is supposed to run on users accounts (thats
the way the front-end is set up) and they don't have the neccessary
permissions to drop & create triggers & indexes. I can't see any way
to give them permissions only on specific tables or triggers/indexes.
Nor does giving them permissions to the stored procedures that do the
dropping & re-creating work, DDL permissions don't seem to be
inherited the way they are with tables.
Is blanket rights to drop & create objects through the db_ddladmin
role the only way users can get rights?
Thanks,
K FineganK Finegan (KevinFinegan@.Hotmail.com) writes:
> I have an archival process on a large database that runs once a month.
> At the beginning of the process the triggers and indexes on the
> tables whose data is moved are dropped, the data is moved and then the
> triggers and indexes are recreated at the end. This produces a
> massive improvement in performance.
> The problem is the process is supposed to run on users accounts (thats
> the way the front-end is set up) and they don't have the neccessary
> permissions to drop & create triggers & indexes. I can't see any way
> to give them permissions only on specific tables or triggers/indexes.
> Nor does giving them permissions to the stored procedures that do the
> dropping & re-creating work, DDL permissions don't seem to be
> inherited the way they are with tables.
> Is blanket rights to drop & create objects through the db_ddladmin
> role the only way users can get rights?
In SQL2000, yes. The upcoming version of SQL Server has some more
possibilities.
As for the triggers, it's probably better to say ALTER TABLE DISABLE
TRIGGERS ALL, than to drop them. Not that this addresses the permissions
problem.
There is a way to have a trigger off-turnable by means of regular
permissions though. In the trigger body you do this:
IF object_id('tempdb..#reloading') IS NULL
... Trigger logic comes here.
In you process you would create this temp table. The test would save you
the logic of the trigger, but you may still have an overhead, because
SQL Server has to run the statement as if there trigger was active.
No, for indexes I don't have any tricks.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland, I like the trigger trick, will probably use it &
DISABLE which I didn't know about. Pity about the indexes. For the
moment I suppose I'll have to give one (trustworthy) user ddl_admin
rights.
K Finegan