Dropping everything and increased size of transaction logs

May 8 at 2:30 PM
Hello.
I'm not sure if this is actually connected somehow, but I've noticed lately that the size of transaction log for a database that uses SqlTableDependency increased in size drastically (around 1 GB every 2 hours). While checking sql log I found a lot of records from SqlTableDependency.

You can see the log here: pastebin.com/ZDaeM4JZ

This several lines repeats themselves over and over again and they are different from the issue already reported about transaction logs. This may be some issue with usage or configuration on my side, of course. I would be grateful for any a hint on how to do it correctly.

Also, I noticed this issue after upgrading from 4.8 to 5.3 - again, I'm not sure if it's related in any way.
May 12 at 12:56 PM
Those logs are coming from the stored procedure, and are configurable. It is configured with
sqlTableDependency.ActivateDatabaseLoging which is false by default. But it seems you have set it to true.
set it to false and try again.
sqlTableDependency.ActivateDatabaseLoging = false;
May 15 at 7:18 AM
I think there's an error in ActivateDatabaseLoging implementation - no matter the value of it the logs will still be written.

When ActivateDatabaseLoging is set to true it calls RemoveLogOperations metod:

SqlTableDependency.cs
        protected virtual string PrepareScriptDropAll(string databaseObjectsNaming, string dropMessages)
        {
            var script = string.Format(SqlScripts.ScriptDropAll, databaseObjectsNaming, dropMessages, _schemaName);
            return this.ActivateDatabaseLoging ? script : this.RemoveLogOperations(script);
        }
This method cuts out the 'PRINT' lines. The problem is that method is looking for a non-existing string.

The line in RemoveLogOperations look like that:
var startPos = source.IndexOf("PRINT 'SqlTableDependency:", StringComparison.InvariantCultureIgnoreCase);
And the actual line in SQL look like this:
PRINT N'SqlTableDependency: Dropping trigger [{2}].[tr_{0}].'; 
The culprit is the 'N', which causes the IndexOf to always fail, no?
May 15 at 9:17 AM
Hmm, you are right. It should be fixed. You may file it at issues page, or post a pull request.
May 15 at 9:34 AM
I filed the issue.

Do you think this is the problem behind increased usage of .ldf file?
May 15 at 11:04 AM
Not sure but it certainly has an impact, since according to your logs you are creating and dropping sqltabledependency quite frequently. It is due to the multiple clients I guess. However even those logs cannot reach "to around 1 GB every 2 hours", there may also be over causes for it.
May 15 at 3:59 PM
May 16 at 7:15 AM
Your message is different - not coming from SqlTableDependency. I think it's from SQL Server directly.

I'm far from SQL expert, but that may be a problem with data base owner. You could try to set owner to SA and if the problem will vanish re-check your actual db owner account in search of configuration issues.

Not very helpful, I know, but that's all I was able to think about. Hope it'll help.
May 16 at 8:34 AM
yes - we can easy fix the Problem - the real Problem is that database log file is running full - or better the hard disk is running full because of all those logs. you only be able to fix it when the customer raises an support call...
May 16 at 9:08 AM
Since your log messages are internal to SQL Server I can't see a way to fix login configuration from SqlTableDependency.dll level. You could advise your customers about possible issue and ask them to configure their sql login correctly (or they could allow you to do it as a maintenance service).
Coordinator
May 24 at 7:10 AM
Hi all
This problem should be fixed with version 6.x
Please have a check and let me know if this issue is still present.
Regards.