Ask the Community
Groups
SQL transaction logs are getting very large - Connect IT Community | Kaseya
<main> <article class="userContent"> <p><strong>PROBLEM</strong><br>You have found that the SQL transaction logs are quite large or larger than expected<br><br><strong>CAUSE</strong><br>Kaseya itself does not have any control over the transaction log files - SQL creates these and uses these based on its own design.<br><br>Whenever a change occurs on the database, SQL records this in the transaction logs. This allows that change, or any other change to be "rolled back" in the event of a problem.<br>Typically, these are used during a restore of the database.<br>Imagine that you took a backup of the database on Sunday night. At some point during Monday, there is a problem which leads to the database file being damaged.<br>Using the Sunday night backup, and the transaction log, you are able to get the database back to the last point before the failure, by "re-playing" all the transactions since the last backup.<br>A transaction log is only cleared once a successful backup has taken a place and when the database is set to "simple mode".<br><br><strong>WORKAROUND / RESOLUTION</strong><br>Although full details of this process is outside the scope of this KB article, in brief, to "fix" large transaction logs, you need to take a backup of the "ksubscribers" database. This will allow SQL to clear the transaction logs, however this will not automatically free up the space - that will require a shrink - see below<br><br><strong>ADDITIONAL INFORMATION</strong><br><br>Truncating SQL logs - <a href="/home/leaving?allowTrusted=1&target=http%3A%2F%2Fdatabases.about.com%2Fod%2Fsqlserver%2Fa%2Ftruncate_shrink.htm">http://databases.about.com/od/sqlserver/a/truncate_shrink.htm</a><br>Shrinking a transaction log that has a lot of free space - <a href="/home/leaving?allowTrusted=1&target=http%3A%2F%2Ftechnet.microsoft.com%2Fen-us%2Flibrary%2Fms178037%2528v%3Dsql.105%2529.aspx">http://technet.microsoft.com/en-us/library/ms178037(v=sql.105).aspx</a></p> </article> </main>