Trying to kill suspened job without rebooting the server

HI,

We're trying to kill a suspended job without having to reboot the server because it is the production database.

We ran a query yesterday to backup the database to a mapped drive but then cancelled the query.

The task state of the job has been suspended now for 24 hours and the command is KILLED/ROLLBACK

Do we have to restart the server to kill this process?

And if so, is there any risk invloved in the database starting up again
smythsitAsked:
Who is Participating?
 
EvilPostItConnect With a Mentor Commented:
Oh yeah also BTW. The reason that the system was down for 4 hours was because when the database came back up it had to roll forward the outstanding transactions in the transaction log.

So dont be surpised if the user database takes a little while to come back up.
0
 
HumpdyCommented:
You can do

KILL (SPID)
0
 
HumpdyCommented:
you definetely don't need to restart the server btw, forgot to mention that
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
smythsitAuthor Commented:
When i do kill 159 i get the following message


SPID 159: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 7398180 seconds.
0
 
EvilPostItCommented:
You may have to restart this. I have had proccesses before that we just not rolling back.

Use the following command to find out the oldest transaction

DBCC OPENTRAN

Open in new window


I have had an issue in the past where a process was rolling back for a few days. As this process had effectivly stop the transaction log from being able to clear down old records as it cannot get past the transaction which is trying to roll back.

Thus every transaction log backup will not actually be backup up any data and the log will just be growing. You may want to check your backups too as they may not have been done last night due to a backup already running.

The problem it caused actually took the system down for 4 hours (mainly due to the size of the log).



0
 
HumpdyCommented:
ok, seems like you have some locking/blocking issues.
quickest way would be restart the SQL Service, not the server itself.
0
 
HumpdyCommented:
start, run,
type in
services.msc
look for SQL Server Service, right click....restart.
0
 
smythsitAuthor Commented:
Yes the backup didn't run lastnight because this was still running.


Is there any other way besides restarting the sql service? Production server so would have to do this out of hours.

If we have to restart it is there any risk to the database when the service starts again?

Thanks
0
 
HumpdyConnect With a Mentor Commented:
As EvilPost mentioned, risk could be that the database may take a while to roll forward transactions.
what happens when you run
select * from sys.dm_tran_locks
0
 
EvilPostItCommented:
Unless you have had issues with your disk sub-system i would assume that the database will be ok. But anything can happen. Sorry i know thats a bit of a flacky answer but its the truth.
0
 
EvilPostItCommented:
Have you run DBCC OPENTRAN?
0
 
HumpdyCommented:
bouce the service ... do it :-)

honestly, it's the quickest and best fix for this, you should be fine
0
 
EvilPostItCommented:
Humpdy, thats a bit of a reversal from your second post ;)
0
 
smythsitAuthor Commented:
When i ran select * from sys.dm_tran_locks

these were in the results

Resource Type:                                      Resource_sbutype
database                                                 Bulkop_Backup_log
database                                                 Bulkop_Backup_DB

and below is the result of running DBCC OPENTRAN

Transaction information for database 'Smyths Toys'.

Oldest active transaction:
    SPID (server process ID): 87
    UID (user ID) : -1
    Name          : implicit_transaction
    LSN           : (69738:173408:247)
    Start time    : Nov 19 2010  8:19:37:443AM
    SID           : 0x010500000000000515000000859890766dffea8feb4c67edc5040000
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
0
 
HumpdyCommented:
EvilPost; I said definetely no need to restart the Server, not the Service :-)
0
 
EvilPostItCommented:
The DBCC OPENTRAN result doesnt look to bad. Its only an hour or so. So your database shouldnt take to long to come back up as there will only be an hour or so of transactions to roll forward. Have you restarted the SQL service yet?

Humpdy; was taking the mickey :-)
0
 
HumpdyCommented:
I know :-)

DBCC OPENTRAN

you find the estimations from this accurate ?
0
 
EvilPostItCommented:
Yeah, as it interogates the transaction log. I havent had any issues with it in the past. You?
0
 
HumpdyCommented:
only good, in that the estimations have been over estimated...sometimes but 50 or 75%.
0
 
smythsitAuthor Commented:
No i havn't restarted the service. I'll have to wait till tonight because we can't afford downtime during office hours.

Thanks for the advice anyway guys.

Hopefully it won't take too long to come back up tonight
0
 
EvilPostItCommented:
Sorry but what estimations are you refering to. It only shows information regarding the oldest transaction?
0
 
smythsitAuthor Commented:
Thanks guys
0
 
EvilPostItCommented:
Bear in mind it could have to roll forward all the transactions committed today. If you were to have some sort of failure and loose the log then you would loose everything after the last transaction log backup!

To me this would be an unacceptable risk and I would riase this to management. Let them take make the decision and take the heat if they decide no.

Just my opinion though. Good luck.
0
 
smythsitAuthor Commented:
Great will do. Thanks again
0
 
smythsitAuthor Commented:
Sorry guys, is there any way to estimate how long the system might be down for if i leave the restart till tonight?
0
 
EvilPostItCommented:
Nope, sorry.
0
 
smythsitAuthor Commented:
Ok thanks
0
 
smythsitAuthor Commented:
Hi Guys,

So here is the current story.

The Sql service can't be bounced until we get a full backup first. It's just too risky
We have completed a log file backup this morning with no issues.

We are going to do a database backup tonight - however the cause of all this mess was that we added a new drive (NAS Storage) to the server to receive the DB backup files as we were out of space on the local drive.
The DB is about 900GB
My plan is to split the DB backup into 30 files - each of 30GB
I have got the plan on the attached spreadsheet.

Is there a limit on the number of backup files we can use?
Does anybody see any reason why this backup plan would not work?
We normally split the backup into  files anyway and spread it over the A and B drives in the spreadsheet.

Note that the DB data files are on the drive letters f : u

thanks

EE-Backup-Plan.xlsx
0
 
smythsitAuthor Commented:
Hi

I think we've found the answer to both issues
http://connect.microsoft.com/SQLServer/feedback/details/428738/nativebackup-spid-get-in-to-suspended-mode-with-backupio-and-backupthread-as-last-wait-type-when-backup-process-get-intrrupted-in-sql-server-2008-rtm

It appears that the DB backup was caused by the above.
The Transaction is closed so a server restart will not cause a lengthy roll forward.

Thanks for all the help - we'll still do the file splitting once the DB is back up and running ok again
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.