Friday, December 07, 2012 6:51 PMFor some reason we have two log files on one of our production database. We have transactional replication setup on it. We are experiencing a latency issue during index rebuild/heavy inserts/ heavy deletes operation. I think it's expected. My question is as there will be a increase in the log file size during the above operations, both the logs are growing at the same rate. Not sure why. As log files are written sequentially why both the log files are growing at the same rate? Does the log reader agent tries to search for replicated transactions in both the log files?
Sunday, December 09, 2012 12:52 AMModerator
My question is as there will be a increase in the log file size during the above operations, both the logs are growing at the same rate. Not sure why. As log files are written sequentially why both the log files are growing at the same rate?The log files are growing at the same rate because the auto-growth settings. The engine writes log records sequentially but what you are seeing is the log files become full, and not resusable. The engine will then grow one log file, fill the space, then grows the other and fills that space, and so on.
Does the log reader agent tries to search for replicated transactions in both the log files?Yes, the Log Reader Agent will search for replicated transactions in both log files.
Tuesday, December 11, 2012 2:26 PM
Both the logs are set to restricted growth to 2097152 MB and both in the same drive. Currently the log file sizes is 149 GB and the free space in both the log files is 148 GB. The log sizes are growing continuosly because of the rebuild and delete operations and both the files used to be 60 GB which increased to 150 GB in 8 months. Why the both log sizes are growing when a log file is set to grow to 2 TB?
Wednesday, December 12, 2012 2:16 AMModerator
Wednesday, December 12, 2012 12:53 PM
Autogrowth is set to 50 MB for both the log files.
Wednesday, December 12, 2012 1:28 PMModeratorTry to empty one of the log files into the other. This may take many attempts to do and should be done during a time of low activity. Each time you try it limit the amount the size the file can grow. At some point you should be able to remove the second log file.
looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
Wednesday, December 12, 2012 2:31 PM
We also have log shipping and replication setup on this database. Currently each of the log files size is just 802 MB, rest almost 250 GB free space. When I checked the dbcc loginfo just one vlf related to primary log file is in use. So I think we can just directly shrink the second log file and reduce it to minimum size and delete it if no vlf's are active in it.
Wednesday, December 12, 2012 5:38 PMModerator
You will want to alternate backing up the log and shrinking the log, checking the virtual log file layout using dbcc loginfo to see if the file is still in use after each step. Once the file is no longer in use you can issue an ALTER DATABASE REMOVE FILE operation to remove the log file.
Afterwards, check sys.database_files to verify. You may find SQL Server is reporting the file as OFFLINE. If so, backup the log again and it should be gone from sys.database_files.
- Marked As Answer by Maggie LuoMicrosoft Contingent Staff, Moderator Tuesday, December 25, 2012 5:34 AM
Sunday, December 16, 2012 2:12 AM
see below links:
Thanks Shiven:) If Answer is Helpful, Please Vote