KILL not responding causing transaction log to grow to 34GB

Question

Post reply

KILL not responding causing transaction log to grow to 34GB

Dave Hubble

SSC Rookie

Points: 40
More actions
April 13, 2004 at 7:52 am

#84797

Just found that one of my SQLServer 2000 databases has a process that is in a state of KILLED/ROLLBACK and has been for about two weeks.
This I believe has had the effect of causing the transaction log to grow to 34GB. Now I do not have room to back the database up on the server.
Does anyone know how I can clear this transaction log down safely now ? and if there is anything I can do to get rid of the KILLED process ?
If I reboot the server will it ROLLBACK all the transactions in the log ?

Viewing 9 posts - 1 through 8 (of 8 total)

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

lokesh prasad Grasshopper Points: 19 More actions · Answer 1

If you reboot the server , it will do a rollback and rollforward of all transactions in the log.

Steven. SSCrazy Eights Points: 9806 More actions · Answer 2

You can just stop and restart the sql server, but the transaction will just carry on rolling back/forward if it can.

If the process was running an extended stored procedure when it was killed, it can just sit there for ever (or until someone restarts the sql server).

Steven

John Campbell-96530 SSC-Addicted Points: 488 More actions · Answer 3

Back up your transaction log first, then truncate your transaction log secondly. Viewing the database size in the enterprise manager, you should have a normal sized log for the database now. Back up the database, and then stop and restart the server and the 'rollback' phantom will go away.

It seems that SQL Server 2000 is suseptible to this problem, especially with transactions that involve linked servers, and also distributed transactions.

Check your service packs not only on SQL Server but also on the operating system and any application software that is running SQL code.

mephisto SSC Enthusiast Points: 137 More actions · Answer 4

backup log dbname with truncate_only

dbcc_shrinkfile

SQL Draggon Right there with Babe Points: 766 More actions · Answer 5

SQL Draggon

Right there with Babe

Points: 766

April 14, 2004 at 9:24 am

#502759

Don't count what you do, do what counts.

SQL Draggon

Dave Hubble SSC Rookie Points: 40 More actions · Answer 6

Thanks I'll give it a go and let you know how I get on.

Cheers

SQL Draggon Right there with Babe Points: 766 More actions · Answer 7

please excuse the previous reply -- I have no idea what happened, here is what i was trying to say.

1st: look for the longest running process in the sysprocesses table that is not a system process, identify it -- then kill it.

2nd: if you are not using a t-log based disaster recovery, issue a chackpoint, if it does not work -- it will return an error telling you why -- it could be a hosed replication transaction -- check the SQL Server books or MSDN for a resolution to the error.

Good Luck.

Don't count what you do, do what counts.

SQL Draggon

karenwilk SSC Enthusiast Points: 117 More actions · Answer 8

I have gotten the killed/rollback problem more than once and researched it a bunch. The first thing you need to see is if the rollback is complete. It probably is. To find this out do:

kill spid with statusonly

(replace the spid with the spid number of the killed rollback process)

This will tell you the status of the rollback. If the rollback is 100% complete, then a restart of SQL Server will clear the status. After the process is stopped, then backup or truncate the log and then do a shrinkfile (either with the DBCC command or from Enterprise Manager). That should do you.

Good Luck!