SQL 2005/2008 maintenance plan to shrink and limit the transaction logs of sem5 database
TECH194305
Last Updated August 14, 2013
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
Imported Document ID: TECH194305
Subscribing will provide email updates when this Article is updated. Login is required.
Thanks for your feedback. Let us know if you have additional comments below. (requires login)