SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Frozen Jobs, SPIDS, and Rollback


Frozen Jobs, SPIDS, and Rollback

Author
Message
Brandie Tarvin
Brandie Tarvin
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14787 Visits: 9002
Well this is annoying. Two jobs got stuck this morning in production (running for 3 hours). I stopped them some time ago, but the SPIDs are still showing as Suspended and Runnable (two lines on SP_WHO2 for both SPIDS).

I try to kill the SPIDS and get:


SPID 101: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.
SPID 97: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.


I ran sp_lock and got the following:



spid dbid ObjId IndId Type Resource Mode Status
97 55 0 0 DB S GRANT
97 55 0 0 DB U GRANT
97 55 0 0 DB [BULKOP_BACKUP_DB] U GRANT
97 55 0 0 DB [BULKOP_BACKUP_LOG] S GRANT
101 54 0 0 DB S GRANT
101 54 0 0 DB U GRANT
101 54 0 0 DB [BULKOP_BACKUP_DB] U GRANT
101 54 0 0 DB [BULKOP_BACKUP_LOG] S GRANT


The jobs stopped, and failed, like they were supposed to. I assume this means they rolled back. But this says there's a rollback remaining even though there's no estimated time left on the rollback clock...

Any thoughts?

EDIT: These were restore jobs. That's why the backup is involved.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88355 Visits: 45281
Restart SQL. I've seen this a couple times at a client, never dug out the root cause (I suspect there's some non-SQL resource being held somewhere).

Irritating, I know.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Brandie Tarvin
Brandie Tarvin
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14787 Visits: 9002
Gail,

I think I just found something. Read Jag's post here: http://social.msdn.microsoft.com/forums/en-US/sqldatabaseengine/thread/71d3cd0b-ce4d-4202-8e3b-38b38da90a5b. It sort of makes sense to me, but I'm wondering what else could be affected if I take this advice.

EDIT: I can't restart SQL. This is a production box.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88355 Visits: 45281
Brandie Tarvin (12/21/2011)
Gail,

I think I just found something. Read Jag's post here: http://social.msdn.microsoft.com/forums/en-US/sqldatabaseengine/thread/71d3cd0b-ce4d-4202-8e3b-38b38da90a5b. It sort of makes sense to me, but I'm wondering what else could be affected if I take this advice.


Not sure if that's applicable? Are the jobs just jobs or are they maintenance plans? If they're just jobs, there isn't an OS process involved.

EDIT: I can't restart SQL. This is a production box.


Just SQL, not the server. If that is the issue I had, you won't be able to take any full backups (or diff) until you restart SQL. See if you can schedule 5 min down soon (doesn't have to be now)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Brandie Tarvin
Brandie Tarvin
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14787 Visits: 9002
I know you meant the service. It's just that we've become a not-quite-24 x 7 shop with over 20 databases that multiple applications depend on, so finding a time in which to restart the service is getting to be a pain.

This whole mess started when these databases tried to restore while the de-duplication backup jobs were also trying to run (why they chose to run this in the morning, I'll never know). We're not using native SQL backup anymore, nor the usual third party tools. Since the de-dup backups are done by a different team with a different sort of tool, I don't know what issues this problem will cause them.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Brandie Tarvin
Brandie Tarvin
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14787 Visits: 9002
Well, you were right. There was no other way. We had to do the service restart over the holiday weekend, but it did work. In this case, it didn't prevent all database backups and restores, just the restores of two reporting databases.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
xinternet
xinternet
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 81
We had the same error. What it was was that our job calls and outside PadFiles.exe (a little VB program). It's holding the thread. Once we endtask from Task Manager, SQL Server releases the thread.


David LaCourse
Welsh Corgi
Welsh Corgi
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10252 Visits: 4894
I had this problem.

I was concerned that if I restarted the SQL Server Service that the Database would perform a recovery.

I checked the error log and I do not see that the Database is recoverying.

I had this situation before and I forced it to recover.. I can't remember what i did.

If I attempt to take the Database online. I get the following error:

Msg 5011, Level 14, State 7, Line 2
User does not have permission to alter database 'DW', the database does not exist, or the database is not in a state that allows access checks.
Msg 5069, Level 16, State 1, Line 2
ALTER DATABASE statement failed.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Welsh Corgi
Welsh Corgi
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10252 Visits: 4894
I had to restrt SQL Server a 2nd time.

I was stuck at recovery at 0%. It finally moved to 2%.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Brandie Tarvin
Brandie Tarvin
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14787 Visits: 9002
Corgi,

I have since learned that if you kill the SPID (not the job), then the SPID will stop the job on its own and rollback. It may take a while, but it works.

If you kill the job, though, all bets are off.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search