SQL 2005/2008 maintenance plan to shrink and limit the transaction logs of sem5 database

book

Article ID: 156857

calendar_today

Updated On:

Products

Endpoint Protection

Issue/Introduction

Unable to access the SEPM console (12.1, 12.1 RU1 and12.1 RU1 MP1) connected to SQL database;
The SQL transaction logs fill up;
 

In the scm-server-0.log the following error is found:


Unexpected server error. in: com.sygate.scm.server.servlet.StartupServlet
com.sygate.scm.server.metadata.MetadataException: The transaction log for database 'sem5'
is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column
in sys.databases

Cause

The SQL transaction logs of the sem5 database fill up

Resolution

To solve this issue we have to build a maintenance plan in the SQL management studio
to shrink the (sem5_log1 and sem5_log2) and limit the size of these logs in the SQL 2005 or
2008 server.
To build a maintenance plan to shrink and limit the transaction logs:
Open the SQL server management studio and log in at the database engine;
Expand SQL server agent, and under Jobs right click an select New Job;
Name the job, select his owner and add a description for it;
At the left pane click Steps and at the bottom of the page click New to add a step;
Name the step, the type must be Transact-SQL script (T-SQL);
The database must be sem5 (default database name for SEP);
At the command field you should insert the following lines:
DBCC SHRINKFILE (N'sem5_log2' , 50)
GO
DBCC SHRINKFILE (N'sem5_log1' , 50)
GO

Where (sem5_log1 and sem5_log2) are the names of the transaction logs for the sem5
database, and the number 50 is the limit of the log size, set to 50 Megabytes;

Click ok to close this step;
At he left pane click Schedules and then click new to add a schedule to this job;
Name the job schedule,
The frequency should be set to daily and the daily frequency is set to every 2 hours
(this adjustment were done to huge environment with a heavy load to the sem5
database);

Click ok to close this schedule and ok again to save and close this job;
 


Applies To

Windows 2008 Enterprise (64 and 32 bits)

SEPM 12.1, 12.1 RU1, 12.1 RU1 MP1

Attachments