Here’s a weird one.  I tried to drop a database in our QA environment the other day, but the DROP DATABASE statement would never return.  It would just sit there and spin its wheels.  The database would indeed be dropped, but the statement never finishes executing.  dbcc inputbuffer shows that just a drop database statement is running.  Nothing exotic.

So, I query the sys.dm_os_waiting_tasks DMV to see what exactly this drop statement is waiting on, and low and behold:

Wait, what?  MSSEARCH?  New one on me.  So I peruse over to event viewer and find this.  I’ll tell you right now, this was a red herring.  Nothing to do with the actual issue.  If you see this in your event logs, it’s another issue entirely.  I just don’t want you chasing down erroneous errors like I did.  I ended up uninstalling windows search 4 due to this red herring.

The issue was actually an invalid full-text index.  I had set up a process that takes a database and copies it to the same server nightly for testing dataloads so our QA dept. can do pre & post load comparisons.  To get this database copied quickly (it’s rather large) I drop the current pre-load database, set the current load database offline and copy the files to a new location.  I then bring the load database back online and re-attach the pre-load database.  I had inadvertently not attached the fti files (although I did copy them correctly) when re-attaching the pre-load database. 

I suspect the preload database was still pointing to the postload databases’ FTI files.  That makes the most sense, as if it tried to drop the FTI files, the original database would still be using the files and not let them go.  In the end, I ended up dropping the FTI in the source database before doing the copy, as it’s not being used anyway.  I’m not the best FTI resource, I’ve used them very little.

USE [master]
GO
CREATE DATABASE [PreLoadDatabase] ON 
( FILENAME = N'E:\SqlData\PreLoadData\PreLoadDB.mdf' ),
( FILENAME = N'E:\SqlData\PreLoadData\PreLoadDB.ldf' ),
( FILENAME = N'E:\SqlData\PreLoadData\PreLoadDB.ndf' )
 FOR ATTACH
GO