Sql-server – Unknown System SPID blocking user SPIDs

blockingsql server

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).

Best Answer

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.

Related Solutions

Sql-server – Schema blocking in READ_COMMITTED_SNAPSHOT

The following behavior may be caused by missing indexes on referring side of your FKs: "the price changes take approx 1 hour to process (vs. 1-2 minutes) and sys.dm_tran_locks shows the transaction taking almost 90,000 different locks, compared to around 100-150 when foreign keys were being dropped/recreated"

When a row is deleted or its PK/Unique is mutating, the database engine need to make sure there are no orphans. When there is no proper index to support it, it scans the whole thing.

Sql-server – SQL Server 2008 database is not coming online after restore.. Pleae need help asap

I know this is an old question but will leave this here for anyone else who comes looking. Ran into this problem today and was able to get it going.

This is difficult to believe but disabling parallelism got it online in my case.

I never would have thought to even try that, only I was watching the sysprocesses for that DB and was seeing the parallelism split / cxpacket wait behavior… the wait types would change like it was working on something. Thinking maybe maxdop=1 would help whatever this was by not splitting up, changed it and the DB immediately came online.

exec sp_configure 'show advanced options', 1
go
reconfigure
go
exec sp_configure 'max degree of parallelism', 1
go
reconfigure
go

Other info:

Mine was also SQL 2008 database is 176GB
Also saw (at some point) a system spid (under 51) showing "Create Index" Runs change data capture in production (not sure that had anything to do with it)
Was restoring copy to a dev environment as part of a regular data refresh from production.
Disk space was very tight on the destination server it had just enough room for the data and log files to restore, but was under 10 meg free at that point. I think the lack of space may have helped cause the problem? I was able to free up some space before the maxdop change, but the database was still not usable until the maxdop setting was changed. sys.databases showed the state as 'online' the database looked usable in every way but any attempt to use it would time out

Best Answer

Related Solutions

Sql-server – Schema blocking in READ_COMMITTED_SNAPSHOT

Sql-server – SQL Server 2008 database is not coming online after restore.. Pleae need help asap

Related Question