Sql-server – Query runs in parallel but it is showing as blocked by itself

There is a lot of misunderstanding about CXPACKET. CXPACKET isn't the cause of your problems, it is a side effect. What CXPACKET means when you see it is that that thread of a parallel query is waiting for another thread of that query to do something. Just because you see a lot of CXPACKET waits doesn't mean there's a problem with the query, it means that there is a problem somewhere else. When you see CXPACKET waits you need to look at the other threads of the SPID and see what other waits are besides CXPACKET. That other wait is the problem.

To your specific issue, the reason that the run times are so crazy is probably because the SQL Server is generating a different plan on some days because the statistics are out of date (making the job run long). Then you either manually update statistics (or via a job) or auto stats kicks in and then the plan gets better and the job runs quickly again.

Once you've solved the stats problem you can start looking at other reasons why the job is running slow. The fact that you only have a single disk is not helping any for sure.

It is difficult to tell just from examining the code if it is running forever because of an infinite loop or blocking - my guess is the latter. But here is a slightly different approach that avoids some of the things that could be problematic:

CREATE PROCEDURE dbo.pr_BuscaLog
  @qtdDias INT
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @cmd NVARCHAR(MAX) = N'', @dataLimite DATETIME = CONVERT(SMALLDATETIME, 
      DATEADD(DAY, @qtdDias, GETDATE()));

  SELECT name INTO #tmp FROM sys.databases WHERE database_id > 4;

  -- delete the ones that don't have that view    
  SELECT @cmd += N'DELETE #tmp WHERE name = N''' + name 
      + ''' AND NOT EXISTS (SELECT 1 FROM ' + QUOTENAME(name) 
      + '.sys.views WHERE name = N''vwLogProjetos'');' FROM #tmp

  EXEC sys.sp_executesql @cmd;
  SET @cmd = N'';

  SELECT @cmd += N'INSERT PainelControle..tblLogProjetos(...columns...)  
    select ...columns... FROM ' + QUOTENAME(name) 
      + N'.dbo.vwLogProjetos as T1 with (nolock)  
          where DtTrabalho >= @dt and not exists
          (
            select 1 from PainelControle.dbo.tblLogProjetos T2 
             Where t2.BancoDados collate SQL_Latin1_General_CP1_CI_AS 
             = t1.BancoDados collate SQL_Latin1_General_CP1_CI_AS 
             and t2.codlog = t1.codlog
          );' FROM #tmp;

  BEGIN TRY
    EXEC sys.sp_executesql @cmd, N'@dt DATETIME', @dataLimite;
  END TRY
  BEGIN CATCH
    insert into dbo.tblLogProjetosErro (rotina, comando,Data) 
      values ('pr_BuscaLog', @cmd + ERROR_MESSAGE(), GETDATE());  
  END CATCH
END

Some things I've changed (or that you should):

• No explicit loops. Contrary to popular belief, a while loop is not faster than a cursor (see this post), but neither is needed here.
• Don't count when you don't have to - see this post.
• I parameterized the date value instead of dealing with messy string concatenation (see this post and this post). This is harder to avoid with an entity, but should always be done with variables.
• Please stop using lazy shorthand; e.g. use DATEDIFF(SECOND instead of DATEDIFF(s - see this post.
• Use the schema prefix. Always.