Cannot delete publication error: The transaction log for database 'tridion_cm' is full

Question

While trying to delete an old publication, I get the error:

The transaction log for database 'tridion_cm' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases Unable to delete Publication (tcm:0-33-1). A database error occurred while executing Stored Procedure "EDA_PUBLICATIONS_DELETE".EDA_PUBLICATIONS_DELETE

Looking in the log_reuse_wait_desc column for tridion_cm (SQL Server 2008 R2) I see the text NOTHING, which doesnt seem particularly enlightening.

I also found and ran the following SQL to check files for the DB:

SELECT name AS [File Name] , file_id, physical_name AS [Physical Name],

size/128 AS [Total Size in MB],size/128.0 - CAST(FILEPROPERTY(name,

'SpaceUsed') AS int)/128.0

AS [Available Space In MB]

FROM sys.database_files;

This shows the log to have plenty (>180MB) of space.

Answer 1

You can just Truncate logs. there is a script for truncating the logs. But just confirm that will truncate log effect Your License Support from Tridion, I am not sure about that. here is the link for script TruncateLog

Answer 2

I finally managed to delete the publication, but only by deleting the majority of the content in it first, presumably this made the transaction to delete a small enough size that it managed to push it through. It doesnt answer the question fully, but might be useful for others...

Answer 3

From your description it sounds like when a publication is deleted, Tridion probably wraps all the data changes in a single transaction.

For the publication you were deleting, the transaction resulted in the log filling up which caused it to fail AND for the tranaction to be rolled back. Because the transaction was rolled back it was removed from the transaction log hence when you ran the query to show free space in the Transaction log you still saw plenty of free space.

To me the most correct answer here is to grow the size of the transaction log files so it has enough space it can log the transaction. I would shy away from using techniques such as truncating the transaction log here as that can limit your ability to restore the database to a specific point in time if done outside of the backup process.

Answer 4

This question is a bit old, but just want to share that I run into the same problem today and I followed the below steps (based on Glenn comments), so those are documented in case anybody runs into same issue in the future

Error when trying to delete the publication:

Steps followed to resolve the issue

1.- In the properties of the Tridion CM Database, Files, increase the Initial Size (I increased from 256 into 856 for testing)

2.- Re Start the MSSQLSERVER Service

After doing this, I'm able to delete the publication with no issues

3.- Re Start the MSSQLSERVER Service

4.- Once you delete the publication, you can set the size to the original size

Comment: This error probably would not happen if you set the Auto-growth of the log file to true, but you need to keep an eye to avoid to grow out of control, so probably the safest is to keep with Auto-growth to none