envqueue2

Snippet Name: envqueue2

Tags: helpful,scriptgeneration

Created Date: Oct 1 2021 8:05AM
Last Modified Date: Apr 1 2022 11:23AM

–desc: another flavor of service broker queue information

Dependencies: none-standalone code

default description

Sample Output:

 
 --desc: another flavor of service broker queue information
 
 
 
 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
 
 GO
 
 
 
 IF OBJECT_ID('tempdb..[#QueueResults]') IS NOT NULL 
 
 DROP TABLE [#QueueResults] 
 
 
 
 CREATE TABLE [#QueueResults] ( 
 
 [ServerName]                           VARCHAR(128)                                NULL,
 
 [DatabaseName]                         VARCHAR(128)                                NULL,
 
 [Note]                                 VARCHAR(33)                             NOT NULL,
 
 [Expectation]                          VARCHAR(59)                             NOT NULL,
 
 [NumberofItemsInTheTransmissionQueue]  INT                                         NULL)
 
 
 
 EXECUTE sys.sp_MSforeachdb '
 
 
 
 use [?];
 
 INSERT INTO #QueueResults([ServerName],[DatabaseName],[Note],[Expectation],[NumberofItemsInTheTransmissionQueue])
 
 SELECT 
 
   CONVERT(VARCHAR(128),@@SERVERNAME) AS ServerName,
 
   CONVERT(VARCHAR(128),db_name()) AS DatabaseName,
 
   ''All rows in the  queue are errors'' AS Note,
 
   ''No rows should exist in the queue, all awaiting processing.'' AS Expectation,
 
   COUNT(*) AS NumberofItemsInTheTransmissionQueue 
 
 FROM sys.transmission_queue
 
 HAVING count(*) > 0 ;
 
 '
 
 SELECT * FROM [#QueueResults]