Video Screencast Help
Protect Your POS Environment Against Retail Data Breaches. Learn More.

Monitoring DB

Created: 05 Feb 2010 • Updated: 06 Sep 2010 | 5 comments
AG81's picture

Hello to All,
I've have some  ID 41016 "The Directory database transaction log has used 99% of its allocated space" in my EV Server. Investigating about it, I've find one scheduled Job in SQLServer (SQL Server Agent => Job =>)  EV Monitoring History and executes this: "exec PurgeHistory". It is scheduled to run every day at 00:00.

In this interval hour, the EV is backuping up, and the services are in read only mode. After the EV Backup, it runs a full SQL Backup (with BE 12.5), and this backup shoud purge the transaction log, isn't it?

Do you think that I must modify the backup schedule? or de SQL Job Schedule for they don't execute at the same time?

Thank you!!

Comments 5 CommentsJump to latest comment

GertjanA's picture

Hello Agonzalez

Make sure that the backup job indeed backs up the transaction logs, and clears unused entries, and shrinks the transaction log

I'm no be expert, but you should be able to figure it out. As an FYI, we have our transactionlogs backed up every 15 minutes in sql... The backup-files are transferred to a shared disk, where actual backup takes them, and deletes them.

gertjan

Thank you, Gertjan, MCSE, MCITP,MCTS, SCS, STS
Company: www.t2.nl

www.quadrotech-it.com

www.symantec.com/vision

AG81's picture

Hello GerjanA,
The backup method for de SQL instance is full backup. I don't know if this method shrink de transaction log.
Thanks fot your answer, but my question is not solved yet... If you cant help me please.
If it possible that the transaction log is used at 99% beacuse de SQL job is running when the services are in backup mode?

Thank you!

Agonzalez.

Ameen's picture

Hi ,

I am not a SQL expert but i have reduced the size of SLQ T-log by running the below querry with backup.

backup log <your database name> with truncate_only

This querry will truncate all logs.

Regards,
Ameen

Liam Finn's picture

Ameen is correct. This script will do the truncate once. You should setup a maintenance plan to do this automatically ever 15  mins

Liam Finn's picture

Agreed truncate the logs daily or more frequent is the best solution. We truncate every 15 mins to disk then backup that disk daily and delete the truncated logs.

we also run a weekly job to shrink, re-org the data in the database to reclaim white space

A Full backup does not truncate the logs it just does a dump of the database and the logs into a .BAK file. You must truncate the logs at least daily but it is best to do it every hour if not every 10-15 mins

Your backup solution I recommend is full to .bak every day and truncate every 15 mins. Each dat backup the truncated logs to tape and then delete the truncated files off the disk to keep your disk space usage down