Tuesday, November 11, 2008 10:54 AM
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
Tuesday, November 11, 2008 1:02 PMModeratorCheck 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,
dbcc shrinkfile('log file name',1024)
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,
Friday, November 14, 2008 11:29 AM
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
Friday, November 14, 2008 6:10 PMModeratorIn 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.
Monday, November 17, 2008 9:24 AM
Thanks a lot Deepak, it is done and the log file was reduced to 1 GB
Thank you very much..