Solved

Sql Server .ldf file size

Posted on 2013-06-04
5
188 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
  • 2
  • 2
5 Comments
 
LVL 9

Expert Comment

by:sarabhai
ID: 39218455
0
 
LVL 68

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 68

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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

743 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now