Force Kill a session?

Question

Force Kill a session?

newbieuser

SSCrazy Eights

Points: 9423
More actions
May 8, 2009 at 7:57 am

#130693

Hi all,
I need to bounce the database and I'm stopping all the services connected to the database. I'm trying to kill a session as
kill 105 with statusonly
I get this message:
SPID 105: transaction rollback in progress. Estimated rollback completion: 13%. Estimated time remaining: 4730167 seconds.
I ran sp_who and the status for this session is suspended and the cmd is KILLED/ROLLBACK.
Is there a way to force the kill command?? Thanks for your help

Viewing 15 posts - 1 through 15 (of 33 total)

You must be logged in to reply to this topic. Login to reply

David-1011936 Ten Centuries Points: 1378 More actions · Answer 1

psangeetha (5/8/2009)
Hi all,
I need to bounce the database and I'm stopping all the services connected to the database. I'm trying to kill a session as
kill 105 with statusonly
I get this message:
SPID 105: transaction rollback in progress. Estimated rollback completion: 13%. Estimated time remaining: 4730167 seconds.
I ran sp_who and the status for this session is suspended and the cmd is KILLED/ROLLBACK.
Is there a way to force the kill command?? Thanks for your help

Let the process continue the rollback. If you stop the service now, the database will come up in suspect state until the rollback completes. One way or the other, the rollback has to finish.

newbieuser SSCrazy Eights Points: 9423 More actions · Answer 2

But, the process is in suspended state since yesterday and I just ran the command again and the estimated rollback completion is still 13%. Is there a way to make this process run?

Thanks

David-1011936 Ten Centuries Points: 1378 More actions · Answer 3

psangeetha (5/8/2009)
But, the process is in suspended state since yesterday and I just ran the command again and the estimated rollback completion is still 13%. Is there a way to make this process run?
Thanks

Are there any transactions open that would be causing the rollback to not be able to complete?

newbieuser SSCrazy Eights Points: 9423 More actions · Answer 4

I only 2 processes open for this database with same Process ID. The status for both are suspended and the command is KILLED/ROLLBACK. Application is SQLAgent-TSQL JobStep (Job .... :Step 1) , Wait_type for the 1st process is BACKUPTHREAD and 2nd process is IO_COMPLETION. Both the processes(same process id) are from last night's scheduled backup job.

Thanks again

David-1011936 Ten Centuries Points: 1378 More actions · Answer 5

psangeetha (5/8/2009)
I only 2 processes open for this database with same Process ID. The status for both are suspended and the command is KILLED/ROLLBACK. Application is SQLAgent-TSQL JobStep (Job .... :Step 1) , Wait_type for the 1st process is BACKUPTHREAD and 2nd process is IO_COMPLETION. Both the processes(same process id) are from last night's scheduled backup job.
Thanks again

I think at this point I would go ahead and bounce it. The key will be to keep a close eye on the SQL log when the service restarts, to see what it does with the database.

newbieuser SSCrazy Eights Points: 9423 More actions · Answer 6

I stopped all the software services and tried to bring the db offline. I get this message, Alter Database failed because a lock could not be placed on database 'wbs2007pg' Try again later.

Thanks for your help

David-1011936 Ten Centuries Points: 1378 More actions · Answer 7

Just to be clear, you stopped and then re-started the SQL service, right?

What is the SQL log saying? Is the database marked as suspect?

newbieuser SSCrazy Eights Points: 9423 More actions · Answer 8

No.. I just tried to bounce the database. There are 7 other databases that are being used on the same server, so I wont be able to restart the service now, is it the SQL Server service?

Thanks

David-1011936 Ten Centuries Points: 1378 More actions · Answer 9

psangeetha (5/8/2009)
No.. I just tried to bounce the database. There are 7 other databases that are being used on the same server, so I wont be able to restart the service now, is it the SQL Server service?
Thanks

If the database is still trying to go through a roll-back, you probably won't be able to take it off-line. Yes, the SQL Server service is the one you would want to be looking at, when the time is correct.

I would guess that when SQL restarts, there's a pretty good chance that database will be marked suspect, and SQL will try to restore it at that time.

newbieuser SSCrazy Eights Points: 9423 More actions · Answer 10

Okay, if this is the only option then I should probably let everyone know that the databases will go down and bounce the SQL server service.

After the restart, if the database is in suspect mode, is there anything that I need to do?

Thanks for your help.

David-1011936 Ten Centuries Points: 1378 More actions · Answer 11

psangeetha (5/8/2009)
Okay, if this is the only option then I should probably let everyone know that the databases will go down and bounce the SQL server service.
After the restart, if the database is in suspect mode, is there anything that I need to do?
Thanks for your help.

Watch the SQL log for messages as to what it's doing with the database. It should have messages where it will indicate what progress it is making in bringing the database online, etc. I would monitor that first, see how SQL handles it; after that it will be a little clearer as to what step to take next.

newbieuser SSCrazy Eights Points: 9423 More actions · Answer 12

David,

I tried to open the Log file from SSMS, I get this message

THe file can not be opened because it is being used by another process. Please close all applications that might access this file and try again.

IS there any other log file to look at?

Thanks

newbieuser SSCrazy Eights Points: 9423 More actions · Answer 13

I just looked at SQL server error log on the SSMS, I see this message for this SPID:

The operating system returned error 1450(insufficient system resources exist to complete the requested service.) to SQL Server during a write at offset .. in file with handle .. This is usually a temporary condition and the SQL Server will keep retrying the operation. If the condition persists then immediate action must be taken to correct it.

This tells me it is a resource issue. But the backups for the other databases are running fine every night, just for this database I see this message in the error log from 05/01 with the same SPID. The backup probably failed from 05/01 and SQl Server is still re-trying it?

David-1011936 Ten Centuries Points: 1378 More actions · Answer 14

psangeetha (5/8/2009)
I just looked at SQL server error log on the SSMS, I see this message for this SPID:
The operating system returned error 1450(insufficient system resources exist to complete the requested service.) to SQL Server during a write at offset .. in file with handle .. This is usually a temporary condition and the SQL Server will keep retrying the operation. If the condition persists then immediate action must be taken to correct it.
This tells me it is a resource issue. But the backups for the other databases are running fine every night, just for this database I see this message in the error log from 05/01 with the same SPID. The backup probably failed from 05/01 and SQl Server is still re-trying it?

There are a couple of things you could do at this point:

-- make sure that you know where the backup of the database is supposed to be going, that there's more than enough room there for the backup, and that the SQL account has sufficient privileges for that location. Normally (hopefully) this is somewhere on a local drive for the SQL box, which can then be copied to other network locations if needed.

-- run a backup for that database manually, to see what is happening. You should be able to script out the backup job and run it in Query Analyzer, which would give you a good window into what is happening. If that works, then it may indeed be a permissions issue of some type.