What caused tempdb to fill up?
Large volume of CREATE TABLE AS SELECT (CTAS) or INSERT SELECT statements fills up the tempdb during data movement operations. Break your CTAS or INSERT SELECT statement into multiple, smaller transactions. Queries with insufficient memory allocated (via resource class or workload group) can spill into tempdb .
- Set tempdb to auto grow.
- Ensure the disk has enough free space.
- Set it's initial size reasonably.
- If possible put tempdb on its separate disk.
- Batch larger and heavy queries.
- Try to write efficient code for all stored procedures, cursors etc.
Use the ALTER DATABASE command
If more files are added to tempdb , you can shrink them after you restart SQL Server as a service. All tempdb files are re-created during startup. However, they are empty and can be removed. To remove additional files in tempdb , use the ALTER DATABASE command with the REMOVE FILE option.
The TempDB database is special in many ways, but an interesting aspect is that when its files automatically grow when they become full, this growth is not persisted and will be undone on the next restart of the SQL Server service. We can see how this works by looking at two system tables: sys. master_files and sys.
TempDB may have been almost completely filled by another previous transaction and the transaction causing the growth event is an innocent bystander. This is unavoidable, and needs to be kept in mind when analysing the data. If you don't catch the exact transaction this way, you are still on the right track.
The max number of TempDB files is 32767. Why are you considering increasing the number of TempDB files? What problems are you trying to address?
text ORDER BY [internal object MB] DESC; When you run above query, it will give you details about query growing TempDB which from the cache. If any query which is currently running or removed from the cache will be not part of the result of this query.
- Execute the DBCC DROPCLEANBUFFERS command to flush cached indexes and data pages. CHECKPOINT; GO. DBCC DROPCLEANBUFFERS; GO.
- Execute the DBCC FREEPROCCACHE command to clear the procedural cache. DBCC FREEPROCCACHE; GO.
If a user ran an atrocious adhoc query that caused your tempdb to grow so much that it caused your disk space alert to fire and: you needed that alert to stop. the storage team is not going to give you more space.
- Step 1 – Attempt Repair with SQL Server Management Studio (Optional) ...
- Step 2 – Choose a Good Database Repair Tool (Recommended) ...
- Step 3 – Download Your SQL Repair Tool. ...
- Step 4 – Run Your SQL Database Repair Tool. ...
- Step 5 – Scan the Corrupted SQL Database.