This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(51.2, 55.00000000000001%, 14%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
Error:
Unknown SQL Exception 9002 occurred. Additional error information from SQL Server is included below.
The transaction log for database 'tempdb' is full due to 'ACTIVE_TRANSACTION'.
This is caused one of the site to not launch because of the error. Temp db database was recreated on Jan24th and of course it is never backup. Running the disc usage report see the transactional log is 60% Unused and 39% used. We increased space in transactional logfile, which gave me access to SSMS. There are 6 databases whre the Log Space Used is more than 45%. Do I need to shrink the logfile in any of those databases. Logfile was not full in any of the databases.
Do I need to shrink the logfile in any of those databases.
No. Shrinking a database file is an exceptional action. And if you get a problem in one database, there is even less reason to fiddle with the log in another database, be that tempdb or something else.
I think the issue here is tempfile and a service restart might clean up tempfile. Tempfile is 60G. Using this script got the output
USE master
GO
SELECT DataFileName = mf.name,
FileSizeMB = mf.size*8/1024
FROM sys.master_files AS mf
WHERE mf.database_id = db_id('tempdb')
ORDER BY mf.type, file_id;
GO
USE tempdb
GO
SELECT DataFileName = dbf.name,
FileSizeMB = dbf.size*8/1024
FROM sys.database_files AS dbf
ORDER BY dbf.type, dbf.file_id;
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(211.20000000000002, 78%, 30%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ECZ%3C/text%3E%3C/svg%3E)
Have you restarted the sql server service, restart the SQL Server will clear the tempdb db and recreate it.
if not, what is the output of the following query.
DBCC SQLPERF(LOGSPACE)
GO
SELECT name, recovery_model_desc, log_reuse_wait,log_reuse_wait_desc
FROM sys.databases
GO
@Cris Zhan-MSFT - I plan on restarting SQL Server services today.
But the output from DBCC SQLPERF(LOGSPACE) - 60 G Logsize
and
SELECT name, recovery_model_desc, log_reuse_wait,log_reuse_wait_desc
FROM sys.databases has all Simple recovery model as Nothing as 'log_reuse_wait_desc' and all full backup "logbackup"
Hi,
So the log size of tempdb is 60G, and the 'log_reuse_wait_desc' entry corresponding to tempdb is 'NOTHING'.
It appears that the active transaction that prevented the tempdb transaction log from being truncated has been committed or rolled back.
Currently you can try to shrink the tempdb log file, or schedule SQL Server to restart to free up tempdb space.
@Cris Zhan-MSFT - I have restarted sqlserver and logspace usage was 0.11% after restart. After 4 hrs temp file grew 98%.
and SELECT name, recovery_model_desc, log_reuse_wait,log_reuse_wait_desc
FROM sys.databases. Any advices?
After few minutes Tempdb is showing nothing (log-reuse_wait_desc)
So the workload on the server you are administrating seems to require a bit of log space. Maybe it is time to get more disk for tempdb?
I have added few more temp datafile, hoping this might reduce logfile being used up. Any suggestions?
You have a wound on your left hand, and to cure that, you put a plaster on your right hand?
No, adding that data file is not going to reduce the amount of log space you need.