Quick one today: Earlier last week, I was playing with one of our customer databases in our lab environment. As part of implementing table partitioning, some tables changes were necessary; Moving data from one set of tables to another. In the middle of running those scripts, this error occurred:
Msg 1105, Level 17, State 2, Line 1 Could not allocate space for object 'dbo.SORT temporary run storage: 140751663071232' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup. Msg 9002, Level 17, State 4, Line 1 The transaction log for database 'tempdb' is full due to 'ACTIVE_TRANSACTION'.
All the heavy data movement, between tables and reindexing, has incurred a lot of growth in the transactional log file of the user database; And equivalent growth in the data/log files of tempdb. So, we took the following steps to rectify it: As the script took a few hours to execute and complete all the data movement. We did the following steps, every few minutes:
- Issue CHECKPOINT on the tempdb
USE tempdb GO CHECKPOINT GO
- Take Transactional log backup on the user database
BACKUP LOG CustomerXS TO DISK = N'M:\MSSQL\Backup\MSSQLSERVER\XS and RT\XS_Movement.trn' WITH COMPRESSION GO
This allowed us to create more free space in the database files to accommodate space for new activity.
Hope this helps,
_Sqltimes
[…] : Login Failure Alerts – meaning of different error messages from Operating System Sql Server Error Messages : The transaction log for database ‘tempdb’ is full due to … […]
Thanks this was very helpful
Appreciate your feedback Mercy. Glad it was helpful for you.
In situations, when you are running a long series of transactions that frequently fill up the log file, I use the scrip mentioned in this post. It runs continuously and prevents any log fill ups.
Hope this help,
_SqlTimes
Hi. I am getting this error message from all our SSIS packages running on a 2016 SQL Server. All our SSIS packages are failing and reporting the error above, but we have verified, and the TEMPDB files and TEMPDB Log files are not even close to being full, and the drives have 75% free space. Plus, we do not have any active transactions either. All other operations are working properly. I appreciate any help on the matter.
If it says, “Active_Transaction”, then there is an active transaction lingering some where. There is a DBCC command and some system DMVs to uncover the such transactions.
Thank you. I finally found the issue. The tempdb issue was on our ERP server.