killed/rollback stuck on object_name(99)

Hello:
I have a process that's been stuck for two days.. It's a stored procedure
that runs as part of a scheduled SqlAgent job. I tried to kill the process
which put it into a rollback. Kill with statusonly returns:
SPID 52: transaction rollback in progress. Estimated rollback completion:
0%. Estimated time remaining: 0 seconds.
I ran dbcc page for the resource that is listed in the wait type
(PAGEIOLATCH_UP)
and it points to Obj_id 99. Running "select object_name(99)" returns the
object name "Allocation".
Does anyone know what this means and how to allow the rollback to complete?
This spid blocks other processess that try to run in the affected database.
Even Enterprise Manager is blocked. Can't refresh table list or procedure
list in EM. Current activity times out. I can use sp_who2 to see active
processes.hi,
Right, try again using this:
KILL <your_process> WITH STATUSONLY
Because of your process has been running a long time the rollback will take
a lot of time (not the same, of course, but a lof anyway)
Rollback is undoing changes and transactions commited
Current location: Alicante (ES)
"tthrone" wrote:

> Hello:
> I have a process that's been stuck for two days.. It's a stored procedure
> that runs as part of a scheduled SqlAgent job. I tried to kill the proces
s
> which put it into a rollback. Kill with statusonly returns:
> SPID 52: transaction rollback in progress. Estimated rollback completion:
> 0%. Estimated time remaining: 0 seconds.
> I ran dbcc page for the resource that is listed in the wait type
> (PAGEIOLATCH_UP)
> and it points to Obj_id 99. Running "select object_name(99)" returns the
> object name "Allocation".
> Does anyone know what this means and how to allow the rollback to complete
?
> This spid blocks other processess that try to run in the affected database
.
> Even Enterprise Manager is blocked. Can't refresh table list or procedure
> list in EM. Current activity times out. I can use sp_who2 to see active
> processes.
>|||Hi Enric,
I did that. It returns:
> SPID 52: transaction rollback in progress. Estimated rollback completion:
> 0%. Estimated time remaining: 0 seconds.
>
It's been returning the same thing for two days. It doesn't appear to be
making any progress on the rollback. The original process should have done
123,000 row inserts on a previously empty table. I can't imagine 123k rows
should take 2 days to rollback. I think it's totally stuck and idle.
"Enric" wrote:
> hi,
> Right, try again using this:
> KILL <your_process> WITH STATUSONLY
> Because of your process has been running a long time the rollback will tak
e
> a lot of time (not the same, of course, but a lof anyway)
> Rollback is undoing changes and transactions commited
> --
> Current location: Alicante (ES)
>
> "tthrone" wrote:
>|||First, try to find out what application and T-SQL statement caused this
situation so perhaps it won't repeat:
DBCC INPUTBUFFER (spid) will display the last T-SQL statement sent by the
client application owning this SPID.
SP_LOCK (spid) will list information about what specific objects the SPID
currently has locked and what type of lock (table, page, etc.).
Next, try to diagnose what is going on with your server hard disks, memory,
etc. that may have caused this unusual cirsumstance. If the server is
running critically low on disk space, this can cause problems when
attempting rollback a large transaction. Also, go into the windows
management console and review the event logs for possible evidence.
This article describes how get more detailed information about the current
status of the SPID:
http://support.microsoft.com/defaul...kb;en-us;171224
For example, the Process Status Structure (PSS) has the following values:
0x4000 -- Delay KILL and ATTENTION signals if inside a critical section
0x2000 -- Process is being killed
0x800 -- Process is in backout, thus cannot be chosen as deadlock victim
0x400 -- Process has received an ATTENTION signal, and has responded by
raising an internal exception
0x100 -- Process in the middle of a single statement transaction
0x80 -- Process is involved in multi-database transaction
0x8 -- Process is currently executing a trigger
0x2 -- Process has received KILL command
0x1 -- Process has received an ATTENTION signal
This article describes how to identify and troubleshoot an orphaned
connection:
http://support.microsoft.com/kb/137983/EN-US/
If the SPID can't be killed, then:
1. stop the SQL Server service (no need to reboot)
2. using Windows Explorer, move the data and transaction log file(s) to
another location
3. re-start the service
4. restore the database from the most recent backup
"tthrone" <tthrone@.discussions.microsoft.com> wrote in message
news:D7361B71-3C5A-41CE-A4D0-68685B910E3E@.microsoft.com...
> Hello:
> I have a process that's been stuck for two days.. It's a stored procedure
> that runs as part of a scheduled SqlAgent job. I tried to kill the
> process
> which put it into a rollback. Kill with statusonly returns:
> SPID 52: transaction rollback in progress. Estimated rollback completion:
> 0%. Estimated time remaining: 0 seconds.
> I ran dbcc page for the resource that is listed in the wait type
> (PAGEIOLATCH_UP)
> and it points to Obj_id 99. Running "select object_name(99)" returns the
> object name "Allocation".
> Does anyone know what this means and how to allow the rollback to
> complete?
> This spid blocks other processess that try to run in the affected
> database.
> Even Enterprise Manager is blocked. Can't refresh table list or procedure
> list in EM. Current activity times out. I can use sp_who2 to see active
> processes.
>|||Thanks JT. I know some answers to questions/issues you listed. I know the
transation that was in-flight, but I don't know why it stuck. Still can't
figure out why it remains stuck, but I found something interesting in the
process of doing some of what you suggested.
For one, I see this spid blocks some of my attempts to use sysobjects. I
mentioned that I can't refresh procedures or tables in EM on this database.
I think that's why. I have it narrowed down to one (maybe a few) affected
tables. I can query sysobjects so long as I don't try to read certain rows.
Not sure how that happened!
I think I'm going to have to try your suggestion about stopping the service
and restoring.
Thanks for the help.
"JT" wrote:

