How do I remove or shrink 250GB GP log file

[knesja]

Hi. We are a small company running GP 9.0. We do not have in-house IT personnel, and our out-sourced IT guys don't really know GP. Turns out we have an absolutely HUGE log file ... 250GB and growing. How do we (1) shrink or get rid of this huge file and (2) limit its future growth?

Many thanks in advance for any assistance you can provide.

Keith

 

[awaria]

Keith,
these steps are performed in SQL, not through the Great Plains UI.
You need to open SQL Mgmt Studio and Query Analyzer. Your IT guy doesn’t need to know GP to purge the log file. He needs to know SQL. The one task I would recommend in GP, would be to create a test database, if one does not already exist, then first runs these against that database. This would happen under GP Utilities, to create a new database.

* test any of these processes on a test database,bkp and restore target database into another database for testing. first to ensure you have the steps documented properly and you receive the desired results. A backup or test GP database.
** make sure no one is using that database before performing either process.
*** also make sure to backup the database prior to running these processes.

To purge the log file, via SQL you could detach the database, rename the log file, then re-attach the database without naming the log file. The system will create a new default empty .ldf file that will only be about 100K in size.
---------------
sp_detach_db database_name'

* rename .ldf file and don’t reference the .LDF in the attaché query, just name the .MDF file

sp_attach_db @dbname = 'database_name',
@filename1='F:\Program Files\Microsoft SQL Server\MSSQL$PROD\Data\database_name.mdf'

//skip this portion of the query that would re-attache the original log file that you want to purge,
@filename2='G:\Program Files\Microsoft SQL Server\MSSQL$PROD\Data\database_name.LDF'
//


You could also shrink the log under properties of that database in SQL Mgmt Studio. Right Click on database and under TASKS, should be option to select files for shrinking

To limit growth, make sure limit file growth in that database properties.
Set db & log size growth to 10%

Your Maintenance job can be set to truncate log file on backup as well.
Good Luck, again, be sure to test in a test db first, then when ready, backup production database prior to performing tasks. Must be done when no users are logged in to GP database.

awaria