3

We are performing a migration of 3rd party data and it is hanging.

When we investigate we see SPID 38 (a system/background SPID) is the blocking user SPIDs trying to do the migration. Unfortunately, the SQL_Text from sp_whoisactive (or even activity monitor) is NULL for SPID 38. The wait_info is PREEMPTIVE_OS_GENERICOPS.

Is there anyway to tell what 38 is trying to do?

It's using a Windows-based tool the 3rd party has given us. I took a backup of the data, restored it on this new server (so the database and its contents are accessible and working). Then we run this tool to do the upgrade to their new version of the software and that is where we are getting the blocks.

sp_WhoIsActive @get_locks = 1 returns a really long XML (4k characters too long to post).

0
2

I decided to look at the sys.sysprocesses table and found that FT_CRAWL (looking in the CMD field) was running on the blocking spid.

I guess a restored database starts crawling right away? Anyways, I shut off the Full Text Daemon and the blocking ceased. We finished the upgrade and then turned the Daemon back on and all was well in the world.

I have emailed Adam Mechanic to see if the CMD field is used in his sp_WhoIsActive, as I think that would be very beneficial in this type of situation.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.