Scheduling a job more frequently than every minute

I have a SQL Agent job that is scheduled to run every minute. Works like a
charm, however, my client would like it to run more frequently, like every 15
seconds. I'm pretty sure this cannot be scheduled in the SQL Agent, but I'm
equally sure it can be done. I just don't know the right verbiage or
commands to use or how to execute it. Any help would be greatly appreciated.
Thanks in advance.
d
http://sqldev.net/sqlagent/SQLAgentRecuringJobsInSecs.htm
Above is for 2000. I haven't tried it on 2005.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Darlene Mack" <DarleneMack@.discussions.microsoft.com> wrote in message
news:0A8AD7BE-245F-4912-8FC5-9BDE45D389A1@.microsoft.com...
>I have a SQL Agent job that is scheduled to run every minute. Works like a
> charm, however, my client would like it to run more frequently, like every 15
> seconds. I'm pretty sure this cannot be scheduled in the SQL Agent, but I'm
> equally sure it can be done. I just don't know the right verbiage or
> commands to use or how to execute it. Any help would be greatly appreciated.
> Thanks in advance.
>
> d
|||Thanks!!!!
"Tibor Karaszi" wrote:

> http://sqldev.net/sqlagent/SQLAgentRecuringJobsInSecs.htm
> Above is for 2000. I haven't tried it on 2005.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Darlene Mack" <DarleneMack@.discussions.microsoft.com> wrote in message
> news:0A8AD7BE-245F-4912-8FC5-9BDE45D389A1@.microsoft.com...
>
>

Scheduling a job more frequently than every minute

I have a SQL Agent job that is scheduled to run every minute. Works like a
charm, however, my client would like it to run more frequently, like every 1
5
seconds. I'm pretty sure this cannot be scheduled in the SQL Agent, but I'm
equally sure it can be done. I just don't know the right verbiage or
commands to use or how to execute it. Any help would be greatly appreciated
.
Thanks in advance.
dhttp://sqldev.net/sqlagent/SQLAgent...gJobsInSecs.htm
Above is for 2000. I haven't tried it on 2005.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Darlene Mack" <DarleneMack@.discussions.microsoft.com> wrote in message
news:0A8AD7BE-245F-4912-8FC5-9BDE45D389A1@.microsoft.com...
>I have a SQL Agent job that is scheduled to run every minute. Works like a
> charm, however, my client would like it to run more frequently, like every
15
> seconds. I'm pretty sure this cannot be scheduled in the SQL Agent, but I
'm
> equally sure it can be done. I just don't know the right verbiage or
> commands to use or how to execute it. Any help would be greatly appreciat
ed.
> Thanks in advance.
>
> d|||Thanks!!!!
"Tibor Karaszi" wrote:

> http://sqldev.net/sqlagent/SQLAgent...gJobsInSecs.htm
> Above is for 2000. I haven't tried it on 2005.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Darlene Mack" <DarleneMack@.discussions.microsoft.com> wrote in message
> news:0A8AD7BE-245F-4912-8FC5-9BDE45D389A1@.microsoft.com...
>
>

Scheduling a job more frequently than every minute

