"Controlling " transaction log files

[micang]

SQL 2000

Hi All,

My transaction log files are growing to big sizes. I have shrunk the databases which has shrunk the tansaction logs.

I am not experienced in setting up SQL 2000 and pretty much just chose default setting upon installation. I assume that by default it allows the trans logs to grow. Is there a setting that I can prevent these trans logs from growing to huge sizes? I assume this does not harm the server.

Any help appreciated.

Michael

 

[ptheriault]

You must backup your log files.
If you have a to restore this database for some reason do you need to restore to a point in time. For example:

You backup your db everynight at 12 AM
Your users come in at 8 and enter data all day long
At 8 PM your server crashes. If you are not doing incremental backups to you transaction log then you have just lost a complete days worht of data. Can you or your company afford that? In most cases the answer is no.

So you should have a schedule that look somthing like this. It will depend on your companies needs.
12 AM full databackup
When that completes.
start incremental backups. like every 30 min to run till the next full databackup.

Now I use backup devices. So my full database backup overwrites the previous day.
My firt log backup of the day will init the device. (Clear out previous days incrementals)
Then I start incrementals.

To shrink your log. Back it up then run
DBCC SHRINKFILE (db_log_name, truncateonly)

If you don't need to recover this database to a point in time. The just set your database recovery model to simple. This will cause your transaction log to truncate on checkpoint.





- Paul

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

 

[micang]

Thanks again Paul for the explanation.

To date I have been doing complete database backups every night. I didnt even see that you can do transaction log back up's. Am still not 100% sure the complete workings of a mdf and a ldf.

Thanks for all the info, much clearer on things now.

Michael