October 28, 2005 at 10:21 am
Hi everybody, thanks to all that had help me before, your advices are very important to me. I have one big problem, once I make a database I set File growth to MB and all is normal but just for a time; 4 or 5 weeks later I notice that Log File is about 4GB meanwhile MDF is on 1GB. It is a big database, the sales of a year of a selfservice company, but I have found this problem of growth in diferent servers of this company, 4 of 10.
Does somebody know why and how I fix this? Thanks in advance
October 28, 2005 at 7:29 pm
Hi,
Reindexing, delete,drop and many other statements are reason for log file growth.
If u dont want that logfile should grow then change the database recovery mode to simple.
But then u can not tale Tlog backup.
U can also truncate the log file.By using this command.
Backup Log <database name> with truncate_only
dbcc shrinkfile(logical name u want to shrink,initial size)
u can get the logical name of logfile from sysfile in the database.
initial size is the size u want that logfile should shrink to.
for more information read in BOL Truncate Log file.
HTH
from
Killer
November 2, 2005 at 7:45 am
For the Sales database and with the said log growth it sounds obvious that number of transactions are high. In that case I will suggest having Recovery mode full, because keeping Recovery mode Simple you
are always going to loose the changes affected after last full backup, which is a scary thing to do, there is no reason to take for granted that this many number of transactions after last full backup are not worth keeping safe.
Here is what I suggest with database size of 1 GB ,
Create Job that take full DB backup every night which should not take more than couple of minutes. Have 2 or 3 log backup in this 24 hours, that should help reducing the log growth. Another important thing to do in DB Backup job every night is keep first step as
Backup Log Sales With Truncate_Only
DBCC Shrinkfile(Sales_log, 150)
[Db name Sales and log size 150 mb are used as example, you should replace that with the name and size you need]
This will bring log size back to 150 MB and next step of this job will take full Db Backup which ensures you are not loosing any data changes at all.
Kindest Regards,
Sameer Raval [Sql Server DBA]
Geico Insurance
RavalSameer@hotmail.com
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply