SQL Server Alert System: 'Severity 017 - Insufficient Resources' occurred on SQL-SVR04-VM

Hi Everyone,

I need some help to tweak and configure the SQL Server settings to allow the ARM scan to run completely.

This is the error that I have received, despite the T:\ drive has been extended to 45 GB and there was no indication of the disk space was full:

DESCRIPTION:    Could not allocate space for object 'dbo.WORKFILE GROUP large record overflow storage:  141138546393088' 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.

This is the Disk space information:

Any help would be greatly appreciated.

  • Might you be using SQL Express? Hitting the 10 GB per database limit? You get that error message then. 

    But temp-db shouldn't be that heavily used then....
    If you run below query you can see how much of a database file is used and how much is empty. Most of the time the temp db files should be empty more or less. 

    [FILE_SIZE_MB] =
    convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) ,
    NAME = left(a.NAME,25),
    FILENAME = left(a.FILENAME,40)
    dbo.sysfiles a

    Run above against the db you want to check. If Express and tempdb full you have to figure out what is filling tempdb. 

  • Actually, I am using the standard SQL Server:

    Microsoft SQL Server 2016 (SP2-CU15-GDR) (KB4583461) - 13.0.5865.1 (X64)   Oct 31 2020 02:43:57   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor) 

  • This is the result of your command:

  • Ahh, stupid me. The answer was in the first post.

    The four tempdb files are now 2304 MB (initial size) and they have a maxsize of 2304MB as you see below in yellow. Meaning they are not allowed to grow.

    From the query you got from me and ran you see they are almost empty now (space_used_mb) but sometimes they are heavily used, like in big jobs or index maintenance.

    Edit each tempdb file by clicking the "..."-button, green below, and change the maxsize to somthing much larger (as you have a specific tempdb drive I would set it to unlimited. )

    Change each tempdb file to the same setting. 

    Good luck!

  • 141138546393088 do I need about 141 TB to match the required error logs ?

  • Really hope you don't :-).

    No, I think that is something else, not how much is needed. The change above can be done without interfering with production by the way.  

    I definitely thinks your 45 GB disk will be enough.

  • It's never a good idea to go with unlimited autogrowth. set maxsize so that some free space on T: is left over.