Looking at Query Performance Insight I found this query running 3-4 times/hr (my autopause setting is 1hr):
SELECT c.*,
i.object_id, i.unique_index_id, i.is_enabled, i.change_tracking_state_desc, i.has_crawl_completed,
i.crawl_type_desc, i.crawl_start_date, crawl_end_date,
i.incremental_timestamp, i.stoplist_id, i.data_space_id, i.property_list_id,
cast(OBJECTPROPERTYEX(i.object_id, 'TableFullTextMergeStatus') as int) as merge_status,
cast(OBJECTPROPERTYEX(i.object_id, 'TableFulltextDocsProcessed') as int) as docs_processed,
cast(OBJECTPROPERTYEX(i.object_id, 'TableFulltextFailCount') as int) as fail_count,
cast(OBJECTPROPERTYEX(i.object_id, 'TableFulltextItemCount') as int) as item_count,
cast(OBJECTPROPERTYEX(i.object_id, 'TableFulltextKeyColumn') as int) as key_column,
cast(OBJECTPROPERTYEX(i.object_id, 'TableFulltextPendingChanges') as int) as pending_changes,
cast(OBJECTPROPERTYEX(i.object_id, 'TableFulltextPopulateStatus') as int) as populate_status
FROM [46e881b7-c5f1-41cb-8eee-7c92a89cba41].sys.dm_fts_active_catalogs c
JOIN [46e881b7-c5f1-41cb-8eee-7c92a89cba41].sys.fulltext_indexes i on c.catalog_id = i.fulltext_catalog_id
Any thoughts on what might be going on? Is there a way to detect the origin of the query? I only have one VM hooked up to the db, and I have services turned off, so not sure what is causing this periodic call.
I found the issue using Query Performance Insight, where I was able to view actual activity including query text. Even though I had configured the service's own scheduler to make it go to sleep, the service was still querying the DB. Once I actually turned the service off using the Task Scheduler, the DB paused after an hour of inactivity.
Query Performance Insight is a great tool for determining exactly what is going on with your database. One thing to remember though, if the database is paused and you navigate to Query Performance Insight, the database will be brought online.