Transaction Log file .ldf is too small

Question

Current database is almost 200GB however the transaction log file is only 4.1GB. I am worried if this influences the performance of databse and how could it be solved.

When I do this query

DECLARE @DatabaseName VARCHAR(50) ;
  SET @DatabaseName = 'dbname'
  SELECT name ,
    recovery_model_desc ,
    log_reuse_wait_desc
  FROM  sys.databases
  WHERE name = @DatabaseName

Show Recovery Model = Simple, Log Reuse Wait = Nothing.

Anyone has a knowledge why the log file is so small, and does this influences on the performance?

score 5 · Answer 1 · edited Apr 13 '17 at 12:42

The file size of the transaction log is in no way related to the size of the data files, so the question you've asked is really a non-starter to troubleshoot performance.

The transaction log stores the physical operations that happen to the database. Since the database is in SIMPLE recovery, the log only holds on to the operations until a CHECKPOINT occurs -- at this time, the transaction log that's no longer required is internally cleared and can be reused. The physical file size would not need to change unless the active transactions take up more space than is physically available. (Yes, I've simplified things a bit in this paragraph.)

That said, your transaction log could suffer from performance issues if there are too many VLFs, likely from the result of small autogrowth increments -- see my answer here for a bit of information about how the transaction log works internally with regards to this. It also depends what the workload is for this database (many small transactions, or few large transactions).

score 2 · Answer 2 · answered Oct 14 '12 at 19:08

The small log size will NOT negatively affect any performance OTHER than transaction recovery.

All the log file does is store transactions. If you aren't interested in being able to roll back transactions to a specific point, you should be ok. In simple mode, you would still be able to recover recent transactions.

Are you backing up the db? If so, this also potentially reduces the need for full recovery model.

Also, if you were to switch to FULL recovery model, your log file (.ldf) size would most likely be BIGGER than your db (.mdf) file size. So, be careful since that could become cumbersome to manage. I have had to deal with swelling log files before were the entire storage array fills up: not fun!

score 0 · Answer 3 · answered Oct 14 '12 at 19:27

I'm guessing your database is in simple recovery mode, meaning the transaction log is regularly purged automatically (simplified explanation). This means the size of the file will be smaller, at the cost of not being able to recover transactions to a point in time in case of a crash.

If your transaction log is in full recovery, a possible explanation could be that the file was created at a small size originally, and either there's simply not a lot of activity on the database, or the server just rebooted.

Typically, for production systems, I set my T-log size to 20-25% of the size of the data files. Having a small size shouldn't have a direct impact, but you'll suffer a performance hit if the log fills up due to high activity, and the OS then needs to grow the log (in case of auto-growth). This typically has an impact on performance as the file grows, as nothing can be written to disk during this time (again, simplified).

Transaction Log file .ldf is too small

3 Answers3