> First, try to find out what application and T-SQL statement caused this
> situation so perhaps it won't repeat:
> DBCC INPUTBUFFER (spid) will display the last T-SQL statement sent by the
> client application owning this SPID.
> SP_LOCK (spid) will list information about what specific objects the SPID
> currently has locked and what type of lock (table, page, etc.).
> Next, try to diagnose what is going on with your server hard disks, memory
,
> etc. that may have caused this unusual cirsumstance. If the server is
> running critically low on disk space, this can cause problems when
> attempting rollback a large transaction. Also, go into the windows
> management console and review the event logs for possible evidence.
> This article describes how get more detailed information about the current
> status of the SPID:
> http://support.microsoft.com/defaul...kb;en-us;171224
> For example, the Process Status Structure (PSS) has the following values:
> 0x4000 -- Delay KILL and ATTENTION signals if inside a critical section
> 0x2000 -- Process is being killed
> 0x800 -- Process is in backout, thus cannot be chosen as deadlock victim
> 0x400 -- Process has received an ATTENTION signal, and has responded by
> raising an internal exception
> 0x100 -- Process in the middle of a single statement transaction
> 0x80 -- Process is involved in multi-database transaction
> 0x8 -- Process is currently executing a trigger
> 0x2 -- Process has received KILL command
> 0x1 -- Process has received an ATTENTION signal
> This article describes how to identify and troubleshoot an orphaned
> connection:
> http://support.microsoft.com/kb/137983/EN-US/
> If the SPID can't be killed, then:
> 1. stop the SQL Server service (no need to reboot)
> 2. using Windows Explorer, move the data and transaction log file(s) to
> another location
> 3. re-start the service
> 4. restore the database from the most recent backup
>
> "tthrone" <tthrone@.discussions.microsoft.com> wrote in message
> news:D7361B71-3C5A-41CE-A4D0-68685B910E3E@.microsoft.com...
>
>|||When querying sysobjects (or any other blocked table), you can get around
the locks by changing the isolation level to read uncommitted data. However,
this should not be used in a production system except perhaps in some
reporting situations.
set transaction isolation level read uncommitted
select * from sysobjects
"tthrone" <tthrone@.discussions.microsoft.com> wrote in message
news:1C4B57D4-1894-4688-8E32-6157E28AD503@.microsoft.com...
> Thanks JT. I know some answers to questions/issues you listed. I know
> the
> transation that was in-flight, but I don't know why it stuck. Still can't
> figure out why it remains stuck, but I found something interesting in the
> process of doing some of what you suggested.
> For one, I see this spid blocks some of my attempts to use sysobjects. I
> mentioned that I can't refresh procedures or tables in EM on this
> database.
> I think that's why. I have it narrowed down to one (maybe a few) affected
> tables. I can query sysobjects so long as I don't try to read certain
> rows.
> Not sure how that happened!
> I think I'm going to have to try your suggestion about stopping the
> service
> and restoring.
> Thanks for the help.
> "JT" wrote:
>|||I normally do set the transaction isolation level to read uncommitted. I di
d
that in this case as well.
I even tried using the hint "with(readuncommitted)" but it was still blocked
by the stuck spid when I tried to return the sysobject rows of tables that
were affected.
Our DBA is going to bounce the service later today. I'm hoping it will
clear up after the restart.
"JT" wrote:

