Solved

Trying to kill suspened job without rebooting the server

Posted on 2010-11-19
29
2,231 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
  • 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
 
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Sql Permission 6 43
Truncate vs Delete 63 88
Dynamically create stored procedure   using c# 9 77
CREATE DATABASE ENCRYPTION KEY 1 45
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now