SQL Server with small database size and huge log file size

Question

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 & 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

enter image description here

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

could this be the reason for large log files

Answer 1

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

1. Perform LOG backup of a database
2. Change recovery mode to SIMPLE (ALTER DATABASE YourDBName SET RECOVERY SIMPLE)
3. 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)

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

• Learning: Appreciate your reply, i am not a DBA person, i am facing this issue with the Hosting company as backup size .bak file is growing and difficult to download large files & time to take backup also. Hosting company doesnt have idea about resolving this issue as they share this SQL SERVER with other clients also. I was thinking if i cant so domething from my side to reduce the log file size as the access to database also
• Shekar Kola: Hi @Learning, i see, in that case if you got full control over the databases (db_owner), perform the steps as mentioned in my edited post
• Learning: I changed it using Management STudion first i changed recovery to Simple and then i was able to shrink file, but for some it show status as "Replication" for these i am not able to shrink database. i managed to shrink log file only for those whos status was showing as Nothing..
• Shekar Kola: @Learning, if the database part for replication (you can validated with updated query in my answer), you need to wait until the current transactions replicated. If replication not enable, that means the database might have been restored from replication topology, in that case you can run sp_removedbreplication , after a while REPLICATION must disappear at log_reuse_wait_desc