Can't Kill SPID

Question

Can't Kill SPID

Jpotucek

SSCoach

Points: 18583
More actions
May 4, 2015 at 7:52 am

#99041

I have a SQL Server 2000 Database. 30+ GB with a 15+GB log file.
I attempted to backup the Database and I receive the following error:
Server: Msg 9001, Level 21, State 1, Line 1
The log for database 'MYDB' is not available.
I then checked the Activity log and I see a System process associated with this Database:
Process ID 5
Command: autoshrink
Wait Type: LOGBUFFER
WaitTime: 76153109
Memory usage: 3
Physical I/O: 181
Cannot kill this SPID as it is a System process (receive this error:)
Server: Msg 6107, Level 14, State 1, Line 1
Only user processes can be killed.
Can anyone help with this?

Viewing 3 posts - 1 through 2 (of 2 total)

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

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 1

Jpotucek (5/4/2015)
I have a SQL Server 2000 Database. 30+ GB with a 15+GB log file.
I attempted to backup the Database and I receive the following error:
Server: Msg 9001, Level 21, State 1, Line 1
The log for database 'MYDB' is not available.
I then checked the Activity log and I see a System process associated with this Database:
Process ID 5
Command: autoshrink
Wait Type: LOGBUFFER
WaitTime: 76153109
Memory usage: 3
Physical I/O: 181
Cannot kill this SPID as it is a System process (receive this error:)
Server: Msg 6107, Level 14, State 1, Line 1
Only user processes can be killed.
Can anyone help with this?

You'll need to wait for the autoshrink to complete. As an important sidebar, you need to find out what is doing the autoshrink and have it stop doing that. It's fragging the hell out of your files and data and is consider by everyone I know including the guy that wrote it to be one of the absolute worst practices there is for SQL Server.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jpotucek SSCoach Points: 18583 More actions · Answer 2

duly noted about the autoshrink being a bad practice. Thank you!