May 14, 2009 at 7:47 am
Hi There,
Can anyone give me some advise to shrink the logfile on a SQL 2005 production server.
There is only 1 ldf and 1 mdf file for the database but the ldf file is already over 20gb.
All the backups is done daily but the ldf file still don't shrink.
If I try to shrink the db manually it is still not possible cause I will only be able to free about 150mb.
Is there anything else I can do to get the ldf file smaller without putting the server at risk?
May 14, 2009 at 7:52 am
are you doing regular transactional log backups?
if not do these first as these will mark space in the logs for re-use
May 14, 2009 at 8:03 am
If the recovery mode is set to full you have to make transaction log backups regularly (each 5 or 10 minutes) . The period depends on the workload.
Transaction log backups actually truncate the log but they don't shrink it.... In any case if you backup your transaction log enough regularly then it will not grow that much...
May 14, 2009 at 12:28 pm
Thank you, I am busy doing a transactional backup.
I only have a HDD space issue at this stage.
Hopefully everything will be sorted out now.
May 14, 2009 at 2:59 pm
Please read through this - Managing Transaction Logs[/url]
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply