I have a 2008 instance that is showing a lot of batch requests/sec and it would appear (via profiler) that the Master database is having 00's of scans/sec. I have never seen anything like it, there are no other servers here with this occurring. Not really sure where to go next. There is next to no information in profiler - no textdata, no sqlhandle ... Only trace flad 1204 and 1222 are running and turning them off makes no difference. Any ideas please?
Have you looked at sys.dm_exec_requests? You should be able to see stuff going by there. Then link over to sys.dm_exec_sqltext. If it's encrypted you'll still get a row, although you won't see the query.
I would be tempted to set up an extended event session that captured some information about anything being executed on master. CREATE EVENT SESSION [TraceMaster] ON SERVER ADD EVENT sqlserver.sql_statement_completed (ACTION (sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.client_pid, sqlserver.nt_username, sqlserver.username) WHERE sqlserver.database_id = 1), ADD EVENT sqlserver.module_end (ACTION (sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.nt_username, sqlserver.username) WHERE sqlserver.database_id = 1) ADD TARGET package0.ring_buffer WITH (MAX_MEMORY = 4096KB, EVENT_RETENTION_MODE = ALLOW_MULTIPLE_EVENT_LOSS, MAX_DISPATCH_LATENCY = 1 SECONDS, MAX_EVENT_SIZE = 0KB, MEMORY_PARTITION_MODE = NONE, TRACK_CAUSALITY = OFF, STARTUP_STATE = ON) If you use [SQLSarg's extended events manager] (note I said SQLSarg because I don't want to mis-spell his surname!) then it makes it pretty easy to get the output out - plus then you can add actions for more info (for example, sqlserver.sql_text to get the input buffer)... Hahah - it took me so long to get the event session SQL right, that there are two better answers now! :