Currently my db logs for my production SQL Server 2008 R2 server is growing out of control:
The server mentioned above has daily backups scheduled @1am & translog backups every 15 min.
The database is replicated in full recovery model to a subscriber. Replciation is pushed from the node above (publisher). That same db log file on the subscriber is ~< 100 GB on disk.
What I did to try and fix:
Nothing above has worked so I then attempt to shrink the log files which doesn't work either using DBCC SHRINKFILE. The size doesn't ever change.
Can anyone please tell me what is wrong or what I need to do as a SQL Server DBA to resolve the above issue?
Possible things that may stop you from shrinking the translog file:
Looking at the size of your translog file size, most likely it was caused by the 2nd possibility.
Your replication distribution agent runs quite frequent
SQL Server log reader agent marks the translog file as being used and prevent them from being shrunk, which is what SQL Server does after the translog file is backed up. If this process happens frequent and long enough, this could prevent your translog file from being shrunk on translog scheduled back up.
Look at this MSDN transactional explaination and how to modify log reader agent.
And a thread in MSDN forum that describe similar problem, there is DBCC query here that helps you identify running transaction that may be blocking the translog file (DBCC OPENTRAN).
Long running transaction is occurring on your database
You can check wheter any long running transaction is happening by using DBCC OPENTRAN and what process is running then decide what to do with it. As soon as the long running transaction is finished you should be able to shrink the log file.