CheckDB TempDB usage

  • Hi, I have a database that is around 100GB in size, running a CheckDB on SQL2008r2 runs fine.

    I recently moved it to SQL2016, still keeping the compat level the same for now, but it now fails to complete a CheckDB I get this error

    Command: DBCC CHECKDB ([Databasename]) WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY

    Msg 1105, Level 17, State 2, Server servername, Line 1

    Could not allocate space for object 'dbo.SORT temporary run storage:  140754900221952' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

    Msg 9002, Level 17, State 4, Server servername, Line 1

    The transaction log for database 'tempdb' is full due to 'ACTIVE_TRANSACTION'.

    Msg 8921, Level 16, State 1, Server servername, Line 1

    Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.

    Outcome: Failed

    Duration: 02:00:45

    Date and time: 2021-02-10 10:33:34

    The TempDB is split into 4 data files and one log file on SQL2016 and just 2 data files and log file on SQL2008R2, the disk on 2016 has 53gb space and allowed to autogrow and the disk on SQL 2008r2 has 250gb space however only 10GB is allowed.

    Could anyone help me understand why its now using more Tempdb space on the new server when it appears to be the same size database and tables.

  • I don't know the answer.

    But keeping the compatibility level unchanged does not imply that the CHECKDB process will also be unchanged. Perhaps the checks have become more thorough and therefore require more resources. I'm sure there will be a DBA here who knows the answer to that.

    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

  • I don't know what changes were made to DBCC between 08 and 16. I'm sure there were more than a few since there were all sorts of underlying updates. However, WHY doesn't matter nearly so much as WHAT. You have a very clear error message. You need some more space. Period. I'd sweat this in two distinct stages. Stage one, get some more space so I can run my consistency checks. Stage two, worry about what internal changes are likely to have caused a shift in behavior (assuming I care that much once I've fixed the issue).

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • "The transaction log for database 'tempdb' is full due to 'ACTIVE_TRANSACTION'."

    The first thing I would do is look for long running transactions on the server. It might have nothing to do with CHECKDB.

     

  • It seems like you might have a (very) old transaction running.

    See what this tells you:

    USE tempdb;

    DBCC OPENTRAN;

     

    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".

Viewing 5 posts - 1 through 4 (of 4 total)

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