Restarting Data Collection (MDW)

Most data collection errors can be resolved by restarting data collection. A PowerShell script can be used to:

1. Stop data collection in all the SQL Server instances running on the machine
2. Kill dcexec.exe processes
3. Delete all cache files (our cache directory is c:\temp)
4. Start data collection in all the SQL Server instances running on the machine

 
function RestartDataCollection($SrcSrv)
{
    try
    {
        $SrcCon  = New-Object System.Data.SqlClient.SQLConnection("Data Source=$SrcSrv;Initial Catalog=master;Integrated Security=True;Connection Timeout=60") 
        $SrcCon.Open() 
                
        $SrcCmd = New-Object system.Data.SqlClient.SqlCommand("select @@version", $SrcCon)  
        $SrcCmd.CommandTimeout = 300

        $query = "
        declare @instances table( 
        value nvarchar(100)
        ,instance_name nvarchar(100)
        ,data nvarchar(100)
        );

        declare @count int;

        with a as(
        select *, ROW_NUMBER() over(partition by l.collection_set_id order by l.log_id desc) row_num
        from msdb.dbo.syscollector_execution_log_internal l with(nolock)
        )
        select @count = count(*)
        from a
        where a.row_num = 1
        and a.failure_message is not null

        --if @count > 0 

        insert into @instances
        exec xp_regread
        @rootkey = 'HKEY_LOCAL_MACHINE'
        ,@key = 'SOFTWARE\Microsoft\Microsoft SQL Server'
        ,@value_name = 'InstalledInstances'

        select 
        case when instance_name = 'MSSQLSERVER' then SERVERPROPERTY('ServerName') else instance_name end instance_name 
        ,SERVERPROPERTY('ComputerNamePhysicalNetBIOS') computer
        from @instances
        "

        $SrcCmd.CommandText = $query
        $instances = $SrcCmd.ExecuteReader()
        $a = New-Object System.Collections.ArrayList
        $i = 0

        $query = "
        exec msdb.dbo.sp_syscollector_stop_collection_set @name = 'Server Activity'
        exec msdb.dbo.sp_syscollector_stop_collection_set @name = 'Query Statistics'
        exec msdb.dbo.sp_syscollector_stop_collection_set @name = 'Disk Usage'
        "
        
        "stop collection"

        $computer = ""

        while ($instances.Read()) 
        {
            $SrcSrv = $instances.GetValue(0) 
            $computer = $instances.GetValue(1)
            $r = $a.Add($i) 
            $a[$i] = $SrcSrv
            $i = $i + 1
            $SrcSrv
            try
            {
                Invoke-Sqlcmd -ServerInstance $SrcSrv -Query $query -QueryTimeout 300
            }
            catch
            {
                $e = $_ | select -ExpandProperty InvocationInfo
                $m = $_.Exception.Message.TrimEnd().Replace("'","") + ", " + $e.ScriptLineNumber.ToString() + ", " + $e.OffsetInLine.ToString()
                $m                
            }
        }
        $instances.Close()

        "killing dcexec processes"

        $processes = Get-Process -ComputerName $computer -Name "DCEXEC" -ErrorAction SilentlyContinue
        
        foreach ($process in $processes)
        {
            try
            {
                taskkill /F /T /S $computer /PID $process.Id
            }
            catch
            {
                $e = $_ | select -ExpandProperty InvocationInfo
                $m = $_.Exception.Message.TrimEnd().Replace("'","") + ", " + $e.ScriptLineNumber.ToString() + ", " + $e.OffsetInLine.ToString()
                $m                
            }
        }

        "deleting cache files"

        $files = get-childitem "\\$computer\c$\temp\*.cache" -ErrorAction SilentlyContinue

        foreach ($file in $files)
        {
            try
            {
                $file.Delete()
            }
            catch
            {
                $e = $_ | select -ExpandProperty InvocationInfo
                $m = $_.Exception.Message.TrimEnd().Replace("'","") + ", " + $e.ScriptLineNumber.ToString() + ", " + $e.OffsetInLine.ToString()
                $m                
            }
        }

        $query = "
        exec msdb.dbo.sp_syscollector_start_collection_set @name = 'Server Activity'
        exec msdb.dbo.sp_syscollector_start_collection_set @name = 'Query Statistics'
        exec msdb.dbo.sp_syscollector_start_collection_set @name = 'Disk Usage'
        "
        
        "start collection"

        foreach ($SrcSrv in $a)
        {
            $SrcSrv
            try
            {
                Invoke-Sqlcmd -ServerInstance $SrcSrv -Query $query -QueryTimeout 300
            }
            catch
            {
                $e = $_ | select -ExpandProperty InvocationInfo
                $m = $_.Exception.Message.TrimEnd().Replace("'","") + ", " + $e.ScriptLineNumber.ToString() + ", " + $e.OffsetInLine.ToString()
                $m                
            }
        }

        $SrcCon.Close() 
        $SrcCon.Dispose() 
    }
    catch
    {
        $e = $_ | select -ExpandProperty InvocationInfo
        $m = $_.Exception.Message.TrimEnd().Replace("'","") + ", " + $e.ScriptLineNumber.ToString() + ", " + $e.OffsetInLine.ToString()
        $m
    }
}

("Server1", "Server2", "Server3") | foreach{RestartDataCollection $_}