I want an answer of question and that would be highly appreciated


First of all i am an IT guy from Sports Field and using SQL Server 2008R2. I have 2 production servers server 1 and Server 2 both are using SQL Server Transactional Replication [whereas Server 1 is Processing Major Database Operations crawlers and xml parsers data translation and many more stored procedures. it’s a publisher] [Server 2 mostly contains Websites that are used to show sports data on website and it’s a subscriber]

  • Actual Data file size is around 3 GB on server 1
  • Actual Data file size is around 1 GB on Server 2

The problem is that Server 1’s Log file size is increasing with uncertainty let say it grows over the night and jumps to 14 – 15 GB normally if i do not shrink the size with simple recovery model. Usually i am using Full Recovery model because of Replication. I know that it’s pretty much load but that thing was not growing until i tried to make DB’s errors rectified.

Due to log size increase replication between both servers struck. some of my queries also contain nolock because previous problems until my joining was deadlocks in that DB. Now it’s around 85% less. :)

I already took and implement a Transaction log because someone posted that it might be helpful to take Log backup it helps to reuse the free space of log but there is no success indeed suppose a backup log which was using around 2 Gb log Once and had around 1600 MB free so the log backup which i initiated was around 400MB. but still the space 1600 MB was enaged to log.

please help / suggest a solution to automaticaly clear DB’s Transaction log. Summary.

  1. Irregular / uncertainty in growing of Transaction Log and not releasing space

  2. Transactional Replication between both servers stopped due to Transactional log (I think)

One more thing : i got an errors from log reader.

Error codes are (355, 298, 165, 382, 18456, 4060).

There are only 2 users sa, sqladmin

  • Backing up the transaction log file does not reduce the size of your log file (.ldf file). In your example of a 2GB log file, a backup of the active portion of 400 MB, will not reduce the file size down to 1.6GB. It simply marks the portion of the log file that was backed up as available for logging since it was backed up. How often are you backing up the log file? – Queue Mann Oct 6 '15 at 14:05
  • from 2 days. but what would be the reason of increment. Why replication dies. this is the reason which i am trying to understanding from you Tech Gurus. And by the way thanks for comment / reply. :) – hassanumair1 Oct 6 '15 at 14:21
  • Transaction log will grow depending upon how you have set that up initially in addition to the process using it. So first make sure that autogrow is not in % and is defined to a apprpt value. Another thing is , log file would be a problem if you think its growing that big eating all the space making the drive where it resides go full. Taking regular log backups should be helpful. – KASQLDBA Oct 6 '15 at 14:32
  • For Transnational replication, what error do you see in replication monitor or error logs confirming that its not working? – KASQLDBA Oct 6 '15 at 14:33
  • First of all, you need to be regularly be backing up the transaction log file. If you really don't want that file to grow, you may have to back it up multiple times per hour. It will really depend on how busy Server A is. As far as replication, as long as a transaction is in the middle of being replicated, it should still be marked as 'Active.' Try running DBCC SQLPERF(logspace) to see how much of your log file you're really using and then run: DBCC LOGINFO to see how much of it is active. – Queue Mann Oct 6 '15 at 14:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.