SQL Server with small database size and huge log file size I have several databases, for some log file size is growing more than normal even though very little data is added to few table around 50-100 rows a month. i.e. Database 12 data file size is 79MB and it log file size is 833MB, and over a week log file size has increased by almost 100MB while only about 100-150 records were added into one of the contact table using web form which only have 5-6 fields.

Similar issue with database 1 , database size is 16MB and log file now is around 554MB, database 1 is a test database and no data was added to this DB over last 1 year.

We have tried shrinking database that didn't make much difference...

Not sure what should i do next &amp; why database log file is so huge. Also when we take backup .bak file those files are also large as log files example Database 10 .bak file is also around 800MB.

Production server is SQL Server 2014 Standard edition



Recovery model for Database 10 is Simple and for all other databases its Full

could this be the reason for large log files
Could this be the reason for large log files  


Yes, particularly with FULL recovery model and if there is NO frequent LOG backups as the in-active VLFs in LOG file cannot be truncated without backup in full recovery model.   

With Simple recovery model LOG backup is not the case, most probably at one point of time there might have been a heavy transaction/Index maintenance online performed and to accommodate that heavy operation LOG size increased, from that moment on-wards the in-active VLF of log file would be truncated automatically in simple recovery model but that would free-up the space in LOG file not reduce size of LOG file unless the truncation performed manually (DBCC SHRINKFILE). To check used and free space of LOG: DBCC SQLPERF ('Logspace')

Shrinking file (in your case with simple recovery) is not recommended as there might possibility same amount of space required in LOG file to accommodate upcoming heavy transaction.  


  We have tried shrinking database that didn't make much difference.  


Assuming you tied DBCC SHRINKFILE to shrink the LOG file size in full recovery model, if there is no impact with SHRINKFILE command there must still active portion on LOG which would not allow us to truncate, usually when you look at message section after DBCC execution it clearly indicates. Also you can check what stopping the SHRINK operation with following query

Descriptions of log_reuse_waits 

select  name, 
        log_reuse_wait_desc, 
        is_distributor,
        is_merge_published,
        is_published,
        is_cdc_enabled
from sys.databases 


You may want read detailed explanation on LOG SIZE GROWTH



If you're not concerned on point in time recovery   


Perform LOG backup of a database 
Change recovery mode to SIMPLE (ALTER DATABASE YourDBName SET RECOVERY SIMPLE)
Shrink the file size (DBCC SHRINKFILE (2, 1024))  


  2 indicates file ID
  1024 indicates new size of file (keep enough space as per your workload)

Since the LOG backups are not maintained, keep the database in simple recovery mode so that it wouldn't grow rapidly