Sql-server – SQL Server with small database size and huge log file size

backupsql serversql server 2014transaction-log

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

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

could this be the reason for large log files

Best Answer

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

Related Solutions

Sql-server – Shrinking the log file does not reduce size

Here is the answer to my own question.

Run the below query to get information about the log file's reuse wait:

SELECT log_reuse_wait_desc
FROM sys.databases
WHERE name = 'DBName'

I got the following output:

log_reuse_wait_desc
-------------------
REPLICATION

There were some replication-related objects remaining in the database, even after removing the replication.

To remove the replication from the database, sp_removedbreplication can be used. But it didn't work for us as replication was not active at the time and actually replication had been removed long before.

The solution was to import the database contents to another database using the import option of SQL Server.

Sql-server – Fix transaction log file after database update

Short answer:

Just leave the log file as big as it typically needs to be, and stop worrying about it.

Longer answer:

First, why do you want to keep shrinking the file? If it's just going to grow again (and keep in mind that shrink and grow operations are expensive, especially for the log), then what did you gain? What did you use all that freed up space for in the meantime? Please read this page, in full, this article, and this blog post, and then come back with further questions. You're not seeing 8 million people on the Internet tell you not to shrink for no reason: it's a really bad and pointless exercise in almost all cases. There are exceptions, but this does not sound like one of them.

Sometimes I need to execute the shrink multiple times

I've addressed the primary reason for this in this answer. Essentially, SQL Server has to have two checkpoints or two log backups in order to wrap around to the reusable portion of the log. (This has to do with the number and arrangement of the VLFs in the log file, and where the current LSN points. We could post a book here but the easy answer is to just do what you're doing or run an additional log backup before starting.)

Another possibility is that the log can't be reused for a variety of reasons. Next time you try to shrink the file and it doesn't shrink on first try, check sys.databases.log_reuse_wait_desc. For example, if you're also using replication on this database, this can be caused by that activity, an un-replicated transaction, etc.

But usually it's just the wrap-around thing. So next time, issue two manual log backups - I don't see why you need to wait for the 15 minute interval (unless additional, smaller log backups in between will throw off any manual processes you have around your AGs). Or (as per above) just stop performing this pointless work - especially since that work is done over and over on every replica. Once again, this is useless effort you're putting in with the net result of: you freed up a little disk space for a little while.

Best Answer

Related Solutions

Sql-server – Shrinking the log file does not reduce size

Sql-server – Fix transaction log file after database update

Related Question