I have a SQL Agent job that is scheduled to run every minute. Works like a
charm, however, my client would like it to run more frequently, like every 15
seconds. I'm pretty sure this cannot be scheduled in the SQL Agent, but I'm
equally sure it can be done. I just don't know the right verbiage or
commands to use or how to execute it. Any help would be greatly appreciated.
Thanks in advance.
dhttp://sqldev.net/sqlagent/SQLAgentRecuringJobsInSecs.htm
Above is for 2000. I haven't tried it on 2005.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Darlene Mack" <DarleneMack@.discussions.microsoft.com> wrote in message
news:0A8AD7BE-245F-4912-8FC5-9BDE45D389A1@.microsoft.com...
>I have a SQL Agent job that is scheduled to run every minute. Works like a
> charm, however, my client would like it to run more frequently, like every 15
> seconds. I'm pretty sure this cannot be scheduled in the SQL Agent, but I'm
> equally sure it can be done. I just don't know the right verbiage or
> commands to use or how to execute it. Any help would be greatly appreciated.
> Thanks in advance.
>
> d|||Thanks!!!!
"Tibor Karaszi" wrote:
> http://sqldev.net/sqlagent/SQLAgentRecuringJobsInSecs.htm
> Above is for 2000. I haven't tried it on 2005.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Darlene Mack" <DarleneMack@.discussions.microsoft.com> wrote in message
> news:0A8AD7BE-245F-4912-8FC5-9BDE45D389A1@.microsoft.com...
> >I have a SQL Agent job that is scheduled to run every minute. Works like a
> > charm, however, my client would like it to run more frequently, like every 15
> > seconds. I'm pretty sure this cannot be scheduled in the SQL Agent, but I'm
> > equally sure it can be done. I just don't know the right verbiage or
> > commands to use or how to execute it. Any help would be greatly appreciated.
> >
> > Thanks in advance.
> >
> >
> > d
>
>

Scheduler node deadlock message.

Hi:

We seem to be hitting a performance problem on SQL Server 2005. We encounter the following message frequently in the event log. We have update stats, reindexing and everything else in place. However this message keeps getting logged.

All schedulers on Node 0 appear deadlocked due to a large number
of worker threads waiting on LCK_M_IS. Process Utilization 25%%

MS Experts (Product Gurus)/MVPs can you please offer some explanation or insight into the error and also a possible resolution on how to fix it. I have the following questions:

1). Is this related to poor queries performing badly and needs to be tuned?.

2). Is this a more DIsk I/O Issue and whether we need faster disks or disk arrays?

3). Is something configured wrongly on the SQL Server?

4). Are we handling our transactions badly and thus resulting in blocking or deadlocks?.

Please provide your valuable suggestions.

Thanks

Ankith

I forgot to mention the system configuration.

The Server is Windows Server 2003 (x64 Edition) with AMD Quad (4) Processors (2.2 GHZ) and has apprx 16 GB of RAM.

Awe is enabled.

|||

This message indicates that there are some pretty long blocking chains on your server.

Have a look at the blocking section in http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx for methods of troubleshooting blocking issues.

|||

Hi Jerome:

Thanks for the info. Is this error anyway related to Hard disk contention (because I see worker threads waiting on LCK_M_IS). Just want to get your opinion and see if we can also take a look at our hard disks.

Thanks.

|||

Based on the wait type it's highly unlikely that disk issues have anything to do with the problem. Start by looking at the connection that's holding incompatible lock and determine what it's doing.

sql

Scheduled job fails because locks were not released on the table

