Contents
Why does Transaction Log get full?
When the transaction log becomes full, SQL Server Database Engine issues a 9002 error. The log can fill when the database is online, or in recovery. If the log fills while the database is online, the database remains online but can only be read, not updated.
What happens if TempDB runs out of space?
Nothing works without TEMPDB. If it grows too big, it will have a chain-reaction down through the rest of the databases, to include making them temporarily unusable. An emergency solution is always to stop and start the SQL Server services. That causes TEMPDB to rebuild at its original size.
What is causing tempdb to grow?
Much like your operating system has a page file to handle memory overflows, SQL Server uses TempDB like a page file. The most common occurrence of this is when a query “spills” to TempDB. Temp tables can lead to uncontrolled growth if they are being populated by a query that needs to be tuned.
Why is there no free space in tempdb?
They all grew to 63 GB, and filled up the 500 GBdrive (the .mdf and .ndf files say 99% free space.). I ran sp_who2 ‘Active’ and there is no active transaction using tempdb. Why can’t I release space? I cannot restart this server till Friday, which is our designated maintenance window. What should I do until then?
How to check the size of tempdb database?
You can check the configured size vs. actual size using these queries from Andy Mallon’s blog: The values from sys.master_files are the ones that tempdb should return to after a reboot. If those values have increased to 63 GB, then someone has configured them that way.
What should tempdb return to after a reboot?
The values from sys.master_files are the ones that tempdb should return to after a reboot. If those values have increased to 63 GB, then someone has configured them that way.
Why is MY SQL Server not releasing space?
Why can’t I release space? To be clear, the files won’t “shrink” back to their previous size on their own while the system is running. They should go back to their configured size after a reboot (since tempdb is recreated when the database engine starts up).