Truncate transaction log?

  • We do a full backup nightly of our database and transaction logs, using separate maintenance plans for the database and transaction log. Backup is written to disk, and then copied to tape using Veritas Backup Exec. Disk backups older than 4 days are automatically deleted.

    We have gotten into trouble sometimes if I did a manual backup to disk because the extra files used up all the disk space, then the nightly backup failed.

    I recently discovered that the transaction log has not been backed up for quite a while. I'm guessing the reason it has failed is lack of space. All the history log says is, "Backup can not be performed on this database. This sub task is ignored".

    Since it has been so long, I suppose the file has grown quite large. Since we do a full backup every night, I should think I could truncate the t-log manually so that we can get started again, and then set the back-up period for the t-log to perhaps hourly.

    I don't have a clue how to do this. Can anyone confirm if this is the right direction to go, tell me how to proceed?

    Bill

  • Check the database...the issue might not be disk space. In Enterprise Manager, right click on the database, select Properties, go to the Options tab. Is the Recovery Mode Full or Simple? If it is Simple, then you CANNOT backup the transaction log.

    -SQLBill

  • The recovery model is set to Simple. I guess it was set to Simple because we had no need for the point of failure recovery at the time. We will need to decide what direction we wish to go.

    Are there any other ramifications of changing the recovery model? Will the maintenance plan still create same full daily database backup?

  • The maintenance plan will still do full backups.

    The main thing with using Simple vice Full is that you can't do transaction log backups, which in turn means you can't do 'point-in-time' recoveries. With only a full backup - it's all or nothing. You can also do differential backups in Simple mode.

    -SQLBill

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply