Solved

Sql Server .ldf file size

Posted on 2013-06-04
5
189 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

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 …
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

867 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

16 Experts available now in Live!

Get 1:1 Help Now