Click here to Skip to main content
15,867,756 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
i am trying to update three columns in a table in 3 different update queries based on three different conditions and it says
The transaction log for database 'A_TEST' is full due to 'ACTIVE_TRANSACTION'


Can some body suggest what might be the issue. i have googled it but seems nothing can be done in programmatically, any inputs on how to handle it in sql script while running that command

my logs are written to the following path in sql server which has autogrowth enabled

"D:\MSSQL\DATA"

but my D drive is full and has only 8MB space left.

Could this be an reason for the issue?

What I have tried:

haven't tried as i am not sure how to approach via code as the database is customer one and have no permission to change the log settings
Posted
Updated 24-Nov-22 2:22am
v2
Comments
Herman<T>.Instance 24-Nov-22 5:51am    
What are your DB settings for logging and why is your db keeping connections/transactions open?

Quote:
The transaction log ... is full
...
my D drive is full
...
Could this be an reason for the issue?
Yes.

If SQL Server doesn't have enough space in the transaction log to write the details of your transaction, you will get a "transaction log full" error. Even if your transaction log doesn't have a fixed size, if the disk it's stored on has run out of space, the transaction log will be unable to grow, and won't be able to process any more transactions.

The Transaction Log (SQL Server) - SQL Server | Microsoft Learn[^]

As Herman said, you need to find out why your transaction log is taking up so much space. There could be many reasons - for example, if your database recovery mode is set to "full" and you've never taken a transaction log backup.

Microsoft's documentation has a good explanation of the possible causes, and what you can do to try to fix them:
Troubleshoot full transaction log error 9002 - SQL Server | Microsoft Learn[^]

NB: The ACTIVE_TRANSACTION reason suggests you have at least one long-running transaction which has never been committed or rolled-back. Query the sys.dm_tran_database_transactions[^] dynamic view to see what transactions are active.
 
Share this answer
 
Comments
CHill60 24-Nov-22 8:23am    
5'd. More comprehensive than my overlapped solution.
Without knowing the nature of the queries you are running nor the volume of data they will impact it is impossible to say whether or not only having 8MB left on your D: drive is the cause - but I would suggest that it is highly likely.

If the database belongs to a customer then you should contact their DBA and discuss their Transaction Log management - Microsoft offer recommendations Manage Transaction Log File Size - SQL Server | Microsoft Learn[^].
Or you can ask them to shrink or truncate the transaction log.

However, you say that your D: drive is full - which sort of implies that you have a copy of the customer database in your local environment - in which case it's up to you to manage your local transaction logs - see the link above. Given that the database is called "A_TEST" then you can probably afford to be quite ruthless with those logs.

In particular look to the Transact-SQL feature DBCC SHRINKFILE[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900