I moved to a new company recently wherein they have 2 databases in a SQL server of size 20-25 GB. I'm unable to shrink the log file of the databases.
Transaction log backup of the databases are set to run every 30 mins from 6 AM-7PM.
They are of size 10 and 2 Gb respectively.
I tried to shrink it around 8-9 PM last night but I was not able to do.
The log_reuse_wait column stated it's waiting on a log_backup.
I tried taking couple of log backups which didn't help.
I ran DBCC OPENTRAN but there are no open transactions.
Also, this morning even after the Tlog backups started at 6:00 AM till now(10:30) it still shows LOG_BACKUP in log_reuse_wait column.
And no OPEN TRAN as well.
When I researched on this I found this article from Paul Randal: http://www.sqlskills.com/blogs/paul/why-is-log_reuse_wait_desc-saying-log_backup-after-doing-a-log-backup/
It states that if the DB is small and all the content of tlog file is in same VLF it couldn't truncate.
I have some 300-400 VLFs in the Databases.
So that doesn't make sense here as well.
One thing I noticed that every morning at 6 AM the tlog is a little bigger than the other ones.
So I'm thinking to check with the lead DBA to run tlog round the clock.
Other than that, I would like to see why is this happening and Am i missing anything.
I tried to shrink with the below command:
Use DB1 go DBCC SHRINKFILE(2,10) GO
Note: It's SQL 2012 instance running 0n 2012 compatibility