Search sqlserver-dba.com
Follow sqlserver-dba.com
*Use the Comments section for questions
SQLServer-DBA.com Links
Recent Posts
- The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
- How to find privilege changes with SQL Server Default Trace
- sys.dm_server_services - How to check if SQL Server service is running or not?
- Msg 3023, Level 16, State 2 Backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed.
- Can we restore a database to an older version in SQL Server?
- How do I save all SQL Agent Jobs
- How to view SQL Server mount point space without access to server
- DBCC SHRINKFILE: page could not be moved because it is a work table page.
- SQL Agent Last Modified explanation
- Sql certificate chain was issued by an authority that is not trusted
How to shrink tempdb when Event ID 1101 Could not allocate a new page for database 'TEMPDB' because of insufficient disk space in filegroup 'DEFAULT'
17 February,2017 by Tom Collins
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
Related Posts
TempDB performance and strategy checklist
Author: Tom Collins (http://www.sqlserver-dba.com)
Share:
Verify your Comment
Previewing your Comment
This is only a preview. Your comment has not yet been posted.
As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.
Having trouble reading this image? View an alternate.
Posted by: |