Stopping the Growth of Transaction Log Size 1

[Faheem786]

Hi there

I'm new to SQL Server 2000 and I need your kind support as I really have to find out the right solution.

Here is my problem:

I use SQL server 2000 to hold my product database. I often need to backup my database and restore it on my web host's server.

The problem is the actual file size is only 6 MB's but the log file grows day by day which is nearly around 200 MB's now. I'm not in a situation to hold any important data on the log file but I need to backup and restore often.

Is there any way to restrict the log file growth. I just dropped the database, recreated and

using the SQL Enterprise Manager --> Database --> My Database --> Properties --> Transaction Log, I just tried to restrict the Maximum File Size (to 2 MBs) using file properties. But when I update the database one or two times I get an error message saying that I need to free up some spaces for the log.

Could you experts please help me to get rid of this problem? Hope you will guide me.

Thanks
Faheem

 

[RickCole]

The quick simple solution is to have the truncate log on checkpoint option set on your database. This can be achieved via Enterprise Manager and going to the options tab with properties of the database.

However before you do this have a look at the following link for some useful background information.

http://support.microsoft.com/support/kb/articles/Q110/1/39.asp

Also have a look in BOL under the topic backup. A good understanding of SQL Server backup facilities work is well worth knowing about.

Rick.