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(275.2, 3%, 36%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EC%3C/text%3E%3C/svg%3E)
My transaction log backup maintenance plan keep failing with the following error.
Executing the query "BACKUP DATABASE [TnDb] TO DISK = N'E:\Logs\Tn..." failed with the following error: "Write on "E:\Logs\TnDb\ TnDb_backup_2024_02_04_112623_1927552.trn" failed: 112(There is not enough space on the disk.)BACKUP DATABASE is terminating abnormally.10 percent processed.20 percent processed.30 percent processed.40 percent processed.50 percent processed.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Is this a case of not enough disk space on the "E" drive? What is a "ResultSet" property not set to correctly means?
Yes, you are short on disk space on the E: Drive. The verbiage about query, ResultSet etc is some general text from the maintenance plan or Agent, and nothing to do with the backup failure. Just ignore that part.
Thank you for the response. I got the disk space increase and I was able to backup the transaction log; however, it's not deleting some old .trn files that are dated 2020. Here's my Maintenance Cleanup Task in the Maintenance plan.
Hi @CharlieLor,
There is not enough space on the disk
The issue is clear that you do not have enough space on your driver. ResultSet specify the result type expected by the SQL statement being run.
In addition, The Maintenance Cleanup task does not automatically delete files in the subfolders of the specified directory. This feature reduces the possibility of a malicious attack that uses the Maintenance Cleanup task to delete files. If you want to delete files in first-level subfolders, you must select Include first-level subfolders.
Check Maintenance Cleanup Task (Maintenance Plan)
Regards，
Zoe Hui
If the answer is helpful, please click "Accept Answer" and upvote it.
My confusion is, I did check the "Include first-level subfolders" but it's not deleting files that are two three years old in the log folder.
Can I simply go into the Windows File Explorer and delete them my self? At least this will give me more disk space to backup.
Hi @CharlieLor, Make sure that you already have the bak files you need, and then you may delete the old .trn files manually.
Thank you so much for all the help. I'm now able to backup but the issue is, why is my SQL Server Database Transaction Log File (.LDF) in the data folder still so big? I thought once the log is backed up, the file size of this file should be smaller. No? To get it to smaller size, does that mean I have to manually shrink the file?
First a remark on how this forum is supposed to work. There are two types of entries: Answers and Comments. Answers are for answers to the original question. Comments on the original question are typically for request for clarification, or any other comment that don't aspire to answer the question. Further comments could be answers from the original poster.
Comments on Answers are typically followup to the answers.
Today, you have posted no less than three Answers, but you would only post an Answer to your own Question, if you find the solution yourself, and none of your posts did. I have converted all your posts to comments.
Now over to:
Thank you so much for all the help. I'm now able to backup but the issue is, why is my SQL Server Database Transaction Log File (.LDF) in the data folder still so big? I thought once the log is backed up, the file size of this file should be smaller.
No, the log file does not shrink automatically, that would be a very bad thing. Keep in mind that it takes computer resources to grow the log file, because all the space has to be zeroed out. Therefore, you want the log to retain the size it needs to support the workload.
What happens when the log is backed up is that it is truncated. That is, log records no longer needed for recovery are cleared, so that new log entries can be written into the space. But the file size does not change. Only the amount of used space within the file decreases.
There are situations where the log file may grow beyond what is actually needed. Say that a user leaves an uncommitted transaction in an SSMS query window open for a week. The log can't be truncated past the oldest active transaction, so this is likely to cause the log to grow. Once such a situation has been cleared out, it makes sense to shrink the log file to the size it had before.
But if you don't know of any such an event, I'd say that it is unwise to shrink the log. At least as long as you have not monitored the log-space usage for a longer time and concluded that the usage is never above 50%.
To see what prevents log truncation, you can run this query:
SELECT log_reuse_wait_desc FROM sys.databases WHERE name = 'yourdb'