cdosysmail works for a few days then stops

• Orlando Colamatteo

  SSC Guru

  Points: 182276

  More actions

  February 8, 2013 at 6:39 am

  #1585924

  The fact that it works for a while and then starts having trouble is the odd part. Everything I am seeing online talks about it never working at all when you see that error.

  I am assuming the server is 32-bit? Are you having any other symptoms besides this? Have you checked to make sure your VAS reservation is not fragmented by the time this starts happening? Does restarting help? Is it happening now? What does this say on your server?

  WITH VASummary(Size, Reserved, Free)

  AS (

  SELECT Size = VaDump.Size,

  Reserved = SUM(CASE (CONVERT(INT, VaDump.Base) ^ 0)

  WHEN 0 THEN 0

  ELSE 1

  END),

  Free = SUM(CASE (CONVERT(INT, VaDump.Base) ^ 0)

  WHEN 0 THEN 1

  ELSE 0

  END)

  FROM (

  SELECT CONVERT(VARBINARY, SUM(region_size_in_bytes)) AS Size,

  region_allocation_base_address AS Base

  FROM sys.dm_os_virtual_address_dump

  WHERE region_allocation_base_address <> 0x0

  GROUP BY region_allocation_base_address

  UNION

  SELECT CONVERT(VARBINARY, region_size_in_bytes),

  region_allocation_base_address

  FROM sys.dm_os_virtual_address_dump

  WHERE region_allocation_base_address = 0x0

  ) AS VaDump

  GROUP BY Size

  )

  SELECT SUM(CONVERT(BIGINT, Size) * Free) / 1024 AS [Total avail mem, KB],

  CAST(MAX(Size) AS BIGINT) / 1024 AS [Max free size, KB]

  FROM VASummary

  WHERE Free <> 0;

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato

• Jeff Moden

  SSC Guru

  Points: 1003867

  More actions

  February 8, 2013 at 7:56 am

  #1585970

  I actually haven't run into this particular problem with CDOSYS but have run into similar problems because someone forgot to destroy the sp_OA* objects that were created. Make sure that your CDOSYS proc has something like the following near the end and that it actually is being executed. It may or may not be the solution to your particular problem but I've found that a lot of people forget to do this so it's a good check to make anyway.

  --===== Close the email object (helps prevent memory leaks)

  EXEC @Result = dbo.sp_OADestroy @objEmailID;

  IF @Result <> 0 OR @@ERROR <> 0 THEN RAISERROR('ERROR: Failed to destroy mail object on exit',16,1) WITH NOWAIT;

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)