Menu
Msg 1101, Level 17, State 10, Line 12 Could not allocate a new page for database 'TEMPDB' because of insufficient disk space in filegroup 'DEFAULT'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
What does this mean in plain English.
620
Adding Files to a Different Disk If the Database engine shows an error of disk space unavailability then you can try adding files on a different disk using Transact-SQL. It should solve the disk space issue as you are now using disk space of different disk to add files.
Using SQL Server Management StudioExpand Databases, right-click the database to increase, and then click Properties. In Database Properties, select the Files page. To increase the size of an existing file, increase the value in the Initial Size (MB) column for the file.
If this happens, operations will fail. A disk drive should have enough free space to allow the largest database file to automatically grow to twice its size. Cause: The databases have large files that may exceed the available free space. Resolution: Free disk space on the database server computer.
After you use a DELETE statement in Microsoft SQL Server to delete data from a table, you may notice that the space that the table uses is not completely released.
I've found that the normal cause of such explosive growth of TempDB is a query, either ad hoc or in a stored procedure, that has an unexpected many-to-many join in it that some refer to as an "Accidental Cross Join". Behind the scenes, it can create litterally billions of internal rows that end up living in "work" tables that live in TempDB.
The fix isn't to simply allocate more disk space. The fix is to find which query is the cause of the problem and fix it. Otherwise, you'll be stuck in a never ending cycle of having to restart SQL Server, etc, etc.
And, no... you don't have to check to see if TempDB is in the "SIMPLE" recovery mode because you can't set it to anything else. Try it and see.
135
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
