SQL Master log not trunicating

[Don_Bentz_2]

I have a clients sql server with a the master log file at around 57 gig but the database is only around 15 meg. If I run the Sql job it fails on trunicating it, however from what I understand is the Veritas Backup SQL option will handle the trunicating on full backup. I thought I'd manually run the sql maint plan to see if there was a problem with the veritas part but since it fails I'm scratching my head.

Anyone with thoughts?

Don

[Joshua_Small]

Your master SQL file really doesn't justify a Full recovery model anyway.

Whilst it may be an important file, it's one that changes rarely.

Open Enterprise manager and put it in simple mode.

There are various manual shrink operations you can use to drop the size down after this- google around on "sql shrink logfiles" or similar.

[Don_Bentz_2]

It's actually already in Simple Mode. Master.mdf is now at 10 meg (Ran shrink and it went down 15 to 10) however mast.ldf is still sitting at 57.3 gig. This file seems to grow each night and doesn't seem to trunicate.

Any thoughts before I jump on a MS PSS call?

Don

[Joshua_Small]

If the database is already in simple mode, it's definitely not Veritas' job to shrink the log. That's to do with flushing it after a backup *in full recovery mode* only.

You can try the following, from SQL Analyser:

DBCC SHRINKFILE(master_log, 10)
BACKUP LOG master WITH TRUNCATE_ONLY
DBCC SHRINKFILE(master_log, 10)

And then see what happens.

[chris_Knott]

I actually run 2 seperate SQL backups jobs. 1 to backup the SQL DB's and a second job to truncate the log files.

[Joshua_Small]

Hi Chris,

Again,

From Veritas's point of view, truncating log files doesn't make alot of sense if you're running in simple mode.
You should remove such a job if you have one.

Have you tried the given commands to see their effect on the file size?

[shweta_rege]

Hello,


What recovery model did you set for the database?

The transaction logs will be deleted in two cases :

- If the database recovery model is Full and the transaction logs are backed up using the "Log" option.

- If the database recovery model is Simple




Thank You,

Shweta