> When querying sysobjects (or any other blocked table), you can get around
> the locks by changing the isolation level to read uncommitted data. Howeve
r,
> this should not be used in a production system except perhaps in some
> reporting situations.
> set transaction isolation level read uncommitted
> select * from sysobjects
> "tthrone" <tthrone@.discussions.microsoft.com> wrote in message
> news:1C4B57D4-1894-4688-8E32-6157E28AD503@.microsoft.com...
>
>

Kill cmd process started from SQL Server Agent

Hi!
I have a small problem , but it's still a problem.
I have a SQL Server Agent job that runs a .cmd file. This CMD is logged to a textfile.
This process is locked, waiting for me to type a password, but I have nowhere to type that pass.

What I want to do is kill the process that i locking the logfile, because since the logfile is locked, the job cannot be started again (and it's a scheduled job).
The jobs status is 'Not Running'.
I have solved the problem by making the cmd write to another logfile, so the schedule will work, but the file is still locked, and I don't want to restart the server since it's a productionserver.

How to I find the process that is initialized from SQL Agent, and kill it?

Thanks!

BixCould be you'll have a big problem...

what does the cmd file execute?

If it's ANY type of GUI you could hang the box...

What's in the cmd file?|||Nope, no GUI is executed.
It's juat a matter of reading textfiles, formatting the data and inserting it into the db.

The part that hangs is a "Net Use"-command for accessing a networkshare.
I have added the user and pass so that this does not happen again...|||I'm not sure about this, but I believe you will find cmdexec in your system processes. You can kill the PID and it should take care of you.|||killing the parent process without terminating the child may lead to system instability. cmdexec does not take care of anything that had been invoked from it.

Kill an user process with script

Hi all,
I am writing a script to kill any process connect to the user database before daily restore job in DR machine.

For testing, the script is getting the spid from sysprocesses and sysdatabases and store the specific spid which accessing the user DB into cursor and sp_who spid.
HOwever, if I change the sp_who to kill, it shows error. Anything wrong in such script? Thanks in advance

> I am writing a script to kill any process connect to the user database
> before daily restore job in DR machine. >
> For testing, the script is getting the spid from sysprocesses and
> sysdatabases and store the specific spid which accessing the user DB
> into cursor and sp_who spid. You're making this way harder than it has to be: USE master GO ALTER DATABASE db_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE

|||

Hi,

I have the same problem, but suggested solution wouldn't resolve it.

What I need is get 'spid' via 'sp_who' for the specific user where hostname is not on the list of machines allowed to connect using this account and then pass this/these spid(s) as a parameter(s) to KILL. This script planed to be executed as a sceduled job.

Thanks,

Leonid

|||

Try this

declare @.kill_stmt nvarchar(10), @.cntr int

declare @.kill_tbl table

(ident int identity(1,1),

spid int,

loginame nvarchar(128),

dbid int)

insert into @.kill_tbl(spid, loginame, dbid)

Select sp.spid, sp.loginame, sb.dbid

from master..sysprocesses sp

inner join master..sysdatabases sb on sp.dbid = sb.dbid

where sb.name = 'Test' --replace the database name with desired database name

set @.cntr = 1

while @.cntr <= (select max(ident) from @.kill_tbl)

begin

Select @.kill_stmt = 'KILL ' + convert(varchar, spid) from @.kill_tbl where ident = @.cntr

exec (@.kill_stmt)

select @.cntr = @.cntr + 1

end

This is kind of old but should work. If it works for you, you may want to put it in a stored procedure. A couple quick modifications and it should also work for Leonid.

One darwback is that if users a connecting and disconnecting. The spid may change between select and the kill. There may be other got ya's but it worked for what I needed it for.

Hope this works for you!

|||

This query can be used to get the list of connections that need to be killed except your connection

SELECT * FROM SYS.DM_EXEC_CONNECTIONS WHERE SESSION_ID<>@.@.SPID

|||

SELECT * FROM SYS.DM_EXEC_CONNECTIONS

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'SYS.DM_EXEC_CONNECTIONS'.

|||Thanks! This works just fine!

Kill an user process with script

Hi all,
I am writing a script to kill any process connect to the user database before daily restore job in DR machine.

For testing, the script is getting the spid from sysprocesses and sysdatabases and store the specific spid which accessing the user DB into cursor and sp_who spid.
HOwever, if I change the sp_who to kill, it shows error. Anything wrong in such script? Thanks in advance

> I am writing a script to kill any process connect to the user database
> before daily restore job in DR machine. >
> For testing, the script is getting the spid from sysprocesses and
> sysdatabases and store the specific spid which accessing the user DB
> into cursor and sp_who spid. You're making this way harder than it has to be: USE master GO ALTER DATABASE db_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE

|||

Hi,

I have the same problem, but suggested solution wouldn't resolve it.

What I need is get 'spid' via 'sp_who' for the specific user where hostname is not on the list of machines allowed to connect using this account and then pass this/these spid(s) as a parameter(s) to KILL. This script planed to be executed as a sceduled job.

Thanks,

Leonid

|||

Try this

declare @.kill_stmt nvarchar(10), @.cntr int

declare @.kill_tbl table

(ident int identity(1,1),

spid int,

loginame nvarchar(128),

dbid int)

insert into @.kill_tbl(spid, loginame, dbid)

Select sp.spid, sp.loginame, sb.dbid

from master..sysprocesses sp

inner join master..sysdatabases sb on sp.dbid = sb.dbid

where sb.name = 'Test' --replace the database name with desired database name

set @.cntr = 1

while @.cntr <= (select max(ident) from @.kill_tbl)

begin

Select @.kill_stmt = 'KILL ' + convert(varchar, spid) from @.kill_tbl where ident = @.cntr

exec (@.kill_stmt)

select @.cntr = @.cntr + 1

end

This is kind of old but should work. If it works for you, you may want to put it in a stored procedure. A couple quick modifications and it should also work for Leonid.

One darwback is that if users a connecting and disconnecting. The spid may change between select and the kill. There may be other got ya's but it worked for what I needed it for.

Hope this works for you!

|||

This query can be used to get the list of connections that need to be killed except your connection

SELECT * FROM SYS.DM_EXEC_CONNECTIONS WHERE SESSION_ID<>@.@.SPID

|||

SELECT * FROM SYS.DM_EXEC_CONNECTIONS

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'SYS.DM_EXEC_CONNECTIONS'.

|||Thanks! This works just fine!

Kill an user process with script

Hi all,
I am writing a script to kill any process connect to the user database before daily restore job in DR machine.

For testing, the script is getting the spid from sysprocesses and sysdatabases and store the specific spid which accessing the user DB into cursor and sp_who spid.
HOwever, if I change the sp_who to kill, it shows error. Anything wrong in such script? Thanks in advance

> I am writing a script to kill any process connect to the user database

> before daily restore job in DR machine.

>

> For testing, the script is getting the spid from sysprocesses and

> sysdatabases and store the specific spid which accessing the user DB

> into cursor and sp_who spid.

You're making this way harder than it has to be:

USE master

GO

ALTER DATABASE db_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE

|||

Hi,

I have the same problem, but suggested solution wouldn't resolve it.

What I need is get 'spid' via 'sp_who' for the specific user where hostname is not on the list of machines allowed to connect using this account and then pass this/these spid(s) as a parameter(s) to KILL. This script planed to be executed as a sceduled job.

Thanks,

Leonid

|||

Try this

declare @.kill_stmt nvarchar(10), @.cntr int

declare @.kill_tbl table

(ident int identity(1,1),

spid int,

loginame nvarchar(128),

dbid int)

insert into @.kill_tbl(spid, loginame, dbid)

Select sp.spid, sp.loginame, sb.dbid

from master..sysprocesses sp

inner join master..sysdatabases sb on sp.dbid = sb.dbid

where sb.name = 'Test' --replace the database name with desired database name

set @.cntr = 1

while @.cntr <= (select max(ident) from @.kill_tbl)

begin

Select @.kill_stmt = 'KILL ' + convert(varchar, spid) from @.kill_tbl where ident = @.cntr

exec (@.kill_stmt)

select @.cntr = @.cntr + 1

end

This is kind of old but should work. If it works for you, you may want to put it in a stored procedure. A couple quick modifications and it should also work for Leonid.

One darwback is that if users a connecting and disconnecting. The spid may change between select and the kill. There may be other got ya's but it worked for what I needed it for.

Hope this works for you!

|||

This query can be used to get the list of connections that need to be killed except your connection

SELECT * FROM SYS.DM_EXEC_CONNECTIONS WHERE SESSION_ID<>@.@.SPID

|||

SELECT * FROM SYS.DM_EXEC_CONNECTIONS

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'SYS.DM_EXEC_CONNECTIONS'.

|||Thanks! This works just fine!

Kill a job process

If you kill a job's process, is everything the job has
done rolled back ie is the whole job run in a
transaction? What about if the job used cursors?
TIA,
CB
No implicit transactions are created for jobs or job steps. It is entirely
your responsibility to handle transactions within a step and transactions
cannot span multiple steps.
If you need this functionality, consider using a multi-step DTS package.
Hope this helps.
Dan Guzman
SQL Server MVP
"CB" <anonymous@.discussions.microsoft.com> wrote in message
news:2a4101c4c1ae$d60fda90$a401280a@.phx.gbl...
> If you kill a job's process, is everything the job has
> done rolled back ie is the whole job run in a
> transaction? What about if the job used cursors?
> TIA,
> CB
|||Dan,
it appears that multi-step DTS package would also not do the trick. In
fact, even within a step in a multiple or single step DTS package, the
commit of transactions still obeys the general rule -- unless you define
explicitely, individual implicite transactions still would commit. Wouldn't
you agree?
Quentin
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OmOSNCbwEHA.3624@.TK2MSFTNGP09.phx.gbl...
> No implicit transactions are created for jobs or job steps. It is
entirely
> your responsibility to handle transactions within a step and transactions
> cannot span multiple steps.
> If you need this functionality, consider using a multi-step DTS package.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "CB" <anonymous@.discussions.microsoft.com> wrote in message
> news:2a4101c4c1ae$d60fda90$a401280a@.phx.gbl...
>
|||It is true that you need to explicitly issue a BEGIN TRAN and COMMIT (or
ROLLBACK) within the DTS package. However, unlike a SQL Agent job, the
steps within a DTS package can share the same database connection and
transaction. You can specify a DTS workflow so that the steps run in the
desired order. For example, you can create the following Execute SQL Tasks
using the same SQL Connection:
Execute SQL Task1:
BEGIN TRAN
Execute SQL Task2:
DELETE FROM MyTable
WHERE MyKey = 1
Execute SQL Task3:
INSERT INTO MyTable (MyKey)
VALUES(1)
Execute SQL Task4:
COMMIT
Hope this helps.
Dan Guzman
SQL Server MVP
"Quentin Ran" <removethis.qran2@.yahoo.com> wrote in message
news:uFFeJvbwEHA.3448@.TK2MSFTNGP10.phx.gbl...
> Dan,
> it appears that multi-step DTS package would also not do the trick. In
> fact, even within a step in a multiple or single step DTS package, the
> commit of transactions still obeys the general rule -- unless you define
> explicitely, individual implicite transactions still would commit.
> Wouldn't
> you agree?
> Quentin
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:OmOSNCbwEHA.3624@.TK2MSFTNGP09.phx.gbl...
> entirely
>
sql

Kill a job process

If you kill a job's process, is everything the job has
done rolled back ie is the whole job run in a
transaction? What about if the job used cursors?
TIA,
CBNo implicit transactions are created for jobs or job steps. It is entirely
your responsibility to handle transactions within a step and transactions
cannot span multiple steps.
If you need this functionality, consider using a multi-step DTS package.
Hope this helps.
Dan Guzman
SQL Server MVP
"CB" <anonymous@.discussions.microsoft.com> wrote in message
news:2a4101c4c1ae$d60fda90$a401280a@.phx.gbl...
> If you kill a job's process, is everything the job has
> done rolled back ie is the whole job run in a
> transaction? What about if the job used cursors?
> TIA,
> CB|||Dan,
it appears that multi-step DTS package would also not do the trick. In
fact, even within a step in a multiple or single step DTS package, the
commit of transactions still obeys the general rule -- unless you define
explicitely, individual implicite transactions still would commit. Wouldn't
you agree?
Quentin
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OmOSNCbwEHA.3624@.TK2MSFTNGP09.phx.gbl...
> No implicit transactions are created for jobs or job steps. It is
entirely
> your responsibility to handle transactions within a step and transactions
> cannot span multiple steps.
> If you need this functionality, consider using a multi-step DTS package.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "CB" <anonymous@.discussions.microsoft.com> wrote in message
> news:2a4101c4c1ae$d60fda90$a401280a@.phx.gbl...
>|||It is true that you need to explicitly issue a BEGIN TRAN and COMMIT (or
ROLLBACK) within the DTS package. However, unlike a SQL Agent job, the
steps within a DTS package can share the same database connection and
transaction. You can specify a DTS workflow so that the steps run in the
desired order. For example, you can create the following Execute SQL Tasks
using the same SQL Connection:
Execute SQL Task1:
BEGIN TRAN
Execute SQL Task2:
DELETE FROM MyTable
WHERE MyKey = 1
Execute SQL Task3:
INSERT INTO MyTable (MyKey)
VALUES(1)
Execute SQL Task4:
COMMIT
Hope this helps.
Dan Guzman
SQL Server MVP
"Quentin Ran" <removethis.qran2@.yahoo.com> wrote in message
news:uFFeJvbwEHA.3448@.TK2MSFTNGP10.phx.gbl...
> Dan,
> it appears that multi-step DTS package would also not do the trick. In
> fact, even within a step in a multiple or single step DTS package, the
> commit of transactions still obeys the general rule -- unless you define
> explicitely, individual implicite transactions still would commit.
> Wouldn't
> you agree?
> Quentin
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:OmOSNCbwEHA.3624@.TK2MSFTNGP09.phx.gbl...
> entirely
>

Kill a job process

If you kill a job's process, is everything the job has
done rolled back ie is the whole job run in a
transaction? What about if the job used cursors?
TIA,
CBNo implicit transactions are created for jobs or job steps. It is entirely
your responsibility to handle transactions within a step and transactions
cannot span multiple steps.
If you need this functionality, consider using a multi-step DTS package.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"CB" <anonymous@.discussions.microsoft.com> wrote in message
news:2a4101c4c1ae$d60fda90$a401280a@.phx.gbl...
> If you kill a job's process, is everything the job has
> done rolled back ie is the whole job run in a
> transaction? What about if the job used cursors?
> TIA,
> CB|||Dan,
it appears that multi-step DTS package would also not do the trick. In
fact, even within a step in a multiple or single step DTS package, the
commit of transactions still obeys the general rule -- unless you define
explicitely, individual implicite transactions still would commit. Wouldn't
you agree?
Quentin
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OmOSNCbwEHA.3624@.TK2MSFTNGP09.phx.gbl...
> No implicit transactions are created for jobs or job steps. It is
entirely
> your responsibility to handle transactions within a step and transactions
> cannot span multiple steps.
> If you need this functionality, consider using a multi-step DTS package.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "CB" <anonymous@.discussions.microsoft.com> wrote in message
> news:2a4101c4c1ae$d60fda90$a401280a@.phx.gbl...
> > If you kill a job's process, is everything the job has
> > done rolled back ie is the whole job run in a
> > transaction? What about if the job used cursors?
> > TIA,
> > CB
>|||It is true that you need to explicitly issue a BEGIN TRAN and COMMIT (or
ROLLBACK) within the DTS package. However, unlike a SQL Agent job, the
steps within a DTS package can share the same database connection and
transaction. You can specify a DTS workflow so that the steps run in the
desired order. For example, you can create the following Execute SQL Tasks
using the same SQL Connection:
Execute SQL Task1:
BEGIN TRAN
Execute SQL Task2:
DELETE FROM MyTable
WHERE MyKey = 1
Execute SQL Task3:
INSERT INTO MyTable (MyKey)
VALUES(1)
Execute SQL Task4:
COMMIT
Hope this helps.
Dan Guzman
SQL Server MVP
"Quentin Ran" <removethis.qran2@.yahoo.com> wrote in message
news:uFFeJvbwEHA.3448@.TK2MSFTNGP10.phx.gbl...
> Dan,
> it appears that multi-step DTS package would also not do the trick. In
> fact, even within a step in a multiple or single step DTS package, the
> commit of transactions still obeys the general rule -- unless you define
> explicitely, individual implicite transactions still would commit.
> Wouldn't
> you agree?
> Quentin
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:OmOSNCbwEHA.3624@.TK2MSFTNGP09.phx.gbl...
>> No implicit transactions are created for jobs or job steps. It is
> entirely
>> your responsibility to handle transactions within a step and transactions
>> cannot span multiple steps.
>> If you need this functionality, consider using a multi-step DTS package.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "CB" <anonymous@.discussions.microsoft.com> wrote in message
>> news:2a4101c4c1ae$d60fda90$a401280a@.phx.gbl...
>> > If you kill a job's process, is everything the job has
>> > done rolled back ie is the whole job run in a
>> > transaction? What about if the job used cursors?
>> > TIA,
>> > CB
>>
>

Kicking off a job on another SQL server

I have a job that runs daily that creates a backup and copies that backup
off to a second server. I currently have another job running on the second
server that restores that database locally on that server. I gave the job
on server 2 enough time where job 1 on server one will have enough time to
finish before job 2 starts. That being said, sometimes, job 1 runs kind of
close. I am not sure if this is possible, but I would like to create a step
in job 1 that would kick off job 2 on server 2. Any ideas if this can be
done?
AaronHi,
There is a command in Windows Resource Kit "RCMD" , which is used to execute
jobs / executables / batch files remotely.
Thanks
Hari
MCDBA
"Aaron" <amhigley@.hotmail.com> wrote in message
news:#05tLnRwDHA.1908@.TK2MSFTNGP10.phx.gbl...
> I have a job that runs daily that creates a backup and copies that backup
> off to a second server. I currently have another job running on the
second
> server that restores that database locally on that server. I gave the job
> on server 2 enough time where job 1 on server one will have enough time to
> finish before job 2 starts. That being said, sometimes, job 1 runs kind
of
> close. I am not sure if this is possible, but I would like to create a
step
> in job 1 that would kick off job 2 on server 2. Any ideas if this can be
> done?
> Aaron
>|||If you create a linked server, you could
EXEC linkedServerName.msdb..sp_start_job 'job_name'
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Aaron" <amhigley@.hotmail.com> wrote in message
news:#05tLnRwDHA.1908@.TK2MSFTNGP10.phx.gbl...
> I have a job that runs daily that creates a backup and copies that backup
> off to a second server. I currently have another job running on the
second
> server that restores that database locally on that server. I gave the job
> on server 2 enough time where job 1 on server one will have enough time to
> finish before job 2 starts. That being said, sometimes, job 1 runs kind
of
> close. I am not sure if this is possible, but I would like to create a
step
> in job 1 that would kick off job 2 on server 2. Any ideas if this can be
> done?
> Aaron
>

kick people logged on

Hi,

i have a job that restores a database every night. The job fails if someone is logged onto the database. Its a development database. I was wondering if there is a command that i can use to kick everyone off the database before the restore?

any help is appreciated.

Hi

We use the following:

USE Master

Go

ALTER DATABASE <DBName> SET SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

--RESTORE DATABASE

GO

ALTER DATABASE <dbName> SET MULTI_USER WITH ROLLBACK IMMEDIATE

Go

|||

Thanks. i think this will be helpful

what does 'with rollback immediate' do?

|||

Hi

WITH <termination>

Specifies when to roll back incomplete transactions when the database is transitioned from one state to another. (BOL)