Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 205
  • Last Modified:

Sql Server .ldf file size

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
cp30
Asked:
cp30
  • 2
  • 2
1 Solution
 
QlemoC++ DeveloperCommented:
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
 
sarabhaiCommented:
0
 
cp30Author Commented:
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
 
QlemoC++ DeveloperCommented:
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

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now