The transaction log for database 'tempdb' is full

sql sql-server sql-server-2012 database-administration sql-server-administration
Rob picture Rob · Jun 17, 2017 · Viewed 9.6k times · Source

I have only read permission to the database I'm querying in. The same query was working for since yesterday but today I am encountering the below error in sql server management studio.

The transaction log for database 'tempdb' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

Any thoughts or suggestions are much appreciated. Thanks in advance!!

Answer

Hybris95 picture Hybris95 · Jun 19, 2017

tempdb support operations like sort or temporary tables.

If a big select with a huge sort operation happens, tempdb might increase unexpectedly.

First make sure that kind of operations are exceptionals, if not fix it or make sure users are sensitive about this subject.

Then restart the instance and shrink the tempdb data file to reduce it to a proper size.

Restarting the instance will produce a service stop, so make sure users are aware of the restart

Edit :

Also if the given ETL cannot or won't be changed, then this is considered as normal behavior and the server has to be properly configured/sized in order to support such transactions.

Related questions

Convert Date format into DD/MMM/YYYY format in SQL Server

I have a query in SQL, I have to get a date in a format of dd/mm/yy Example: 25/jun/2013. How can I convert it for SQL server?

Read More
Recover unsaved SQL query scripts

How to recover the unsaved scripts if the SSMS crashes / unsaved tab gets accidentally closed?

Read More
SQL Server IF EXISTS THEN 1 ELSE 2

Using Sql Server 2012. I have a stored procedure and part of it checks if a username is in a table. If it is, return a 1, if not, return a 2. This is my code: IF EXISTS (SELECT * FROM tblGLUserAccess WHERE GLUserName =…

Read More