SharePoint, .Net, SQL, MS BI, Collaboration and Gadgets

You probably encounter issue where you have large transaction log but small data file, e.g 40GB transaction log and 60MB database.

Take a look at:  http://support.microsoft.com/kb/317375/en-us

Unreplicated transactions
The transaction log size of the publisher database can expand if you are using replication. Transactions that affect the objects that are replicated are marked as “For Replication.” These transactions, such as uncommitted transactions, are not deleted after checkpoint or after you back up the transaction log until the log-reader task copies the transactions to the distribution database and unmarks them. If an issue with the log-reader task prevents it from reading these transactions in the publisher database, the size of the transaction log may continue to expand as the number of non-replicated transactions increases. You can use the DBCC OPENTRAN Transact-SQL reference to identify the oldest non-replicated transaction.

For more information about troubleshooting unreplicated transactions, see the “sp_replcounters” and “sp_repldone” topics in SQL Server Books Online.

For more information, click the following article number to view the article in the Microsoft Knowledge Base:

306769 FIX: Transaction log of snapshot published database cannot be truncated
240039 FIX: DBCC OPENTRAN does not report replication information
198514 FIX: Restore to new server causes transactions to remain in log

These are some of the actions they took to solve the problems:

• Creating a new transaction log (other disk)
• Limiting the growth of the original log
• Triggering replication to clear the log.