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.
>