LOG file is growing

Question

Hi, I'm a MS SQL Server 2000 ( with PHP) user and I just need everyone's help on my abundantly growing database log file! I have a 2GB worth of database (mdf) and its log file grows up to 27GB and occupies my harddisk space! Hope someone could help me in determining how I could possibly resolve this file growth without making any impact on my DB's performance.

and if there is a way to decrease the size of this log file.

 

the database contains only one table of 11 records, banners  database...in the table there are 18 columns, all are normal but there is 2 columns: clicks and hits...so each time a page from our site or a banner is clicked theses 2 are updated.

 

In properties, Transaction log file:

"Automatically grow file"  is checked

"File Grow, By Percent" is checked

"Maximum file size" , "unrestricted"  is checked

 

Please I need Help

Answer 1

Check your recovery model and if its full or bulk logged take a transaction log backup using below command,
Backup log dbname to disk='Path\Filename.trn'
Then shrink the log file using below command,
Use yourdatabasename
go
dbcc shrinkfile('log file name',1024)
go

If your database is in simple recovery model truncate the log using below command and shrink using above command.
backup log dbname with truncate_only

The below link should help you understand better,
http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=Log%20file%20growth%20in%20SQL%20Server&referringTitle=Home

- Deepak

Answer 2

Hi thanks a lot for your reply, but please can you tell where to put these commands.

I tried to make a maintenance plan for the back up and then  i create a new job where i insert the 2 commands but it didn't run.

So please can you tell me how to do it, if i want to do back up for one time without a plan..

 

thanks a lot

 

Suzie

Answer 3

In SQL Server 2000, go to start - all programs - SQL server - Query analyzer (or go to start > run > type 'isqlw' and hit enter to open Query analyzer. Inside that you need to type those queries and run it.

- Deepak

Answer 4

Hello

Thanks a lot Deepak, it is done and the log file was reduced to 1 GB

Thank you very much..

 

Regards,