Rather perplexed with my SQL server Log file. With identical primary & secondary servers, the production server on live sends the log files to the secondary every 15 minutes.
Also, every evening, a full backup (using the maintenance plan wizard) performs a full backup to a file location on the secondary server.
However, the log file seems to continually steadily grow. The database is 43Gb in size, and the log file has steadily increased to nearly 81Gb now. There are only about 80 users using the database.
On top of the full backup, I added a transaction log backup just to see if this made a difference, but still the log file is expanding. There's a weekly re-indexing job that runs on a Saturday night.
Database is on SQL Server 2008R2 (was migrated from a 2005 server a few months ago). It's in 2008 compatibility mode now, Full recovery model.
Any help greatly appreciated!
Best Answer
This is a big no no. By doing a manual transaction log backup, you can break the LSN of the log shipping secondary and cause the restores to fail, unless you used the copy_only option, which doesnt allow for truncation anyway.
There are only a few things that can cause a log not to truncate/clear, an active transaction, the recovery model set to full w/ no t-log backups, or a feature that is not working properly (mirroring, replication etc...)
You can check for open transactions by running DBCC OPENTRAN in the context of the said database. Also, I know it may seem like a waste, but I would also confirm that your log backups are actually succeeding. It could be a situation where the backup drive is out of space and the backups are failing.
Edit: I forgot to add, when your log does truncate, you will want to shrink your log file as the number of VLFs will probably quite large and could degrade performance. You can use dbcc shrinkfile