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

Question

Post reply

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

IT researcher

SSCertifiable

Points: 7606

December 21, 2024 at 6:20 am

#4502737

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;

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246985 More actions · Answer 1

That is not a query … it looks more like a proc. definition.

Does it time-out from within SSMS, or is this called from somewhere else?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

ScottPletcher SSC Guru Points: 101119 More actions · Answer 2

(1) If you have a lot of dbs, make the $IDENTITY column a primary key in the table, so you have a seek instead of a scan

(2) DBCC CHECKDB can have rather high overhead. Could you specify some options to reduce the checking required, for example, NOINDEX and/or PHYSICAL_ONLY?

(3) Why use a transaction? Do you really want to rollback what's in dbo.dbcc_history from earlier checks if you have an error later? The trans adds some slight overhead.

(4) Use "INSERT INTO dbo.dbcc_history" rather than "INSERT INTO dbcc_history"; again, avoids very slight overhead, and can help prevent a recompile.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".