Timeout Error while executing a TSQL statement in Sql server Express Edition

• IT researcher

  SSCertifiable

  Points: 7606

  More actions

  December 21, 2024 at 6:20 am

  #4502736

  I am getting the below error when I execute a SQL command in SQL Server 2017 Express Edition

  Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding

  The same query works fine in SQL Server Standard Edition. What might be the reason for this issue?

  Query

  BEGIN TRY
      BEGIN TRANSACTION;
  
      -- Declare a table variable to store database names
      DECLARE @dbnames TABLE (
          id INT IDENTITY(1, 1),
          name VARCHAR(128)
      );
  
      -- Insert database names into the table variable
      INSERT INTO @dbnames (name)
      SELECT name
      FROM master.dbo.sysdatabases;
  
      -- Declare variables for processing
      DECLARE @TotalCount INT;
      DECLARE @HalfCount INT;
      DECLARE @Counter INT;
      DECLARE @dbname VARCHAR(128);
  
      -- Get total count of databases
      SELECT @TotalCount = COUNT(1)
      FROM sys.databases;
  
      -- Calculate the halfway point
      SET @HalfCount = @TotalCount / 2;
      SET @Counter = @HalfCount + 1;
  
      -- Loop through the second half of the databases
      WHILE (@Counter <= @TotalCount)
      BEGIN
          -- Get the database name for the current counter
          SET @dbname = (
              SELECT name
              FROM @dbnames
              WHERE id = @Counter
          );
  
          BEGIN TRY
              -- Execute DBCC CHECKDB and insert results into dbcc_history table
              INSERT INTO dbcc_history (
                  [Error],
                  [Level],
                  [State],
                  MessageText,
                  RepairLevel,
                  [Status],
                  [DbId],
                  ObjectId,
                  IndexId,
                  PartitionId,
                  AllocUnitId,
                  [File],
                  Page,
                  Slot,
                  RefFile,
                  RefPage,
                  RefSlot,
                  Allocation
              )
              EXEC ('DBCC CHECKDB(''' + @dbname + ''') WITH TABLERESULTS');
          END TRY
          BEGIN CATCH
              -- Handle errors during DBCC CHECKDB execution
              IF @@TRANCOUNT > 0
                  ROLLBACK;
  
              PRINT 'An error occurred during the transaction for database ' + @dbname + ': ' + ERROR_MESSAGE();
          END CATCH;
  
          -- Increment the counter
          SET @Counter = @Counter + 1;
      END;
  
      -- Commit the transaction if successful
      COMMIT;
  
      -- Select results from the dbcc_history table
      SELECT *
      FROM dbo.dbcc_history;
  END TRY
  BEGIN CATCH
      -- Handle any outer errors
      IF @@TRANCOUNT > 0
          ROLLBACK;
  
      PRINT 'An error occurred during the transaction: ' + ERROR_MESSAGE();
  END CATCH;