Friday, September 16, 2011

Rasslin' With a Reluctant Restore

It's Friday, a day that I like to sit and reminisce (OK, stare out the window daydreaming).  While doing so, I saw somebody on the road outside doing something stupid, reminding me of a time when Stupidity made an appearance in my office.

All was right with the world, the universe was in order, and I think I was doing something, possibly staring out the window.  Suddenly the alarm was raised - the automated restore of one a database in our maintenance environment was failing, and the DBA responsible couldn't figure out why.  Faster than a speeding table scan, I spun around in my chair and grabbed my keyboard.

I began my interrogation of the victim - "What's the error message?"
"There isn't one", he replied, "The job just sits there and doesn't do anything!"

So much for advanced troubleshooting skills.  Firing up sp_who2, I saw that the RESTORE DATABASE command was being blocked (had been, in fact, for nearly three days) by another process.  This other process was stuck in a KILLED/ROLLBACK state, and belonged to none other than the DBA sitting beside me.

"What is this?", I asked.  He replied "Oh, that's something I killed earlier in the week, it shouldn't be there".  Well, it is, so let's figure out why.

Pulling DBCC INPUTBUFFER out of my, umm, utility belt, I found that this shouldn't-exist-but-does process had been running xp_cmdshell:

xp_cmdshell '\\ServerA\Import\SampleLiveData_ClientQuestions.txt'

Again I asked, "What is this?"
"Oh, I was trying to import a text file, I thought that might open it up within Management Studio" was the response.

Resisting the urge to destroy him with my Scowl Of Doom, I calmly replied, "No, what this did was launch Notepad as a background process, and it's sitting there waiting on input, which is why your non-existent process won't die."  To prove my point, I open up Windows Task Manager on the server:

I then further drove the point home by clicking the "End Process" button, dispatching NOTEPAD.EXE into the bit bucket.  Switching back to Management Studio and re-running sp_who2, as expected, the process-that-shouldn't-exist-but-does was no longer in existence.  More importantly, the RESTORE DATABASE process was no longer blocked, and was in fact actively generating disk I/O.  Less than an hour later, the restore was complete, and the universe was once again in order.


Post a Comment