MagE and SQL DB size – what can be done? (4352549)

MagE is storing the logs in the logs table in the SQL DB (besides writing the information to regular log files), and this is resulting in enormous growth of the SQL DB.

What can be done?

Starting with the cumulative hotfix 20131125EX (and later) the behavior of the agent has been changed. Now newly installed MagE instances will not store the information in the SQL DB anymore.

Important:

1. the new default setting (not to store the logs in the SQL DB) does not apply to already installed and working agent instances.

2. by default new agents will always write extended logs, there is no need to turn extended logging on explicitly.

3. Always perform a backup of the SQL DB before beginning with any SQL DB related work.

To stop the existing agents from writing logs to the SQL DB one can execute the attached script.

Or copy the syntax below, use it to create and populate a file, eg. MAgE_Set_SQLLogKeepPeriod=0.sql, and then execute the file.

DECLARE @PROPERTY_NAME NVARCHAR(50)

DECLARE @PROPERTY_VALUE NVARCHAR(2000)

SET @PROPERTY_NAME = 'SQLLogKeepPeriod'

SET @PROPERTY_VALUE = '0'

UPDATE AGENTPROPERTY

SET VALUE = @PROPERTY_VALUE

WHERE BINDINGID IN(

            SELECT APB.ID

            FROM AGENTPROPERTYBINDING APB

                        INNER JOIN AGENTPROPERTYSCHEMA APS ON APB.SCHEMAID = APS.ID

            WHERE APS.NAME = @PROPERTY_NAME AND APB.AGENTTYPE=18

            )

--------------------- 

If needed one can use the following queries to shrink the database file.

Open a new query window and run the following query against the project database.

TRUNCATE TABLE dbo.LOG

After this has been done one can use the below query to shrink the DB, where (MMEXProject) is the name of your Project Database.

USE [MMEXProject]

GO

DBCC SHRINKDATABASE(N'MMEXProject' )

GO

Then the below query will shrink the files.

USE [MMEXProject]

GO

DBCC SHRINKFILE (N'MMEXProject' , 0, TRUNCATEONLY)

GO