May 11, 2015 at 6:47 am
Hi guys
I'm reaching out because I think I reached my limits and I have no idea of what's happening
I'm trying to setup a MDW to monitor our company's test servers activity.
The MDW is hosted by a SQL Server 2014 (CU6) instance
At the moment, I'm trying to monitor another 2014 instance and a 2012 SP2 (CU5) instance.
I followed the wizards to create the MDW and the data collectors.
For the account, as we're using the same local account on these servers, I decided to go with it as it provides me access to the MDW server.
I should also mentioned that I applied the "fix" to remove system databases from the collection (so I can see IncludeSystemDatabases: False) in my collectors.
The SQL Server agent jobs do not fail but I cannot see any data in my MDW.
The server stats look fine to me.
For the disk usage I can see the monitored server databases but only the system databases are available. I get the error
"A data source has not been supplied for the data source DS_TraceEvents."
For the query data collector, the job returns the message "the step did not generate an output" and I cannot see any of the query stats in the MDW database.
I've been looking all over the internet (and will still do) but I was hoping one of you would have faced teh same issues.
Thanks in advance for your help
PS: I've asked this questions on the following thread http://www.sqlservercentral.com/Forums/Topic1562792-2799-1.aspx but was advised to start a new topic so here it is 🙂
May 11, 2015 at 7:10 am
OK, it looks like the data is not there or is not accessible.
Same places to look for information:
-- From the monitored server
SELECT * FROM msdb.dbo.syscollector_config_store; -- this shows you if the collection process is enabled correctly on the monitored server
-- From the MDW database
SELECT * FROM core.source_info_internal; -- This will show you if the collection sets are registered correctly in the MDW database
-- Gianluca Sartori
May 11, 2015 at 4:39 pm
Hi Gianluca,
Thanks again for your help
I ran your queries (sorry for the delay I'm on Australian time 🙂 )
The Monitored server query looks ok, I get the CacheWindow at 1, CollectorEnabled at 1 and the MDWInstance and Database is correctly populated
The MDW database query also returns me collection_set_uid for each of my monitored server and contains disk usage, server activity and query Statistics collector (found by joining your MDW query to the msdb.dbo.syscollector_collection_sets_internal)
I checked on the monitored servers and the collections are running. I'm not sure of one of the columns though named collection_mode. ONly Disk usage has it at 1.
Any idea where I can go from there?
I have my sets of queries and I see the plan cache for the monitored database so I know there is activity on the databases
Thanks again
May 11, 2015 at 4:48 pm
Hi again,
I've just tried increasing the logging of the collectors using the below query
update msdb.dbo.syscollector_collection_sets
set logging level = 2
where collection_set_id = 3
and when ran, it returned me dozens of warnings as the one below
The output column "row_id" (626) on output "Raw File Source Output" (620) and component "RFS - Read current cache with dm_exec_requests" (616) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.
....
The output column "total_elapsed_time" (1165) on output "Union All Output 1" (1147) and component "UNION - Combine interesting requests and interesting stats" (1045) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.
....
and then later on in the job I receive this one
The external columns for ODS - Upload snapshots into active_sessions_and_requests table are out of synchronization with the data source columns. The external column "host_name" needs to be updated.<nl/>The external column "program_name" needs to be updated.<nl/>The external column "command" needs to be updated.
Any idea of what it could be?
Thanks
May 12, 2015 at 2:21 am
Raising the logging level show you some warnings that you can safely ignore. I would revert to the original level.
Try this registry change to enable additional logging to a file: http://blogs.msdn.com/b/sqlagent/archive/2011/07/13/enabling-additional-tracing-for-data-collector.aspx
-- Gianluca Sartori
May 12, 2015 at 10:49 pm
OK,
I've done it and have the logs.
What should I check?
I can see the following but not sure if it's relevant
TxDataCollector!1250!25ec!2015/05/13!13:54:57:: CTxTopQueries::ProcessInput _IN
TxDataCollector!1250!25ec!2015/05/13!13:54:57:: Processed 0 input rows...
TxDataCollector!1250!25ec!2015/05/13!13:54:57:: Done processing 6 input rows.
TxDataCollector!1250!25ec!2015/05/13!13:54:57:: CTxTopQueries::ProcessInput _OUT:00000000
TxDataCollector!1250!282c!2015/05/13!13:54:57:: CTxTopQueries::ProcessInput _IN
TxDataCollector!1250!282c!2015/05/13!13:54:57:: Done processing 0 input rows.
TxDataCollector!1250!282c!2015/05/13!13:54:57:: CTxTopQueries::OutputTopQueries _IN
TxDataCollector!1250!282c!2015/05/13!13:54:57:: Done analyzing 1 queries.
TxDataCollector!1250!282c!2015/05/13!13:54:57:: CTopAnalystPlanChanges::Output _IN
TxDataCollector!1250!282c!2015/05/13!13:54:57:: Done outputting 0 query plan rows for 0 queries.
TxDataCollector!2bc4!1448!2015/05/13!15:00:03:: Done outputting 0 query plan rows for 0 queries.
TxDataCollector!2bc4!1448!2015/05/13!15:00:03:: CTopAnalystPlanChanges::Output _OUT:00000000
TxDataCollector!2bc4!1448!2015/05/13!15:00:03:: CTopAnalystQueryStats::Output _IN
TxDataCollector!2bc4!1448!2015/05/13!15:00:03:: Done outputting 1 interesting query stats rows for metric index 0.
TxDataCollector!2bc4!1448!2015/05/13!15:00:03:: CTopAnalystQueryStats::Output _OUT:00000000
TxDataCollector!2bc4!1448!2015/05/13!15:00:03:: CTopAnalystQueryStats::Output _IN
TxDataCollector!2bc4!1448!2015/05/13!15:00:03:: Done outputting 1 interesting query stats rows for metric index 1.
TxDataCollector!2bc4!1448!2015/05/13!15:00:03:: CTopAnalystQueryStats::Output _OUT:00000000
TxDataCollector!2bc4!1448!2015/05/13!15:00:03:: CTopAnalystQueryStats::Output _IN
TxDataCollector!2bc4!1448!2015/05/13!15:00:03:: Done outputting 1 interesting query stats rows for metric index 2.
....
Regards
Fred
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply