May 8, 2008 at 2:06 am
I have recently changed our production database from simple recovery to full recovery and devised a backup plan so I can restore to a point in time.
However I have just found that the log file .ldf has grown to 30GB and has filled up the disk it resides on. Is there any way to reduce the size of this. I have already tried to shrink and to limit the size of teh file but I cannot reduce the file below the 30GB.
Help!
May 8, 2008 at 2:13 am
Hi
Immedietly schedule a transactional log backup job to take the transactional log backups on the database. And before that please do one full database backup and start the transactiona log backup.
Observe the log space usage by running dbcc sqlperf(logspace) and if no active transactions on the database available the space usage will be less then u can truncate.
Thanks and regards,
Veeresh
May 8, 2008 at 2:23 am
I mean not to truncate the log,. you can shrink the log file after finding the low usage of the log file.
May 8, 2008 at 4:57 am
Since you're using SQL 2005, you cna query the sys.databases view to see why the log is not been reused. There's a column with the log_reuse_wait_desc (I think)
How often are you taking log backups? Have your log backups actually been running?
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
May 8, 2008 at 10:53 pm
Hi Gail Shaw,
from the sys.databases I too noticed column with the name "log_reuse_wait_desc" can you please let me know how we can look into the solution using that.
Thanks
Dinesh
Dinesh
May 8, 2008 at 11:26 pm
CLEAN THE LOG FILE WITH BACKUP WITH NO LOG STATEMENT
May 9, 2008 at 12:00 am
Depends what the column says. It gives you the reason the log is not been reused.
Typical things I've seen - Replication, backup, active transaction, mirroring.
Each needs to be treated differently.
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
May 9, 2008 at 2:52 am
I have now resolved the issue by taking the databases off-line and forcing the log file to shrink to 2GB. I found that the log file was set to 10% increase unrestricted growth. I have now changed this to be limited to 2GB.
I have had to stop transaction log backups as they were too large and also filling up hard disk space. I now run 4 differential backups throughout the working day.
Can anyone tell me why the transaction logs are about 20GB each whilst the database is only 12GB?
May 9, 2008 at 2:56 am
Having queried this I can see that the log_reuse_wait_desc says "LOG_BACKUP". What does this mean?
Edit: The log_reuse_wait = 2
May 9, 2008 at 3:19 am
That you need to do a log backup before the space in the log file can be reused.
Neither differential nor full backups truncate the log. Only log backups do that.
If you don't do log backups, your log will grow until it fills the file.
Also, only log backups allow you to restore to point-in-time. Full and diff just let you restore to the last backup.
If you don't want to do log backups, and you don't care about restring to point-in-time, set the database into simple recovery mode.
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
May 9, 2008 at 7:08 am
I had the exact same problem and it took some experimenting, but this is what has worked for me. As a previous post has mentioned, take a full backup, and then take transaction log backups. But you might need to take 2 or more transactional backups, not just one. Then do a shrink, and you should see your transaction log shrink considerably.
After this initial maintenance, I just scheduled regular transaction log backups & fulls, and I haven't had to do a shrink again (except for some heavy transactional databases).
Hope this works for you.
-Beth
May 9, 2008 at 7:43 am
Darryl, be aware that if you restricted your log to 2GB and the log needs to grow beyond that, all processing will stop until someone manually increases the size or backs up the log to clear out some space. If disk is not the issue, leave it autogrow and change the frequency of your log backups to occur more often and keep the size more manageable.
-- You can't be late until you show up.
May 9, 2008 at 8:04 am
Darryl864 (5/9/2008)
I have now resolved the issue by taking the databases off-line and forcing the log file to shrink to 2GB. I found that the log file was set to 10% increase unrestricted growth. I have now changed this to be limited to 2GB.I have had to stop transaction log backups as they were too large and also filling up hard disk space. I now run 4 differential backups throughout the working day.
Can anyone tell me why the transaction logs are about 20GB each whilst the database is only 12GB?
The transaction logs track all of the changes to your data, be they inserts/deletes/updates or rebuilds of your indexes. So it sounds like you have a lot of changes, just not a lot of net growth (i.e. essentially a lot of updates, or an even amount of deletes and inserts).
Maintenance tasks are also logged, so they can also make up a goodly chunk of your transaction log size.
don't limit the growth of this file, or processing will come to a screeching halt once that size is reached. It sounds like you need a. increase the frequency of your log backups, and b. possibly look at reducing the frequency of some of your maintenance tasks (most indexes do NOT need to be rebuilt nightly).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply