Question: An analyst was completing some reconciliation checks and some of the queries required to complete the reconciliation forced the SQL Server TempDB to grow and fill the drive hosting the TempDB.The error message appearing was :
Event ID 1101Could 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 tactics are available to the DBA for dealing with this temporary problem?
Answer: Even with the best capacity planning – there will be times where users run queries requiring resources beyond the limits. Event ID 1101 means the disk is full.
Depending the situation there are a number of possibilities .
- Add an extra TEMPDB file onto another drive TEMPDB error - Could not allocate space for object dbo.SORT temporary run storage
- Restart SQL Server will recreate the TEMPDB database. This step will require agreement with the SQL Server owner - as they will need to negotiate with the users
- Shrink Tempb without a restart - Shrink tempdb without sql server restart
- Change the query. Check the SQL statement causing this situation. Consider breaking the sql statement down into smaller chunks. SQL Server – List current tempdb statements - SQL Server DBA