Microsoft Dynamics GP (Archived)

SQL log grows unexpectedly

(0) Share

Report

Posted on by ilya r

I am running MS Dynamics GP v10 and MS Sql 2005 x64

Issue: Our SQL GP database log becomes incredibly large and causes to fill up the whole disk

Has anyone ran into similar problems and what are some of the solutions? Thanks, Ilya

*This post is locked for comments

I have the same question (0)

All responses (19)

Answers (0)

Community Member on at

Like (0)

Report

You might want to check the following lines in the Dex.ini file and verify that they are set to 'FALSE'

SQLLogSQLStmt=FALSE

SQLLogODBCMessages=FALSE

SQLLogAllODBCMessages=FALSE

If set to TRUE the log records all transactions.

Debi

Was this reply helpful? Yes No
ilya r 60 on at

Like (0)

Report

They are all FALSE.

I checked the SQL backups and they stopped running since last month because the disk got full. The backups are supposed to clear the log file each time and that stopped working. So I think I have to figure out what happened with the backups. Does anyone have any recommended settings or configurations for setting up a backup plan for database and logs?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Not really GP specific, here is an article that may help out somewhat.

I've used this script inside a maintenance plan in SQL that runs once a month:

declare @ssql nvarchar(4000)
set @ssql= '
        if ''?'' not in (''tempdb'',''master'',''model'',''msdb'') begin
        use [?]
        declare @tsql nvarchar(4000) set @tsql = ''''
        declare @iLogFile int
        declare LogFiles cursor for
        select fileid from sysfiles where status & 0x40 = 0x40
        open LogFiles
        fetch next from LogFiles into @iLogFile
        while @@fetch_status = 0
        begin
          set @tsql = @tsql + ''DBCC SHRINKFILE(''+cast(@iLogFile as varchar(5))+'', 1) ''
          fetch next from LogFiles into @iLogFile
        end
        set @tsql = @tsql + '' BACKUP LOG [?] WITH TRUNCATE_ONLY '' + @tsql
        exec(@tsql)
        close LogFiles
        DEALLOCATE LogFiles
end'

exec sp_msforeachdb @ssql

Was this reply helpful? Yes No
Ron Wilson 6,010 on at

Like (0)

Report

We had this problem before and it ended up that the full nightly backup wasn't truncating the log data after it had been committed. I cannot remember exactly what was causing this issue, but I think it had to do with the Recovery Model of the database properties being set to Full or Simple. Sorry I can't be of any more help, but if you Google sql recovery model you might find some answers.

Was this reply helpful? Yes No
Ron Wilson 6,010 on at

Like (0)

Report

I did a quick Google for "sql recovery model full vs simple" and this was one of the first hits:

http://www.bigresource.com/MS_SQL-Transaction-log-still-growing-when-in-SIMPLE-recovery-mode-uo9IrcRo.html

It looks like it might have some explanations for you.

Was this reply helpful? Yes No
ilya r 60 on at

Like (0)

Report

Do you remember how you solved this issue? Looks like we are having the same problem. We are using the Full recovery model. Really need to get this resolved :(

Was this reply helpful? Yes No
Ron Wilson 6,010 on at

Like (0)

Report

Unfortunately, I do not remember. We outsourced that issue to our IT firm that we use when we can't do it ourselves. It was money well spent for us....it may be for you as well. I wish I had a better answer for you.

I did verify that we are using the Full recovery model as well, so there has to be a way to do it.

Was this reply helpful? Yes No
Suggested answer

Beat Bucher GP Gee... 28,058 Moderator on at

Like (2)

Report

Hi Ilya,

I know this thread is fairly old, but did you managed to resolve your issue ?

When you set your GP databases to 'Full' recovery model, you have to take care of the log transactions and do backup them on a regular basis, otherwise they don't get 'purged' over the time, and that explains the uncontrolled growth of your database LDF file and thus running out of disk space.

I'd also suggest that you run a scheduled job in SQL to shrink your MDF & LDF files at least once a month, but this only works if you take care of proper DB backups (both files).

We do run our GP environment with over a dozen companies all in Full recovery model, and I have scheduled Transaction Logs backup every 15 minutes during the day and one full backup at night. This takes care of my data and never had issue with uncontrolled file growth.

Hope this helps.

Was this reply helpful? Yes No
L Vail 65,271 on at

Like (0)

Report

Boy, this IS an old post. I'm surprised nobody just answered like you did Beat. Sometimes I've had to do a separate maintenance plan to backup the tlogs. Also, they need to include the clean up routing that will delete the log files (and db backup files) after so many days. I don't shrink the db and log files on a regular basis; I only do it before upgrades, perhaps I should consider including that in my maintenance plan.

Kind regards,

Leslie

Was this reply helpful? Yes No
Victoria Yudin 22,769 on at

Like (0)

Report

Hi Leslie and Beat,

My understanding is that shrinking SQL databases can negatively impact performance and should not be something you do regularly.

Here is a great blog post with a number of helpful links (and some more in the comments) as to why shrinking databases is bad: www.brentozar.com/.../stop-shrinking-your-database-files-seriously-now

While it was written a number of years ago, to my knowledge nothing has changed in this regard.

Was this reply helpful? Yes No