Settin a trigger or Stored Procedure for a Transaction Log 1

[klewis10367]

I am running SQL 2000 on a Windows 2000 Server and was wondering if there is a way to make a Transaction Log Backup automatically based off of the size of it. Right now I run a job periodically through the day to truncate and shrink. I was wondering if a trigger or a stored procedure would do it.

 

[ptheriault]

What you are doing doesn't make any sense. You should be running transaction log backups through out the day to backup and clear the transactions in your log. This will free up the committed transactions from the log so new transactions can use that space. Also you could restore your database to a point in time because you would have a backup of transactions that occurred since your last full backup.
If you don't need those transactions to restore your db to a point in time then just set your database to simple recovery. This will truncate your log on check point. By shrinking your transaction log you are just adding more over head to your server because the log just has to auto grow again to an appropriate size for your transactions.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[klewis10367]

Thanks for the reply. If I change to Simple Recovery do I need to run a transaction Backup still?

 

[ptheriault]

Thanks for the reply. If I change to Simple Recovery do I need to run a transaction Backup still?

No, you can't backup the tlog if a database is in simple recovery.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[klewis10367]

Thanks, I found that out when I changed my database to Simple and tried running a transaction Backup. Also On my databases that I am using Full Recovery for I am adding this following step to my Transaction Log Backup Job...

backup log "dbase" with truncate_only...

Will this hurt anything? Our server is low on space and If I do not truncate the log file gets rather large.

 

[ptheriault]

backup log "dbase" with truncate_only...

Will this hurt anything? Our server is low on space and If I do not truncate the log file gets rather large.

If you are going to do that just change the recovery model to simple. you can do that without a restart of the services or any interuption to end users. Simple recovery will keep the log as small as possible.

Either way you can't restore your database to a point in time and you are risking data loss.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[klewis10367]

Unfortunately, the specs for the particular application that we are running require us to run in Full Mode, in case there ever is a time when we need to back up from a Trans Log. I have never had to do this in 6 years but it is per their specs. In simple mode when the database backup is done does it just truncate the transaction Log or does it Truncate and Shrink. Once again, thanks for all of your help.

 

[klewis10367]

One last question. Would running a shrink DBCC once a week fragment the databases and kill performance or would this be fine.

 

[ptheriault]

Are you shrinking the data file or tlog file? I wouldn't shrink the data once a week. It would cause fragmentation and kill io when the database had to autogrow again.

- Paul
- If at first you don't succeed, find out if the loser gets anything.