Friday, June 5, 2015

Gentran Archives and Audit Log Purge Not Completing due to SQL Log Error

So recently I really had to jump through some hurdles to clean up a Gentran Server. Scenario: Archives and Audit Log Purge were running weekly and daily (respectively) but we not completing do to SQL database log being full errors:

Process Control - [Microsoft][ODBC SQL Server Driver][SQL Server]The transaction log for database 'GENTRANDatabase' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
Error returned: 0x80040E14
IDispatch error


Now the official solution from Sterling Commerce in the past has been to adjust the number of days back until the process can complete a full run, then keep adjusting and running the process until you get back to the desired number of day. I've had to do this and it sucks and the problem isn't even Gentran, it's an SQL issue. Not to mention that the server I was on had over 300 million log entries going back to 2012 and they were already having disk space issues. So after a bit of research this is how I got the server back into shape over the course of a weekend.

1st I ran the following SQL script on the SQL Server so I could capture the size of the Gentran servers SQL log file.

 
SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) = 'GENTRANDatabase' 


2nd, I had to reclaim some disk space. So besides all the normal stuff like clearing tmp files, and old download stuff, I wanted to capture and use the 2GB that was being used by the existing Gentran database log file. To do that I found the following SQL script on  MSDN, which if ran correctly should quickly chop that file down to about 1MB.


 
USE GENTRANDatabase;
GO

-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE GENTRANDatabase
SET RECOVERY SIMPLE;
GO

-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (GENTRANDatabase_Log, 1);
GO

-- Reset the database recovery model.
ALTER DATABASE GENTRANDatabase
SET RECOVERY FULL;
GO


3rd, We need to set it so the GENTRANDatabase log file can grow without limits for a while. In the SQL Server administrator application, Right click on the Gentran Database and select Properties.



When the Properties window comes up select the Files page, which should have both a database and a log entry. Select the Autogrowth option button for the Log entry.



When the Autogrowth screen comes up select the Unrestricted File Growth option and select OK, then OK again when you get back to the Gentran Database properties.





4th, At this point try running your archive or audit log purge. Note, use discretion, if it's a big one you may want to cut the days in half and just repeat the above process if you feel that disk space is becoming an issue due to the database log size growing to fast.

Final note, once you are happy with the results don't forget to reset the log file size back to either the size you found it. By default on Gentran we would normally set the log to 2GB for a medium volume system.