Solved

Trying to kill suspened job without rebooting the server

Posted on 2010-11-19
29
2,304 Views
Last Modified: 2012-08-14
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
0
Comment
Question by:smythsit
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 10
  • 9
29 Comments
 
LVL 10

Expert Comment

by:Humpdy
ID: 34171116
You can do

KILL (SPID)
0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34171122
you definetely don't need to restart the server btw, forgot to mention that
0
 

Author Comment

by:smythsit
ID: 34171125
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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34171134
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
 
LVL 10

Expert Comment

by:Humpdy
ID: 34171137
ok, seems like you have some locking/blocking issues.
quickest way would be restart the SQL Service, not the server itself.
0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34171144
start, run,
type in
services.msc
look for SQL Server Service, right click....restart.
0
 
LVL 16

Accepted Solution

by:
EvilPostIt earned 250 total points
ID: 34171159
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
 

Author Comment

by:smythsit
ID: 34171166
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
 
LVL 10

Assisted Solution

by:Humpdy
Humpdy earned 250 total points
ID: 34171174
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
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34171198
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
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34171212
Have you run DBCC OPENTRAN?
0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34171231
bouce the service ... do it :-)

honestly, it's the quickest and best fix for this, you should be fine
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34171304
Humpdy, thats a bit of a reversal from your second post ;)
0
 

Author Comment

by:smythsit
ID: 34171352
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
 
LVL 10

Expert Comment

by:Humpdy
ID: 34171358
EvilPost; I said definetely no need to restart the Server, not the Service :-)
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34171384
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
 
LVL 10

Expert Comment

by:Humpdy
ID: 34171391
I know :-)

DBCC OPENTRAN

you find the estimations from this accurate ?
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34171403
Yeah, as it interogates the transaction log. I havent had any issues with it in the past. You?
0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34171410
only good, in that the estimations have been over estimated...sometimes but 50 or 75%.
0
 

Author Comment

by:smythsit
ID: 34171423
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
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34171425
Sorry but what estimations are you refering to. It only shows information regarding the oldest transaction?
0
 

Author Closing Comment

by:smythsit
ID: 34171434
Thanks guys
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34171445
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
 

Author Comment

by:smythsit
ID: 34171450
Great will do. Thanks again
0
 

Author Comment

by:smythsit
ID: 34171851
Sorry guys, is there any way to estimate how long the system might be down for if i leave the restart till tonight?
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34171857
Nope, sorry.
0
 

Author Comment

by:smythsit
ID: 34171858
Ok thanks
0
 

Author Comment

by:smythsit
ID: 34172449
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
 

Author Comment

by:smythsit
ID: 34173573
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

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Stored Proc - Rewrite 42 77
sql query 5 57
Enabling flash installation using GPO 2 53
get count of orders by customer Sql Server table. 3 47
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question