Solved

Sql Server .ldf file size

Posted on 2013-06-04
5
193 Views
Last Modified: 2014-12-01
Hi there,

Everything I've read seems to indicate that, because my database is in SIMPLE recovery mode, that my .ldf should not grow that much.

We have a database that is around 5GB in size, it is backed up every night (full backup) and we only ever need to revert to the previous nights backup in the event of an emergency.

The majority of data within the database will change constantly during the day, data becomes stale very quickly, and is replaced by fresh data.

Currently the .ldf file is 7GB but with only 1% in use, we have shrunk the database in the past but it has now grown again and I understand that it is not good practice to keep shrinking on a regular basis due to potential disk fragmentation.

Is there any advice on how I can reduce the size of the .ldf file or if that's the kid of size I should expect with so much data changing so often throughout the day?

My main issue is that the server disk is backed up every night and the backup files are too large currently and would like to reduce the .ldf size if possible.

Many thanks
0
Comment
Question by:cp30
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 9

Expert Comment

by:sarabhai
ID: 39218455
0
 
LVL 69

Accepted Solution

by:
Qlemo earned 500 total points
ID: 39218520
sarabhai,
Did you read the question? The DB is already in Simple Recovery Mode ...

cp30,
I would expect the Transaction Log to be significantly smaller, unless you have a lot of overlapping changes, or big amounts of data are changed within the same transaction.
If you are able to shrink the log at all times, I assume maintenance jobs (reindex, recreate stats, ...) are causing this - and you'll just have to accept that.
If the shrinked log file doesn't get significantly resized within 24 hours, there are probably some operations like data imports, cleanup, generation of staging data, or something alike running. In that case you might consider to include a shrink operation after those operations, but only if they are not running often.
0
 
LVL 9

Expert Comment

by:sarabhai
ID: 39218549
0
 

Author Comment

by:cp30
ID: 39218879
Thanks Qlemo, I wil,l shrink it and keep an eye for 24 hours and see what happens.

Another question, my server is autoatically backed up each night (full once a week, incremental each day) by my hosting company, is there any point in including the .mdf and .ldf files in the backup or should I just exclude them, just wondering if they'd ever work as a normal file restore if they were backed up when in use, or if we'd have to go back to latest backup anyway?

Thanks
0
 
LVL 69

Expert Comment

by:Qlemo
ID: 39219212
Depending on the method of backup (i.e. file system backup versus Volume Shadow Copy), the backup might be invalid anyway. I would not include the .mdf (nor .ldf) in the backup. Instead, a SQL backup should be made, and that included into the file system backup. SQL backups can also be made differential, if necessary.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question