Hi,
I have a scheduled job that has started failing frequently because of a user
who only works on the weekends. Even though the user stops working on the
application, somehow the locks stay on the table making the scheduled job to
fail. Is there a way to release the locks on a particular table through TSQL
before the job runs so that the job gets completed successfully?Not some general way. You could use procedures such as sp_who, sp_lock, sp_who2 etc to find the SPID
you need to get rid of and then use the KILL command to force a rollback and termination of the
connection in question. Another option is to set the database to single user and specify a ROLLBACK
option (see ALTER DATABASE).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"ronnie" <ronnie@.discussions.microsoft.com> wrote in message
news:DDE09196-44F6-4681-BD95-40472FB8112A@.microsoft.com...
> Hi,
> I have a scheduled job that has started failing frequently because of a user
> who only works on the weekends. Even though the user stops working on the
> application, somehow the locks stay on the table making the scheduled job to
> fail. Is there a way to release the locks on a particular table through TSQL
> before the job runs so that the job gets completed successfully?|||Well, what exactly is the user doing to lock the table? What do they do
when they "stop working" on it?
"ronnie" <ronnie@.discussions.microsoft.com> wrote in message
news:DDE09196-44F6-4681-BD95-40472FB8112A@.microsoft.com...
> Hi,
> I have a scheduled job that has started failing frequently because of a
> user
> who only works on the weekends. Even though the user stops working on the
> application, somehow the locks stay on the table making the scheduled job
> to
> fail. Is there a way to release the locks on a particular table through
> TSQL
> before the job runs so that the job gets completed successfully?|||The user works remotely and connects to the application through a VPN. I am
trying to find out from the user how he logs off after he stops working.
Maybe he doesn't even logs off and leave the application open on the machine
and just closes the VPN connection. I will post the answer as soon as I hear
from the user.
As this job runs during the night, I can get the spid from the username and
then put in a TSQL command to kill the spid/s created by this user so that
the locks get released from the table.
"Aaron Bertrand [SQL Server MVP]" wrote:
> Well, what exactly is the user doing to lock the table? What do they do
> when they "stop working" on it?
>
> "ronnie" <ronnie@.discussions.microsoft.com> wrote in message
> news:DDE09196-44F6-4681-BD95-40472FB8112A@.microsoft.com...
> > Hi,
> >
> > I have a scheduled job that has started failing frequently because of a
> > user
> > who only works on the weekends. Even though the user stops working on the
> > application, somehow the locks stay on the table making the scheduled job
> > to
> > fail. Is there a way to release the locks on a particular table through
> > TSQL
> > before the job runs so that the job gets completed successfully?
>|||I found out from the user that while running an update on the table, his
machine froze and he wasn't able to do anything afterwards. Since the machine
is at a remote location and he is connecting through VPN he wasn't even able
to reboot the machine. This might have caused the locks to remain on this
table.
What should be done to handle situations like this to prevent future
scheduled job failures?
"ronnie" wrote:
> The user works remotely and connects to the application through a VPN. I am
> trying to find out from the user how he logs off after he stops working.
> Maybe he doesn't even logs off and leave the application open on the machine
> and just closes the VPN connection. I will post the answer as soon as I hear
> from the user.
> As this job runs during the night, I can get the spid from the username and
> then put in a TSQL command to kill the spid/s created by this user so that
> the locks get released from the table.
> "Aaron Bertrand [SQL Server MVP]" wrote:
> > Well, what exactly is the user doing to lock the table? What do they do
> > when they "stop working" on it?
> >
> >
> >
> > "ronnie" <ronnie@.discussions.microsoft.com> wrote in message
> > news:DDE09196-44F6-4681-BD95-40472FB8112A@.microsoft.com...
> > > Hi,
> > >
> > > I have a scheduled job that has started failing frequently because of a
> > > user
> > > who only works on the weekends. Even though the user stops working on the
> > > application, somehow the locks stay on the table making the scheduled job
> > > to
> > > fail. Is there a way to release the locks on a particular table through
> > > TSQL
> > > before the job runs so that the job gets completed successfully?
> >|||> What should be done to handle situations like this to prevent future
> scheduled job failures?
Well, what I was trying to get at what was, what exactly is the user doing
to hold locks on the table in the first place? Using what app(s)? Is he
opening data in a grid? Ideally he should be submitting short transactions
(using stored procedures or insert/update statements) and should not be as
exposed to the risk of connection interruptions. If this has happened more
than once then my guess is his data manipulation techniques are not ideal.|||The user was working on an Microsoft Access application that uses SQL Server
database as the backend. This user did an update / insert into a table using
an Access Form and while he was doing that using VPN and remote desktop his
machine froze and the connection broke down. This somehow created the
situation where the entire table got locked and the connection to SQL Server
persisted even though the user's machine has frozen.
"Aaron Bertrand [SQL Server MVP]" wrote:
> > What should be done to handle situations like this to prevent future
> > scheduled job failures?
> Well, what I was trying to get at what was, what exactly is the user doing
> to hold locks on the table in the first place? Using what app(s)? Is he
> opening data in a grid? Ideally he should be submitting short transactions
> (using stored procedures or insert/update statements) and should not be as
> exposed to the risk of connection interruptions. If this has happened more
> than once then my guess is his data manipulation techniques are not ideal.
>